Could not reserve enough space for object heap

During oracle 12C agent installation i got below,, here is fix , i will add more detail on this ..

Unzipping the agentcoreimage.zip to /export/apps/oradba/XXXX/agent125c_new ....
12.1.0.5.0_PluginsOneoffs_23.zip
Executing command : /export/apps/oradba/XXXX/agent125c/unzip -o /export/apps/oradba/XXXX/agent125c/12.1.0.5.0_PluginsOneoffs_23.zip -d /export/apps/oradba/XXXX/agent125c_new
Successfully unzipped /export/apps/oradba/XXXX/agent125c/12.1.0.5.0_PluginsOneoffs_23.zip to /export/apps/oradba/XXXX/agent125c_new !
mv: cannot rename /export/apps/oradba/XXXX/agent125c_new/sbin_new to /export/apps/oradba/XXXX/agent125c_new/sbin/sbin_new: File exists
Executing command : /export/apps/oradba/XXXX/agent125c_new/core/12.1.0.5.0/jdk/bin/java  -d64 -classpath /export/apps/oradba/XXXX/agent125c_new/core/12.1.0.5.0/oui/jlib/OraInstaller.jar:/export/apps/oradba/XXXX/agent125c_new/core/12.1.0.5.0/oui/jlib/xmlparserv2.jar:/export/apps/oradba/XXXX/agent125c_new/core/12.1.0.5.0/oui/jlib/srvm.jar:/export/apps/oradba/XXXX/agent125c_new/core/12.1.0.5.0/oui/jlib/emCfg.jar:/export/apps/oradba/XXXX/agent125c_new/core/12.1.0.5.0/jlib/agentInstaller.jar:/export/apps/oradba/XXXX/agent125c_new/core/12.1.0.5.0/oui/jlib/share.jar oracle.sysman.agent.installer.AgentInstaller /export/apps/oradba/XXXX/agent125c_new/core/12.1.0.5.0 /export/apps/oradba/XXXX/agent125c /export/apps/oradba/XXXX/agent125c_new /export/apps/oradba/XXXX/agent125c_new/agent_inst AGENT_BASE_DIR=/export/apps/oradba/XXXX/agent125c_new
Error occurred during initialization of VM
Could not reserve enough space for object heap
Could not create the Java virtual machine.

After export below parameter my agent went fine..  

export _JAVA_OPTIONS='-Xms64M -Xmx128m'

How to recover apps password in R12.

In R12 , apps password is not hardcoded, still few workarounds may work for you.
1. if sqlplus , than u need to set it back to old value and continue to work. you can also use 'identified by value' option instead of giving password.

2. if FNDCPASS , than
a. You  can change it back to old value...no loss.
b. if you have any custom program which has apps password hardcoded u can check from there.
c. if you have any custom script in cron or so where apps password is hardcoded u can check from there.
d. check bash_history for last FNDCPASS command.
   e.
Decrypting APPS password in R12 :
SQL> show user
USER is "SYS"
SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/ 2 3 4 5

Function created.

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG1040180FF7A514EB0209759D297661E8E28DD99460D28850C00D783E24843FC44BCB81BD289305AA4FCFF468533FBBBB72


SQL> SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG1040180FF7A514EB0209759D297661E8E28DD99460D28850C00D783E24843FC44BCB81BD289305AA4FCFF468533FBBBB72') from dual;

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG1040180FF7A514EB0209759D297661E8E28DD994
--------------------------------------------------------------------------------
S2MUL8TION

SQL> conn apps/S2MUL8TION
Connected.
SQL>

SQL> drop function apps.decrypt_pin_func;

Function dropped.

Installing and configuring CSSCAN

Below are high label steps for Installing and configuring CSSCAN  in Oracle 10G and 11G .

select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

Note 745809.1 Installing and configuring CSSCAN in 10g and 11g

3) Installing Csscan
Run csminst.sql using these commands and SQL statements:

cd $ORACLE_HOME/rdbms/admin
set oracle_sid=<your SID>
sqlplus /nolog
SQL>conn / as sysdba
SQL>set TERMOUT ON
SQL>set ECHO ON
SQL>spool csminst.log
SQL> START csminst.sql

select owner,  object_type, status, object_name from dba_objects where status ='INVALID';



csscan  full=y userid=sys "/as sysdba"  FROMCHAR=UTF8 TOCHAR=AL32UTF8 LOG=UTF8_TO_UTF8_17_april CAPTURE=Y ARRAY=1000000 PROCESS=2


conn / as sysdba
SELECT OWNER, ORIGINAL_NAME, OBJECT_NAME, TYPE from dba_recyclebin order by 1,2;


conn / as sysdba
PURGE DBA_RECYCLEBIN;

csscan FULL=Y  userid=csmig/S0l_ut10n FROMCHAR=UTF8 TOCHAR=AL32UTF8 LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=2


csscan  full=y userid=csmig/S0l_ut10n FROMCHAR=UTF8 TOCHAR=AL32UTF8 LOG=post_drop CAPTURE=Y ARRAY=1000000 PROCESS=2

Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8

Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (Doc ID 260192.1)
Note 788156.1 AL32UTF8 / UTF8 (Unicode) Database Character Set Implications
What languages are supported in an Unicode (UTF8/AL32UTF8) database? (Doc ID 1051824.6)

From SR

your existing database has a lot of LOSSY data that needs to be fixed.
But this can be fixed after moving to the new character set also .
( Path: you can export data, truncate table, convert characterset and then import data back into tables in new characterset).

But the problem here, you have a lot of LOSSY data. Both using fromchar=WE8ISO8859P1, tochar=WE8ISO8859P1 as well as fromchar=WE8ISO8859P1, tochar=AL32UTF8.

We shall see if there is an intermediate way to do this and bringdown lossy data.
Please provide below details:

1. Please run Csscan FROMCHAR=<WE8ISO8859P1> TOCHAR=<WE8MSWIN1252> and upload the csscan results.
2.
SELECT DUMP(COUNTRY_REMARK, 1016) FROM DW.TB_COM02190_DIM_GEOGRAPHY
where rowid='AACfPpABEAAADDqABV';
SELECT DUMP(SUPPLIER_NAME_CITY_CODE, 1016) FROM DW.BUF_SRC_DLV_MET_SUPP_PLNT_LK_1
where rowid='AACfPpABEAAADDqABV';
3.

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKNOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'),
8, decode(charsetform, 1, 'LONG', 'UNKNOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 8, 9, 96, 112)
order by CHARACTERSET, TYPES_USED_IN

Migration from Oracle to MySQL

Exporting Data from Oracle

Exporting the existing data from Oracle is often one of the cheapest forms of migration, but it can involve more manual input and can be slower than using a migration tool. One of the most popular tools for exporting data from Oracle is Oracle SQL Developer. The Oracle SQL Developer tool is capable of exporting data from Oracle tables in numerous formats, like Excel, SQL insert statements, SQL loader format, HTML, XML, PDF, TEXT, Fixed text, etc.

You can use SQL*Plus to select and format your data and then spool it to a file. This example spools out a CSV (comma separated values) file that could either be imported via LOAD DATA INFILE or by creating a CSV table for immediate access.

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
  from tab1
  where col2 = 'XYZ';
spool off

You can also use the "set colsep" command if you don't want to put the commas in by hand. This saves a lot of typing. Example:

set colsep ','
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1, col2, col3
  from tab1 where col2 = 'XYZ';
spool off

Using PL/SQL

PL/SQL's UTL_FILE package can also be used to unload data. Example:

declare
  fp utl_file.file_type;
begin
  fp := utl_file.fopen('c:\oradata','tab1.txt','w');
  utl_file.putf(fp, '%s, %sn', 'TextField', 55);
  utl_file.fclose(fp);
end;
/


Importing Data into MySQL

Once the data has been exported from Oracle into a flat file, then it can be loaded into MySQL. This step continues with the expectation that an identical schema structure was created in the MySQL database to accept the data.

The most common method to read a flat file into MySQL is using the LOAD DATA INFILE command. This is of the form:

LOAD DATA INFILE 'data.txt'   INTO TABLE tbl_name   FIELDS TERMINATED BY ','  ENCLOSED BY '"'
   LINES TERMINATED BY '\r\n'
   IGNORE 1 LINES;
This will read the exported data from the 'data.txt' file into the table 'tbl_name' using the specified separators.

There is also the LOAD XML INFILE if the exported data is in XML format.


Another method that may be even quicker for load is to use the CSV storage engine capabilities. This requires the table to again be created with the appropriate schema and the storage engine specified as CSV. For example:

CREATE TABLE mytable (
   name varchar(50)  NOT NULL,
   address varchar(100) NOT NULL,
   zipcode CHAR(5) NOT NULL
) ENGINE=CSV;
Once the table is created, a .csv file will appear in the data directory corresponding to the table ready to store data. Copy the exported CSV file over the existing 'mytable.csv' data file and execute a FLUSH TABLES to update the table information. Now you have access to the data and can use a INSERT INTO new_table ... SELECT to read data quickly from the CSV table into a more appropriate table for your production system.


Migration Tools
Depending on the setup you use, the following may be interest in your migration tasks:

There are a host of third party tools, some of which are open source. For example:
http://kettle.pentaho.com/
http://www.convert-in.com/ora2sql.htm
http://www.ispirer.com/products/oracle-to-mysql-migration

Oracle Database Corruption

Master Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1). This will help you in case if you get future issues, please take a look at it.

How to perform ORACLE_HOME/binaries backup

Oracle binaries backup is needed when we are doing any OS  label upgrade like
OEL upgrade .

1. (Suggested but not mandatory) Shutdown databases, listeners, or any other processes related to the ORACLE_HOME which you are taking backup.

2. cd to the directory where ORACLE_HOME is located for example:

cd /u01/app/oracle/product/11.2

3. backup the ORACLE_HOME for example:

tar -pcvf /u01/app/oracle/backup/oracle_home_bkup.tar db1

In the above example, ORACLE_HOME is /u01/app/oracle/product/11.2/db1 and backup the directory is /u01/app/oracle/backup/

Below is an example of restoring the ORACLE_HOME:

1. (This step would be mandatory in the case of restore) Shutdown databases, listeners, or any other processes related to the ORACLE_HOME which you are restoring.

2. Go to the directory where ORACLE_HOME is located for example:

cd /u01/app/oracle/product/11.2

3. Rename or move the current ORACLE_HOME for example:

mv db1 db1_bkup

3. Restore the ORACLE_HOME for example:

tar -pxvf /u01/app/oracle/backup/oracle_home_bkup.tar

Make sure that is enough enough free disk space before doing the backup.

find which asm disk maps to which linux partition

There is varies option  to check which disk is part of Which disk Group .

- # ls -ls /dev/mpath/DATA3*

0 brw-rw—- 1 grid asmadmin 8, 17 Oct 20 12:46 /dev/oracleasm/disks/DATA3
0 brw-rw—- 1 grid asmadmin 8, 18 Oct 20 12:46 /dev/oracleasm/disks/DATA3

2 columns after group (asmadmin) refer to major/minor

e.g. for ASMDISK01 major = 8, minor = 17

# cat /proc/partitions
major minor #blocks name
8 0 83886080 sda
8 1 104391 sda1
8 2 5116702 sda2
8 3 5116702 sda3
8 4 1 sda4
8 5 73545538 sda5
8 16 93487104 sdf
8 17 4008186 sde1 --- Here you GO

Hence, partition /dev/sde1 is mapped to ASMDISK01.


2- /sbin/blkid |grep asm -- Very usefully --

/dev/mapper/ASM_EMC_XX_292Dp1: LABEL="VOTE" TYPE="oracleasm"
..
/dev/sdn1: LABEL="ARCH1" TYPE="oracleasm"
/dev/sdh1: LABEL="DATA6" TYPE="oracleasm"
/dev/sde1: LABEL="DATA3" TYPE="oracleasm"
/dev/sdd1: LABEL="DATA2" TYPE="oracleasm"
/dev/sda1: LABEL="DATA1" TYPE="oracleasm"
/dev/sdb1: LABEL="DATA7" TYPE="oracleasm"
/dev/sdy1: LABEL="DATA2" TYPE="oracleasm"
/dev/sdx1: LABEL="DATA11" TYPE="oracleasm"
/dev/sdw1: LABEL="DATA7" TYPE="oracleasm"
/dev/sdg1: LABEL="DATA4" TYPE="oracleasm"

[+ASM2] /dev/mpath > cat /proc/partitions |grep sdb1
   8       17  141419456 sdb1
[+ASM2] /dev/mpath >

Master node detail in Rac

- CRSd process on the Master node is responsible to initiate the OCR backup as per the backup policy

- Master node  is also responsible to sync OCR cache across the nodes
- CRSd process oth the master node reads from and writes to OCR on disk
- In case of node eviction, The cluster is divided into two sub-clusters. The sub-cluster containing fewer no. of nodes is evicetd. But, in case both the sub-clusters have same no. of nodes, the sub-cluster having the master node survives whereas the other sub-cluster is evicted.

Oracle ClusterWare master’s  information can be found


    - by scanning ocssd logs from various nodes
    - by scanning  crsd logs from various nodes.
    - by identifying the node which  takes the backup of the OCR.
    -the node that store OCR backups is the master node. 

    - With the help of V$GES_RESOURCE view .

If master node gets evicted/rebooted, another node becomes the master.

ORA-27301: OS failure message: No space left on device

Got this just now :) on one of  11 node cluster instance,

SQL> startup
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
SQL> exit

 We have 50+ database running on this cluster .after searching detail  on this error found one usefully oracle doc , make value as per  document .

This is caused by Kernel parameter setting. Since we were running many databases on a server, we should check our semaphore.


In our case it's

$ /sbin/sysctl -a | grep sem
error: permission denied on key 'kernel.cad_pid'
kernel.sem = 250 524288 256 2048

reset value as per above document.

run sysctl and check.
[root@test ~]# sysctl kernel.sem
kernel.sem = 275        524288  256     2048
[root@test ~]# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 2
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 32988817
kernel.shmmax = 67561097216
kernel.shmmni = 4096
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.ip_local_port_range = 1024 65500
kernel.msgmax = 8192
kernel.msgmni = 2878
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
kernel.shmmni = 4096
kernel.msgmnb = 65536
kernel.sysrq = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.ipv4.tcp_syncookies = 1
fs.aio-max-nr = 3145728
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.wmem_max = 1048576
kernel.sem = 275 524288 256 2048

Tried to start db again,
SQL> startup
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size                  2227072 bytes
Variable Size            1241515136 bytes
Database Buffers          402653184 bytes
Redo Buffers               23826432 bytes
Database mounted.
Database opened.

wow all set :)

ORA-17503: ksfdopn:2 Failed to open file

Today received one Critical Incident from  Oem 12C . The alerts message was Message=Health checker runs found new failures in 12 at time/line number: Wed Dec 18 23:49:17 2013/122332.

first i quick check crs status  . crsctl status cluster -all  this command  shows all cluster resource are online, since alerts was coming from only 1 node . i thought  asm alerts will give me some clue on this  . yes it give me ,below was key line in ASM alerts ,

Errors in file /XX/grid/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_XXX.trc:
ORA-17503: ksfdopn:2 Failed to open file +DATA/XXX/spfileXXX.ora
ORA-15173: entry 'XXX' does not exist in directory '/'

XXX is indicate here db name. 
after checking more detail found,  The error comes when db is droped and still it's register into OCR. after removing db from OCR byi srvcet remove .. Incident clear,

 

Archive Logs Are Not Copied To Standby solve issue from scratch

I debug this same issue at least once a week since May 2009 :), below step help to solve issue from scratch
We usually find below erro message on alert logfile.

————————————————————
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
————————————————————

Check:

That you are using password files
That the name of the password file is $ORACLE_HOME/dbs/orapw${ORACLE_SID}
That the password of sys is the same on all the servers involved in the archive copy
If one of the databases is 11g, make sure you set sec_case_sensitive_logon to false
Also check that you created the password file with ignore_case=y
I think this covers my usual debug process.


Never forget  11g has  case sensitive passwords.

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Today while running one Export job , got below issue . 

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "XXX"."XML_DOCUMENT"           3.716 GB 1501732 rows
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at 20:22:02
Job SYS_EXPORT_SCHEMA_01 has been reopened at Friday, 03 January, 2014 20:27
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at 20:27:11

System  and all TBS and MP has sufficient space .  after doing some test came to know issue was -

Cause:
If the dumpfiles are less than the parallel processes, then the dumpfile may be locked by one process and the other process waits for the dumpfile to write. And sometimes the process may not release the lock even if the process finished. So the entire task will not end as the other processes are waiting for the lock.

Solution:
Parallelism should be less than or equal to the number of the dumpfiles. Or avoid the usage of parallel option.
To avoid above confussion i use dumpfile=dumpfile_name_%U.dmp parallel=number of CPU process/2,3.4 ..and my job went fine which any issue .

quickly view reserved words for both SQL and PL/SQL

SQL> help reserved RESERVED

 WORDS (PL/SQL)

 -----------------------

 PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used

 for identifier names (unless enclosed in "quotes").

 An asterisk (*) indicates words are also SQL Reserved Words.

 ALL*            DESC*           JAVA            PACKAGE         SUBTYPE

 ALTER*          DISTINCT*       LEVEL*          PARTITION       SUCCESSFUL

 * AND*            DO              LIKE*           PCTFREE*        SUM

 ANY*            DROP*           LIMITED         PLS_INTEGER     SYNONYM*

 ARRAY           ELSE*           LOCK*           POSITIVE        SYSDATE*

 AS*             ELSIF           LONG*           POSITIVEN       TABLE*

 ASC*            END             LOOP            PRAGMA          THEN*

 AT              EXCEPTION       MAX             PRIOR*          TIME

 AUTHID          EXCLUSIVE*      MIN             PRIVATE         TIMESTAMP

 AVG             EXECUTE         MINUS*          PROCEDURE       TIMEZONE_ABBR

 BEGIN           EXISTS*         MINUTE          PUBLIC*         TIMEZONE_HOUR

 BETWEEN*        EXIT            MLSLABEL*       RAISE           TIMEZONE_MINUTE

 BINARY_INTEGER  EXTENDS         MOD             RANGE           TIMEZONE_REGION

 BODY            EXTRACT         MODE*           RAW*            TO*

 BOOLEAN         FALSE           MONTH           REAL            TRIGGER*

 BULK            FETCH           NATURAL         RECORD          TRUE

 BY*             FLOAT*          NATURALN        REF             TYPE

CHAR*           FOR*            NEW             RELEASE         UI CHAR_BASE    

FORALL          NEXTVAL         RETURN          UNION* CHECK*          FROM*        

NOCOPY          REVERSE         UNIQUE* CLOSE           FUNCTION        NOT*         

ROLLBACK        UPDATE* CLUSTER*        GOTO            NOWAIT*         ROW*         

USE COALESCE        GROUP*          NULL*           ROWID*          USER*

COLLECT         HAVING*         NULLIF          ROWNUM*         VALIDATE*

COMMENT*        HEAP            NUMBER*         ROWTYPE         VALUES*

COMMIT          HOUR            NUMBER_BASE     SAVEPOINT       VARCHAR*

COMPRESS*       IF              OCIROWID        SECOND          VARCHAR2*

CONNECT*        IMMEDIATE*      OF*             SELECT*         VARIANCE

CONSTANT        IN*             ON*             SEPERATE        VIEW*

CREATE*         INDEX*          OPAQUE          SET*            WHEN

CURRENT*        INDICATOR       OPEN            SHARE*          WHENEVER*

CURRVAL         INSERT*         OPERATOR        SMALLINT*       WHERE*

CURSOR          INTEGER*        OPTION*         SPACE           WHILE

DATE*           INTERFACE       OR*             SQL             WITH*

DAY             INTERSECT*      ORDER*          SQLCODE         WORK

DECIMAL*        INTERVAL        ORGANIZATION    SQLERRM         WRITE

DECLARE         INTO*           OTHERS          START*          YEAR

DEFAULT*        IS*             OUT             STDDEV          ZONE

DELETE*         ISOLATION

Database Link password Change in 11gR2

Before 11gR2 for changing  database link we have  to be drop and recreated db link  with an updated password.

Sample here is on the database where database link is located:

The password of the database link’s account has just been changed.

SQL> select count(*) from PUMP@DB_LiNK_CUST;
select count(*) from PUMP@DB_LiNK_CUST
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from DB_LiNK_CUST

SQL> alter database link DB_LiNK_CUST connect to dblink_source identified by dblink_source;

Database link altered.

SQL> select count(*) from PUMP@DB_LiNK_CUST;

COUNT(1)
----------
18762

This option is not available in the pre-11gR2.

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

Last month we just migrated an application from 10G to 11g. During a test of the send mail package using UTL_SMTP, we got this error, “ORA-24247: network access denied by access control list (ACL).” After a quick search, found below way to setup ALC in 11G.


1. The send mail package which executes the UTL_SMTP failed.

TEST_USER SQL> exec pkg_mails.SendMail('user@.com', 'Test Subject', 'Hello World');

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "pkg_mails", line 283
ORA-06512: at line 3

2. To fix it, an ACL has to be created.

The principal is the user or role to be added into this ACL. In this case, the TEST_USER account is added during the ACL creation. This field is case sensitive.

SQL> connect / as sysdba

begin
dbms_network_acl_admin.create_acl (
acl             => 'Resolve_Access.xml',      -- Name of the access control list XML file
description     => 'Resolve Network Access using UTL_INADDR',  -- Brief description
principal       => 'TEST_USER',               -- First user account or role being granted or denied permission
                                              --   this is case sensitive,
                                              --   but typically user names and roles are stored in upper-case letters
is_grant        => TRUE,                      -- TRUE = granted, FALSE = denied
privilege       => 'resolve',                 -- connect or resolve, this setting is case sensitive,
                                              --   so always enter it in lowercase
                                              --    connect if user uses the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL
                                              --    resolve if user uses the UTL_INADDR
start_date      => null,                      -- optional, null is the default
                                              --   in format of timestamp_with_time_zone (YYYY-MM-DD HH:MI:SS.FF TZR)
                                              --   for example, '2008-02-28 06:30:00.00 US/Pacific'
end_date        => null                       -- optional, null is the default
);

commit;
end;
/
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm#insertedID11

Note that the privilege used for UTL_INADDR is resolve in lowecase.


The description of each variable is clearly described in the Oracle-Base’s article.

3. Verify a newly-created ACL.

SQL> SELECT any_path
     FROM resource_view
     WHERE any_path like '%.xml';

ANY_PATH
--------------------------------------------------------------------------------
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
/sys/acls/ro_anonymous_acl.xml

4. Optionally you can add more users or roles into this ACL by using the add_privilege procedure. This is similar to the create_acl procedure except no description. Sample shown here is to add ADMIN_ADMIN_ROLE role.

begin
dbms_network_acl_admin.add_privilege (
acl           => 'Mail_UTL_Access.xml',
principal     => 'APP_ADMIN_ROLE',
is_grant      => TRUE,
privilege     => 'connect',
start_date    => null,
end_date      => null);

commit;
end;
/

5. Add a host and port range allowed.

begin dbms_network_acl_admin.assign_acl (
acl           => 'Resolve_Access.xml', -- Name of the access control list XML file to be modified
host          => '*',                   -- Network host to which this access control list will be assigned
-- This a host name or IP address or wild card name
lower_port    => null,                  -- (optional)
upper_port    => null);                 -- (optional)
commit;
end; /

SELECT utl_inaddr.get_host_name FROM dual;
GET_HOST_NAME
--------------------------------------------------------------------------------
hostname1

6. Test the send mail package again. This time there is no error, and the recipient receives email.

TEST_USER SQL> exec pkg_mails.SendMail('user@company.com', 'Test Subject', 'Hello World');

PL/SQL procedure successfully completed

Alter Statement Hangs

I found this tip from Oracle document while looking for a solution for session hung at the SQL prompt after issuing "alter tablespace read only". Usually "alter tablespace read only" executes very quickly. However, the likelihood cause of waiting for tablespace to become read-only is due to existing in-flight transactions started before "alter tablespace read only" are still running.

To identify these transactions that are preventing the read-only tablespace is to first identify the "alter tablespace read only" session.

SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND SQL_TEXT LIKE 'alter %';

SQL_TEXT                                 SADDR
---------------------------------------- ----------------
alter tablespace tbs_tts1 read only      0000040634C0D8B8

Based on the identified session address and start SCN number, we can find the earlier executions before the read-only statement by querying the v$TRANSACTION order by ascending start SCN.

SELECT SES_ADDR, START_SCNB
FROM V$TRANSACTION
ORDER BY START_SCNB;

SES_ADDR         START_SCNB
---------------- ----------
0000040634C254F8 2976616884    --> Waiting on this transaction
0000040634C0D8B8 2980274305    --> alter Statement
0000040634C53858 2980283454

From the session address of the blocking transaction, we then can find information about that session.

SELECT T.SES_ADDR, S.USERNAME, S.MACHINE
FROM V$SESSION S,  V$TRANSACTION T
WHERE T.SES_ADDR = S.SADDR
ORDER BY T.SES_ADDR

SES_ADDR          USERNAME              MACHINE
----------------  --------------------  ------------------
0000040634C254F8  RT_ADMIN              test1

RMAN-06169: could not read file header for datafile 236 error reason 4

Today I got a new problem in one of our 3 node cluster  database .During rman cold backup my job was failing -

RMAN-06169: could not read file header for datafile 236 error reason 4

+ [ -z '' ]

+ page_msg=abended

+ [ -z 'RMAN- or ORA- errors encountered.  Please check audit log' ]

--

Above it's clear that something wrong with datafile numbre  236.

 using v$recover_file checked datafile status.

SQL> select * from v$recover_file where error like '%FILE%';

  FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

---------- ------- ------- ----------------------------------------------------------------- ---------- ---------------

       236 OFFLINE OFFLINE FILE NOT FOUND                                                             0

--  Above is the key point "OFFLINE"

SQL> select file#,name from v$datafile where file#=236;

     FILE#

----------

NAME

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

       236

/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest                  string      +SHARED_DATA

As  db_create_file_dest define as +SHARED_DATA , it shows something wrong happen during adding this datafile. common cause is while add datafile if we given space between "add datafile ' +vale of db_create_file_dest'  this way datafile will create inside $ORACLE_HOME/dbs'. seem same happen here for this db also .

Let's fix this now .

SQL>  select file#,name from v$datafile where file#=236;

     FILE#

----------

NAME

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

       236

/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA

alter database create datafile '/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA' as '+SHARED_DATA'

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest                  string      +SHARED_DATA

SQL>

SQL> alter database create datafile '/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA' as '+SHARED_DATA'

  2  ;

alter database create datafile '/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA' as '+SHARED_DATA'

*

ERROR at line 1:

ORA-01136: specified size of file 236 (12800 blocks) is less than original size of 131072 blocks

ORA-01110: data file 236: '/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA'

No problem .

SQL> alter database create datafile '/d001/product/oracle/11.2.0.2/db/dbs/SHARED_DATA' as new;

Database altered.

SQL> select file#,name from v$datafile where file#=236;

     FILE#

----------

NAME

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

       236

+SHARED_DATA/XXXX/datafile/otd.1475.832988265