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

While executing PL/SQL procedure to send mails by using UTL_SMTP, it fails with error “ORA-24247” in 11g databases.

SOLUTION

ACL needs to be created with following instructions.

DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = '*' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'SCOTT','connect')
IS NULL
THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'SCOTT', TRUE, 'connect');
END IF;
EXCEPTION
WHEN no_data_found THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('utl_mail.xml','ACL description', 'SCOTT', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('utl_mail.xml','*');
END;

COMMIT;
SQL> select ACL,PRINCIPAL,PRIVILEGE,IS_GRANT 
from DBA_NETWORK_ACL_PRIVILEGES;

ACL PRINCIPAL PRIVILEGE IS_GRANT
---------------------- ---------- --------- --------------------
/sys/acls/utl_mail.xml SCOTT connect true

ORA-24247 Trying To Send Email Using UTL_SMTP from 11gR1 (11.1.0.6) or higher (Doc ID 557070.1)

The dataguard broker keeps killing process RSM

Dataguard broker keeps killing RSM process overnight and oncall DBA is pagered by alerts.

DG 2014-10-04-04:17:17 0 2 0 DMON: killing process RSM0, pid = 26742
DG 2014-10-04-04:17:18 0 2 0 DMON: waiting for subscribers to disappear...
DG 2014-10-04-04:17:18 0 2 0 PMON: delete state object for RSM0
DG 2014-10-04-04:17:18 0 2 0 PMON: RSM0 died unexpectedly while processing request 1.1.833218860,
DG 2014-10-04-04:17:18 0 2 0 notifying DMON of RSM failure
DG 2014-10-04-04:17:18 0 2 0 DMON: Creating process RSM0
DG 2014-10-04-04:17:21 0 2 0 RSM0: Attach state object
DG 2014-10-04-04:17:21 0 2 0 DMON: Process RSM0 re-created with pid = 22898

WORKAROUND

DGMGRL> show configuration OperationTimeout;
OperationTimeout = '30'

DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=120;
Property "operationtimeout" updated

DGMGRL> show configuration OperationTimeout;
OperationTimeout = '120'
DGMGRL>

Reference Oracle Doc:
Data Guard: Server Hang And Crash Because RSM0 Keeps Re-spawning (Doc ID 1322877.1)
Cause : The Broker is killing the RSM0 process and restarting it because of an internal timeout that is too low.

 

Interval-Reference Partitioned Tables

Partitions in a reference-partitioned table corresponding to interval partitions in the parent table are created when inserting records into the reference partitioned table.

When creating an interval partition in a child table, the partition name is inherited from the associated parent table fragment. If the child table has a table-level default tablespace, then it is used as tablespace for the new interval partition; otherwise, the tablespace is inherited from the parent table fragment.

Interval-reference functionality requires that the database compatibility level (Oracle Database COMPATIBLE initialization parameter setting) be set to greater than or equal to 12.0.0.0.

  • Create a parent table and local index.
SQL>create table reserve 
(
res_id number primary key not null,
res_date date,
hotel_id number(3),
guest_id number
)
partition by range (res_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(partition P_FIRST values less than (TO_DATE('1900-01-01','YYYY-MM-DD'))
);

Table created.

SQL> create index idx_res_date on reserve(res_date) local;

Index created.
  • Create a child table.
SQL>create table transactions 
(
trans_id number not null,
res_id number not null,
trans_date date not null,
amt number,
constraint fk_trans_01 foreign key (res_id) references reserve
)  partition by reference (fk_trans_01);


Table created.
  • Query table and index  partitions.
SQL> select table_name, partition_name, high_value  
from user_tab_partitions  where table_name in ('RESERVE', 'TRANSACTIONS');

TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- --------------- ---------------------------------------------------------
RESERVE     P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TRANSACTIONS   P_FIRST

SQL>select INDEX_NAME,PARTITION_NAME,HIGH_VALUE
from user_ind_partitions;

INDEX_NAME PARTITION_NAME HIGH_VALUE
--------------- --------------- --------------------------------------
IDX_RES_DATE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
  • Insert data into parent table
 SQL> insert into reserve values (1,sysdate-5,1,1);

1 row created.

 SQL> insert into reserve values (2,sysdate-4,1,2);

1 row created.

SQL> insert into reserve values (3,sysdate-3,2,3);
1 row created.

SQL> insert into reserve values (4,sysdate,3,3);
1 row created.

SQL> commit;

Commit complete.
  • Query table and index  partitions.
SQL> select table_name, partition_name, high_value  
from user_tab_partitions  
where table_name in ('RESERVE', 'TRANSACTIONS');

TABLE_NAME PARTITION_NAME HIGH_VALUE
----------- --------------- ---------------------------------------
RESERVE     P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE   SYS_P301 TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE   SYS_P321 TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE   SYS_P341 TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE   SYS_P342 TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE 
from user_ind_partitions;

INDEX_NAME PARTITION_NAME HIGH_VALUE
------------ -------------- ---------------------------------------------------------
IDX_RES_DATE SYS_P301 TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P321 TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P341 TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE SYS_P342 TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
IDX_RES_DATE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
  • Insert data into child table.
SQL> insert into transactions values(1,1,sysdate-5,1000);
1 row created.

SQL> insert into transactions values(2,2,sysdate-4,2000);
1 row created.

SQL> insert into transactions values(3,3,sysdate-3,3000);
1 row created.

SQL> insert into transactions values(4,4,sysdate,4000);
1 row created.

SQL> commit;
Commit complete.
  • Query tables partitions.
SQL> select table_name, partition_name, high_value  
from user_tab_partitions  where table_name in ('RESERVE', 'TRANSACTIONS');


TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- -------------- ----------------------------------------
RESERVE P_FIRST TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P301 TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P321 TO_DATE(' 2014-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P341 TO_DATE(' 2014-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
RESERVE SYS_P342 TO_DATE(' 2014-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
TRANSACTIONS P_FIRST
TRANSACTIONS SYS_P301
TRANSACTIONS SYS_P321
TRANSACTIONS SYS_P341
TRANSACTIONS SYS_P342

10 rows selected.
  • Drop a partition of parent table.
SQL> alter table RESERVE drop partition SYS_P301 update indexes;

Table altered.
SQL> select table_name, partition_name  
from user_tab_partitions
where table_name in ( 'RESERVE','TRANSACTIONS');

TABLE_NAME PARTITION_NAME --------------- --------------- RESERVE P_FIRST RESERVE SYS_P321 RESERVE SYS_P341 RESERVE SYS_P342 TRANSACTIONS P_FIRST TRANSACTIONS SYS_P321 TRANSACTIONS SYS_P341 TRANSACTIONS SYS_P342

8 rows selected.
SQL> select INDEX_NAME,PARTITION_NAME from user_ind_partitions;

INDEX_NAME PARTITION_NAME --------------- --------------- IDX_RES_DATE SYS_P321 IDX_RES_DATE SYS_P341 IDX_RES_DATE SYS_P342 IDX_RES_DATE P_FIRST

We can see both index partition and  child partition are dropped automatically.

Transportable Tablespace on Physical Standby Database

SITUATION

We need refresh one schema with data about 300 GB from PROD database to TEST database for testing purpose. The production database is 7×24, but there is a physical standby database. so we use physical standby database for transportable tablespace activities without touching and impacting on 7×24 production environment. The physical standby database and test database are sitting on the same grid infrastructure. The schema has only one tablespace.

It will take up to 10 hours by using normal datapump export and import. Instead, it will only take half an hour by using transportable tablespace on physical standby database without impacting on primary  database.

ENVIRONMENT

Operating System : RedHat Linux 6
Oracle Grid Infrastructure : 4 nodes 12.1.0.1 Clustware
Oracle RAC PROD database : 11.2.0.4
Oracle RAC TEST database : 11.2.0.4
Schema & Tablespace Name : TRANSPORT

STEPS

  • Create a new schema if you want to keep the original name, otherwise system will generate a new one automatically by using parameter “REMAP_SCHEMA” when importing.
  • Export schema metadata excluding tables and indexes on source database.
$expdp system/password  directory=EXP_DIR dumpfile=transport_meta_no_table_index.dmp content=metadata_only schemas=transport  exclude=table exclude=index
  • Check and make sure the standby is in SYNC with primary

Query data dictionary view V$ARCHIVE_GAP on standby database.

SQL>select THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# from V$ARCHIVE_GAP;

Note: there are lots of bugs and performance issues for this view on some versions like 11.2.0.4.

If you have set up Data Guard for this database, it should be easy to check:

DGMGRL> show database verbose "STNBYDB";

Database - STNBYDB

Enterprise Manager Name: STNBYDB
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 9.41 MByte/s
Real Time Query: OFF
Instance(s):
STNBYDB1
STNBYDB2
STNBYDB3
STNBYDB4 (apply instance)
  • Check and set the value of db_recovery_file_dest_size on standby.

SQL> show parameter recovery

NAME                       TYPE        VALUE
-------------------------- ----------- ------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 4000G
  • Cancel the MRP(Managed recover process).
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.
  • Create guaranteed restore points(GRP) on standby.
SQL> CREATE RESTORE POINT refresh_transport GUARANTEE FLASHBACK DATABASE;

Restore point created.
  • On primary defer the log_archive_dest_2, which is sending logs to standby
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

System altered.
  • Check the protection mode if it is MAX_AVAILABILITY AND MAX_PROTECTION then downgrade it to MAX_PERFORMANCE by:
SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAX_PROTECTION MAX_PROTECTION

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.
  • Activate and open the standby database.
SQL> select open_Mode from v$database;

OPEN_MODE
-------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.
  • Check that the tablespace will be self-contained.
SQL>execute sys.dbms_tts.transport_set_check('TRANSPORT', true);
SQL> select * from sys.transport_set_violations;

==>Any violations must be resolved before the tablespaces can be transported

  • Place the tablespace in read only mode.
SQL> alter tablespace TRANSPORT read only;

Tablespace altered.
  • Do the Metadata export, and copy files.
$ expdp system/password dumpfile=transport_refresh.dmp        \
directory=EXP_DIR transport_tablespaces = TRANSPORT \
transport_full_check=y

Note: The parameters are different for different versions, like for 10g: transport_tablespace=y  tablespaces=TRANSPORT

RMAN> copy datafile 20 to '+DG2'; ( where DG2 is the diskgroup for TEST database )
  • Check the endianness of the target database and convert, if necessary ( NOT applicable to this situation ).
SQL>select * from v$transportable_platform order by platform_id;

If source and target endianness are the same , File conversion is NOT needed ( please refer to oracle doc for other requirements ).

If source and target Endianness are different , the file need to be converted by using RMAN on source server:

RMAN> convert tablespace TRANSPORT to platform='new platform name' ...

On target server:

RMAN> CONVERT DATAFILE .. to PLATFORM=' target platform name' from platform=' source platform name'
  • Import TTS metadata.
$ impdp system/password  DUMPFILE=transport_refresh.dmp DIRECTORY=EXP_DIR TRANSPORT_DATAFILES='+dg2/TEST/DATAFILE/TRANSPORT.588.860329689'

It’s not possible to import when tablespace already exists or when target schema is not created. If users don’t exist, DataPump provides an alternative by using remap_schema (for import utility we can create the schema) ie:

REMAP_SCHEMA=<source_user>:<target_user>

If tablespace already exists in target, we can use remap_tablespace parameter on impdp (there is no option in import but rename tablespace at source or the existing
one at target).

REMAP_TABLESPACE=(<source_tbs1>:<target_tbs1>,<source_tbs2>:<target_tbs2>,…)

  • Place the standby database in mount mode again.
SQL> STARTUP MOUNT;
  • Flashback to GRP, then shutdown and starup the standby database.
SQL> FLASHBACK DATABASE TO RESTORE POINT refresh_transport;
Flashback completed.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.
  • Start up the MRP again if not yet.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT USING CURRENT LOGFILE;

Database altered.
  • Enable the log_archive_dest_2 on primary , and check the standby database SYNC status as per previous step.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.
  • Drop guaranteed restore points(GRP):
RMAN> LIST RESTORE POINT ALL;

using target database control file instead of recovery catalog
SCN RSP Time Type Time Name
---------------- ------------------- ---------- ------------------- ----
6836263465366 GUARANTEED 2014-10-07 11:55:19 REFRESH_TRANSPORT

SQL> DROP RESTORE POINT TRANSPORT_REFRESH;

Restore point dropped.
  • Import schema meta objects like packages, procedures and functions,etc.
SQL> select object_type,status,count(*) from dba_objects where owner='TRANSPORT' group by object_type,status;

OBJECT_TYPE STATUS COUNT(*)
------------ ------- -------
TRIGGER INVALID 6
TABLE VALID 175
INDEX VALID 383
LOB VALID 10


$impdp system/password  directory=EXP_DIR dumpfile=transport_meta_no_table_index.dmp content=metadata_only schemas=transport  exclude=table exclude=index

SQL> select object_type,status,count(*)
from dba_objects
where owner='TRANSPORT'
group by object_type,status;

OBJECT_TYPE STATUS COUNT(*)
------------ ------- -------
FUNCTION VALID 1
SYNONYM VALID 15
PACKAGE VALID 20
SEQUENCE VALID 6
TRIGGER VALID 6
TABLE VALID 175
PACKAGE BODY VALID 18
INDEX VALID 383
LOB VALID 10
VIEW VALID 11

10 rows selected.

 

Setting Shell Limits For Grid and Oracle Users

This post advises how to set resources limit correctly.

ulimit is to provide control over system resources. Setting limits to allow the user to use resources as much as really needed, so the system resources can be shared by all users fairly.

There are different ways and different places (/etc/security/limits.conf, /etc/profile, etc ) for setting shell limits, which are easily overlooked and confused by DBA.

This post will explain how to setup shell limits properly and correctly.

Subscribe to get access

Read more of this content when you subscribe today.