Tuesday, August 25, 2015

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

This error message means that no access control list has been assigned to the host you (your application) are trying to access, or no required privileges have been granted to the user by adding user to the ACL. Following is an example executed on 12c (12.1.0.2) to show how we create ACL and how to test it.  
SQL> create user C##SALMAN identified by salman;

User created.

SQL> grant connect to c##salman;

Grant succeeded.

SQL> grant execute on utl_http to c##salman;

Grant succeeded.


SQL> conn c##salman/salman
Connected.

Declare
V_req utl_http.req;
V_resp utl_http.resp;
Begin
V_req:=utl_http.begin_request('http://192.231.231.1');
V_resp:=utl_http.get_response(v_req);
Utl_http.end_response(v_resp);
End;
/

ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 6


User C##SALMAN is trying to execute a package which relies on ACL, and since no ACL has been created and no required privilege have been granted to C##SALMAN user, ORA-24247 is being thrown.

In the following I will create an ACL and grant privilege to C##SALMAN and after that no error would be returned.
Login as SYSDBA and execute following code. First create ACL with the privilege assigned to user C##SALMAN (parameter “principal” is used to mention user or role to which privilege on the ACL is being assigned). After that, assign this ACL to the host or a list of hosts by providing hostname or IP of the host, or a range IP address of host to which ACL is being assigned.

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'test_acl_file.xml',
description => 'Testing ACL functionality',
principal => 'C##SALMAN',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/

PL/SQL procedure successfully completed.

BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'test_acl_file.xml',
host => '192.*',
lower_port => NULL,
upper_port => NULL);
COMMIT;
END;
 /

PL/SQL procedure successfully completed.

Query the information form data dictionary

SQL> col acl format a50
SQL> col host format a50
SQL> set lines 200
SQL> col principal format a10
SQL> col privilege format a10
SQL> select acl,host from dba_network_acls;

ACL                                                            HOST
-------------------------------------------------- --------------------------------------------------
/sys/acls/test_acl_file.xml                           192.*

SQL> select acl,principal,privilege,is_grant from dba_network_acl_privileges where ACL='/sys/acls/test_acl_file.xml';

ACL                                                PRINCIPAL              PRIVILEGE    IS_GR
-------------------------------------------------- ---------- ---------- --------------------------
/sys/acls/test_acl_file.xml               C##SALMAN           connect             true

Now all hosts in range of IP address 192.* have been assigned this ACL. “host” parameter can also have a value of ‘*’ to assign ACL to all IP addresses, or can have a value like ‘192.168.231.12’ to assign ACL to a single host. “lower_port” and “upper_port” parameters in DBMS_NETWORK_ACL_ADMIN.assign_acl are used to restrict access to only a specific range of ports.

Now try executing UTL_HTTP again as user C##SALMAN
Declare
V_req utl_http.req;
V_resp utl_http.resp;
Begin
V_req:=utl_http.begin_request('http://192.231.231.1');
V_resp:=utl_http.get_response(v_req);
Utl_http.end_response(v_resp);
End;
/

PL/SQL procedure successfully completed.


Same ACL can be assigned to more hosts or a range of IP addresses.  

BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'test_acl_file.xml',
host => '172.29.38.*',
lower_port => NULL,
upper_port => NULL);
COMMIT;
END;
 /

PL/SQL procedure successfully completed.

SQL>  select acl,host from dba_network_acls;

ACL                                                         HOST
-------------------------------------------------- --------------------------------------------------
/sys/acls/test_acl_file.xml                        172.29.38.*
/sys/acls/test_acl_file.xml                        192.*


Adding more users to the ACL
SQL> create user C##TEST identified by test;

User created.

SQL> grant connect to C##TEST;

Grant succeeded.

SQL> grant execute on utl_http to C##TEST;

Grant succeeded.

BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege ( acl => '/sys/acls/test_acl_file.xml',
principal => 'C##TEST',
is_grant => TRUE,
privilege => 'connect',
position => NULL,
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> select acl,principal,privilege,is_grant from dba_network_acl_privileges where ACL='/sys/acls/test_acl_file.xml';

ACL                                                PRINCIPAL              PRIVILEGE    IS_GR
-------------------------------------------------- ---------- ---------- --------------------------
/sys/acls/test_acl_file.xml               C##SALMAN           connect             true
/sys/acls/test_acl_file.xml               C##TEST                  connect             true




Monday, August 17, 2015

Using PGA_AGGREGATE_LIMIT to Limit PGA Memory

Before 12c, there was no hard limit to restrict PGA memory usage by an Oracle session. It means that if a session keeps growing in memory, it would eventually allocate all available memory which would lead to a system slowness/hang and eventually system crash, I have discussed this scenario here.

Starting 12c, we can use parameter PGA_AGGREGATE_LIMIT so set a limit to the PGA growth for each session. Value of this parameter would apply to each session individually – which means that if value of this parameter is set to 2G, each session cannot take more than 2G memory for its PGA. Default value of this parameter is as follows (copied from here)

By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.
     

Following example shows how it works.
SQL> show parameter pga_aggregate_limit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G

SQL> create user c##testuser identified by testuser;

User created.

SQL> grant connect, create procedure to c##testuser;

Grant succeeded.

-- Connect with this new user and create a procedure to test PGA memory growth

SQL> conn c##testuser/testuser
Connected.
SQL> create or replace procedure test_proc is
  2  begin
  3  test_proc;
  4  end;
  5  /

Procedure created.


Now if we call this procedure, it would call itself recursively infinitely and PGA would start growing(and keep growing) in memory.

SQL> exec test_proc


While execution of above procedure is in progress, let’s monitor the PGA memory
SQL> set time on
14:51:47 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   2.48916531

14:51:52 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   900.42667

14:52:05 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1221.55167

14:52:06 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1694.23917

14:52:07 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1705.92667

14:52:08 SQL>
14:52:10 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1733.73917

14:52:11 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1756.67667

14:52:13 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1874.11417

14:52:17 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1886.05167

14:52:20 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1886.05167

14:52:23 SQL> select a.username,b.pga_alloc_mem/1024/1024 from v$session a, v$process b where a.paddr=b.addr and a.username='C##TESTUSER';

USERNAME                       B.PGA_ALLOC_MEM/1024/1024
------------------------------ -------------------------
C##TESTUSER                                   1886.05167

Now it is not growing any further and eventually session would be killed automatically as you can see bellow error returned to the session executing this procedure.
BEGIN test_proc; END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7792
Session ID: 15 Serial number: 45903


SQL>

Alert log file would report and error similar to the following.
Tue Aug 11 14:52:19 2015
Errors in file C:\APP\SALMQURE\diag\rdbms\salman12\salman12\trace\salman12_ora_7792.trc  (incident=133769) (PDBNAME=CDB$ROOT):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Incident details in: C:\APP\SALMQURE\diag\rdbms\salman12\salman12\incident\incdir_133769\salman12_ora_7792_i133769.trc
Errors in file C:\APP\SALMQURE\diag\rdbms\salman12\salman12\trace\salman12_ora_7792.trc  (incident=133770) (PDBNAME=CDB$ROOT):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Incident details in: C:\APP\SALMQURE\diag\rdbms\salman12\salman12\incident\incdir_133770\salman12_ora_7792_i133770.trc
Tue Aug 11 14:52:51 2015
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 2048 MB
Immediate Kill Session#: 15, Serial#: 45903
Immediate Kill Session: sess: 000007FF7D7CC408  OS pid: 7792


Monday, August 10, 2015

Generating Bulk of Data for Testing Purpose

Sometimes we need to generate a huge amount of data to perform some testing. Type of data to be generated depends on the scenario you want to test. I will show here a very simple example to create a very huge table. I usually use it if I need to have a huge tablespace or table to perform some test. For example, to test backup and recovery time of some tablespace with huge data, I would use this method to generate huge data

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 21 09:41:59 2015

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> create tablespace my_tbs datafile 'c:\my_tbs01.dbf' size 100m autoextend on next 100m;

Tablespace created.

10:27:33 SQL> create table test as select * from dba_objects;

Table created.

Elapsed: 00:00:00.89

10:27:55 SQL> alter table test move tablespace my_tbs;

Table altered.

Elapsed: 00:00:00.74

--Delete NULL from OBJECT_ID column if you want to test primary key index on this column.
10:28:08 SQL> delete from test where object_id is null;

1 row deleted.

Elapsed: 00:00:00.20
10:28:17 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
--Repeat INSERT statement to insert TEST table’s data into itself to increase size by 2X with every insert.

10:28:18 SQL> insert into test select * from test;

91122 rows created.

Elapsed: 00:00:00.17
10:28:22 SQL> insert into test select * from test;

182244 rows created.

Elapsed: 00:00:00.61
10:28:25 SQL> insert into test select * from test;

364488 rows created.

Elapsed: 00:00:03.35
10:28:29 SQL> insert into test select * from test;

728976 rows created.

Elapsed: 00:00:07.78
10:28:38 SQL> insert into test select * from test;

1457952 rows created.

Elapsed: 00:00:23.11
10:29:02 SQL> insert into test select * from test;

2915904 rows created.

Elapsed: 00:00:43.86
10:29:47 SQL> insert into test select * from test;

5831808 rows created.

Elapsed: 00:02:10.51
10:31:59 SQL> insert into test select * from test;

11663616 rows created.

Elapsed: 00:04:14.96
10:36:20 SQL> commit;

Commit complete.

Elapsed: 00:00:00.02
--Check size of table and tablespace after having more than 200 million rows in TEST table

10:36:20 SQL> select bytes/1024/1024 from dba_data_files where tablespace_name='MY_TBS';

BYTES/1024/1024
---------------
           3100

Elapsed: 00:00:00.08
10:36:49 SQL> select bytes/1024/1024 from dba_segments where segment_name='TEST';

BYTES/1024/1024
---------------
           3079

Elapsed: 00:00:00.33
10:37:10 SQL> select count(*) from test;

  COUNT(*)
----------
  23327232

Elapsed: 00:00:30.59
10:37:52 SQL>

Tuesday, August 4, 2015

Changing AWR Snapshot Retention and Interval

By default AWR snapshot interval is set to 60 minutes and retention of snapshots is set to 8 days. For better and precise investigation of problems, I recommend to use an interval of 15 minutes and retention of 35 days. If database is huge in size and operations, also RAC, your SYSAUX tablspace would certainly grow in size with these settings. I have observed size of SYSAUX tablespace for a 3 nodes RAC database, around 14G with aforementioned settings.
Following is the method to find out current settings and modify the existing settings.


SQL> set lines 200
SQL> col SNAP_INTERVAL format a20
SQL> col RETENTION format a20
SQL> select * from dba_hist_wr_control;

 DBID              SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ---------- ----------------------------
3275747663    +00000 01:00:00.0          +00008 00:00:00.0    DEFAULT

--Here interval is set to 1 hour and retention is set to 8 days.
--You can also create a snapshot manually by executing following procedure
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

--Now let’s change the retention and interval settings. Interval will be set to 15 minutes and retention to 35 days (35x24x60 = 50400)

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => 50400)

PL/SQL procedure successfully completed.

SQL> select * from dba_hist_wr_control;

 DBID              SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- -------------------------------------
3275747663    +00000 00:15:00.0         +00035 00:00:00.0    DEFAULT

Monday, August 3, 2015

Oracle Deferred Segment Creation

Starting 11g Release 2, we can create a table/index/LOB without space allocated to it (no segment creation) until first row is inserted into the table. Init parameter DEFERRED_SEGMENT_CREATION is used to setup the default behavior. By default value of this parameter is set to TRUE, which means that whenever a table will be created, there will be no initial space allocation (segment creation) to the table, it’s indexes and LOB, until first row is inserted in this table. Value of this parameter can also be set at the session by using ALTER SESSION command.

If value of DEFERRED_SEGMENT_CREATION is set to FALSE (at system level or session level), a segment would immediately be created with the creation of a table. A table/index/LOB can still be created without a segment by using clause SEGMENT CREATION DEFERRED clause with the CREATE TABLE command as it will override the value of parameter DEFERRED_SEGMENT_CREATION. Likewise if value of this parameter is TRUE, still segment creation can be done immediately with the creation of table by creation clause SEGMENT CREATION IMMEDIATE with the CREATE TABLE command.

People who are not familiar with this feature can find themselves perplexed if they see their tables in USER_TABLES view, but no entry for these tables under DBA_SEGMENTS or USER_SEGMENTS views. The reason is same as explained above that if there is no data insertion in these tables, there will not entry in *_SEGMENTS data dictionary views.

Following is an example
SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
deferred_segment_creation            boolean     TRUE

SQL> create user salman identified by salman default tablespace users quota unlimited on users;

User created.

SQL> grant connect, create table to salman;

Grant succeeded.

SQL> conn salman/salman
Connected.

SQL> create table test (id number);

Table created.

SQL> select segment_type from user_segments where segment_name='TEST';

no rows selected

SQL> create index test_idx on test(id);

Index created.

SQL> select segment_type from user_segments where segment_name='TEST_IDX';

no rows selected


Segments get created after inserting one row
SQL> insert into test values(1);

1 row created.

SQL> select segment_type from user_segments where segment_name='TEST';

SEGMENT_TYPE
------------------
TABLE

SQL> select segment_type from user_segments where segment_name='TEST_IDX';

SEGMENT_TYPE
------------------
INDEX


Overriding default behavior if DEFERRED_SEGMENT_CREATION is set to TRUE
SQL> create table test (id number) segment creation immediate;

Table created.

SQL> select segment_type from user_segments where segment_name='TEST';

SEGMENT_TYPE
------------------
TABLE

SQL> create index test_idx on test(id);

Index created.

SQL> select segment_type from user_segments where segment_name='TEST_IDX';

SEGMENT_TYPE
------------------
INDEX