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 .