Cleanup ASM DISK Header Status

SQL> select header_status, path FROM V$ASM_DISK where header_status like 'FORMER';

HEADER_STATU PATH
------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FORMER       /dev/vx/rdsk/dev_data_dg/raw66g40301
FORMER       /dev/vx/rdsk/dev_data_dg/raw66g40302



bash-3.2$  dd if=/dev/zero of=/dev/vx/rdsk/dev_data_dg/raw66g40301 bs=1024 count=50
bash-3.2$ dd if=/dev/zero of=/dev/vx/rdsk/dev_data_dg/raw66g40301 bs=1024 count=50
50+0 records in
50+0 records out
bash-3.2$ dd if=/dev/zero of=/dev/vx/rdsk/dev_data_dg/raw66g40302 bs=1024 count=50
50+0 records in
50+0 records out



SQL> select header_status, path FROM V$ASM_DISK where header_status like 'CANDIDATE';

HEADER_STATU PATH
------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CANDIDATE    /dev/vx/rdsk/dev_data_dg/raw66g40302
CANDIDATE    /dev/vx/rdsk/dev_data_dg/raw66g40301

SP2-0667: Message file sp1.msb not found and SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

After Installing or Upgrading 12C or 11G you may get this error while connecting to sqlplus utility,  this error is because your sqlplus is not in your path.

Issue below commands and then retry.

export ORACLE_SID="Name of Database"
export ORACLE_HOME="Location of your home"
export PATH=$ORACLE_HOME/bin:$PATH.

Thanks.

Query to find Trace file name from Concurrent Request ID

column traceid format a8

column tracename format a80

column user_concurrent_program_name format a40

column execname format a15

column enable_trace format a12

set lines 80

set pages 22

set head off

SELECT 'Request id: '||request_id ,

'Trace id: '||oracle_Process_id,

'Trace Flag: '||req.enable_trace,

'Trace Name:

'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',

'Prog. Name: '||prog.user_concurrent_program_name,

'File Name: '||execname.execution_file_name|| execname.subroutine_name ,

'Status : '||decode(phase_code,'R','Running')

||'-'||decode(status_code,'R','Normal'),

'SID Serial: '||ses.sid||','|| ses.serial#,

'Module : '||ses.module

from fnd_concurrent_requests req, v$session ses, v$process proc,

v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,

fnd_executables execname

where req.request_id = &request

and req.oracle_process_id=proc.spid(+)

and proc.addr = ses.paddr(+)

and dest.name='user_dump_dest'

and dbnm.name='db_name'

and req.concurrent_program_id = prog.concurrent_program_id

and req.program_application_id = prog.application_id

and prog.application_id = execname.application_id

and prog.executable_id=execname.executable_id;

Tablespace Management

TABLESPACE MANAGEMENT

A)  Check existing tablespaces in database

$ sqlplus '/as sysdba'

SQL> select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMPTS1

USERS

 

B) Creating new tablespace in database

SQL> create tablespace rshore datafile '/u03/oradata/rshore/rshore1.dbf' size 25m;

 

Tablespace created.

 

Note: From oracle 9i onwards whenever we create tablespace that by default locally managed tablespace.

 

C) Check for created tablespace in database

 

SQL> select tablespace_name from dba_tablespaces where tablespace_name='RSHORE';

 

TABLESPACE_NAME

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

RSHORE

 

D) Create tablespace with autoallocate option

SQL> create tablespace findata datafile '/u03/oradata/rshore/findata01.dbf' size 25m extent management local autoallocate;

Tablespace created.

Note: AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.

E) Create tablespace with uniform option

SQL> create tablespace hrdata  datafile '/u03/oradata/rshore/hrdata01.dbf' size 25m extent management local uniform;

Tablespace created.                                                                                                                                                                   Note: The alternative to AUTOALLOCATE is UNIFORM. which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1M.

 

The following example creates a tablespace with uniform 128K extents

SQL> create tablespace crmdata  datafile '/u03/oradata/rshore/crmdata.dbf' size 25m extent management local uniform size 128k;

Tablespace created

F) Enabling Autoextend for a Tablespace

SQL> create tablespace mfgdata datafile '/u03/oradata/rshore/mfgdata01.dbf' size 10m autoeextend on next 10m maxsize 100m extent management local;

Tablespace created.                                                                                                                                                                  Note: This statement creates a tablespace named mfgdata with one datafile. When more space is required, 10m will be added up to a maximum size of 100 megabytes:

g) Specifying Segment Space Management for a Tablespace

SQL> create tablespace prcdata datafile '/u03/oradata/rshore/prcdata01.dbf' size 10m  extent management local segment space management auto;

Tablespace created.                                                                                                                                                                  Note: The following example creates a tablespace with automatic segment-space management.

H) Check all available tablespaces in database

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

USERS

RSHORE

FINDATA

HRDATA

MFGDATA

 

I)  Creating undo tablespace

 

SQL>  create undo tablespace undotbs2 datafile '/u03/oradata/rshore/undotbs02_01.dbf' size 50m;

Tablespace created.

 

J) Creating temporary tablespace

 

SQL> create temporary tablespace temp2 tempfile '/u03/oradata/rshore/temp2_01.dbf' size 50m;      

Tablespace created.

 

K) Add datafile to tablespace(increase tablespace size)

 

SQL> alter tablespace findata add datafile '/u03/oradata/rshore/findata02.dbf' size 25m;

Tablespace altered.

 

L) Resize tablespace datafile(increase tablespace size)

 

SQL> alter database datafile '/u03/oradata/rshore/findata02.dbf' resize 50m;

Database altered.

 

M)  Check datafiles in specific tablespace

 

SQL>  alter file_name from dba_data_files where tablespace_name=’FINDATA’;

 

FILE_NAME

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

/u03/oradata/rshore/findata01.dbf

/u03/oradata/rshore/findata02.dbf

 

Note: The above command will list all datafiles present in Tablespace.

 

M) Checking tempfile in temporary tablespaces

 

SQL> select FILE_NAME from dba_temp_files where TABLESPACE_NAME='TEMPTS1';

 

FILE_NAME

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

/u02/oradata/rshore/temp01.dbf

 

N) Keeping tablespace in read-only mode

 

SQL> alter tablespace findata read only;

Tablespace altered.

 

O) Check tablespace status

 

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='FINDATA';

 

TABLESPACE_NAME                STATUS

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

FINDATA                                     READ ONLY

 

P) Keep tablespace back to  online state

 

SQL> alter tablespace findata read write;

Tablespace altered.

 

Q) Check tablespace status

 

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='FINDATA';

 

TABLESPACE_NAME                STATUS

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

FINDATA                                    ONLINE

 

 

 

 R) Keeping tablespace offline mode

 

SQL> alter tablespace FINDATA offline;

Tablespace altered.

 

S) Check tablespace status

 

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='FINDATA';

 

TABLESPACE_NAME                STATUS

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

FINDATA                                    OFFLINE

 

T) Keep tablespace in online mode

 

SQL> alter tablespace FINDATA online;

Tablespace altered.

 

U) Check tablespace status

 

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='FINDATA';

 

TABLESPACE_NAME                STATUS

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

FINDATA                                    ONLINE

 

V) Dropping tablespace

 

SQL> drop tablespace MFGDATA;

Tablespace dropped.

 

Note: Above command just drop tablespace from database but it does not remove

Corresponding datafiles from operating system level.

 

W) Dropping tablespace with contents

 

SQL> drop tablespace PRCDATA including contents and datafiles;

Tablespace dropped.

 

X) Checking size of all the tablespaces

 

SQL> select tablespace_name, sum(bytes)/(1024*1024) from dba_data_files group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/(1024*1024)

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

SYSAUX                                            300

UNDOTBS1                                      200

HRDATA                                           25

USERS                                               100

SYSTEM                                            300

FINDATA                                          75

UNDOTBS2                                      50

RSHORE                                           25

 

Y) Checking size of specific tablespace

 

SQL> select tablespace_name, sum(bytes)/(1024*1024) from dba_data_files group by tablespace_name having tablespace_name='FINDATA';

 

TABLESPACE_NAME                SUM(BYTES)/(1024*1024)

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

FINDATA                                            75

 

Z) Checking free space in all tablespaces

 

SQL> select tablespace_name, sum(bytes)/(1024*1024) from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/(1024*1024)

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

SYSAUX                                        228.375

UNDOTBS1                                 83.0625

HRDATA                                      24

USERS                                          99

CRMDATA                                   24

SYSTEM                                       60.9375

FINDATA                                     73

UNDOTBS2                                 47.75

RSHORE                                       24

 

A) Check free space in specific tablespace

 

SQL> select tablespace_name, sum(bytes)/(1024*1024) from dba_free_space group by tablespace_name having tablespace_name='FINDATA';

 

TABLESPACE_NAME                SUM(BYTES)/(1024*1024)

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

FINDATA                                            73

 

B) Checking temporary tablespace size

 

SQL> select tablespace_name, sum(bytes)/1024/1024 mb

from dba_temp_files

group by tablespace_name; 

 

TABLESPACE_NAME                        MB

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

TEMP2                                           50

TEMPTS1                                       100

 

Concurrent Manager Status Codes and Phase Codes.

What are status codes and Phase codes of Concurrent Manager.

STATUS_CODE Column:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

PHASE_CODE column.
C Completed
I Inactive
P Pending
R Running


Above can be used for fnd_concurrent_requests table to get Concurrent Request status.

FDPSTP failed due to ORA-20100

FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, oXXXXXXXX.tmp in the directory, /usr/tmp

A TEST instance was already up and running and I created another production clone on the same server/machine. Once the services of both the instances  

The concurrent programs were completing in error and when checked the log files, the following error message was there:

ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o0031866.tmp in the directory, /usr/tmp.

You will find more information in the request log.

ORA-06512: at "APPS.FND_FILE", line 417

When investigated, I found that both the instnaces were creating .tmp files in /usr/tmp directory with the same name. This error was being thrown when one instance was trying to create .tmp file and a file with the same name was already created by the other instance.

• To resolve the issue I shutdown both the apps and db services of one instance.
• Created a directory 'temp' in '/usr/tmp' and changed the ownership of this dir to user owner of this instance 
• Logon to database as sysdba
• Create pfile from spfile
• modified UTL_FILE_DIR parameter's first entry from '/usr/tmp' to '/usr/tmp/temp'
• Created spfile from pfile
• Brought up the db and listener
• Now modified the $APPLPTMP variable in TEST_oratest.xml file from '/usr/tmp' to '/usr/tmp/temp'
• Run the autoconfig on apps tier/node
• Brought up the apps services
• Retested the issue and it was resolved 

Post Clone Steps In Oracle Applications

Post Clone Steps In Oracle Applications

The cloning process has traditionally been a manual process performed by DBAs. This process involves copying over the database files – preferably using RMAN DB duplicate, the tech stack, and the application tier to the non-production servers, converting them to DEV or TEST, and then performing a number of post clone steps such as disabling workflow emails from going to end users, scrambling financial information, updating the clone date and the instance name within the apps heading, editing the custom top, changing passwords, and others.  Depending upon the size and configuration the cloning process can take from 12 hours to 48 hours on average.

The number of post clone steps can get to be quite numerous and even with the best of documented procedures; one or two steps can easily be missed.

Note:-  the following is a list of steps in one particular cloning environment, It may vary based on environment.

1. Once the cloned database is renamed, add all the temp datafiles to the TEMP tablespace.

2. All key passwords need to be updated from their production values to the appropriate values for the DEV environment. (ie: sys,system, sysadmin,apps)

3. Custom database directories need to be re-created with the correct paths for the DEV server.

4. Specific Profile options need to be re-defined – like the apps heading naming the instance and the date stamp of the clone.

5. End-dating specific users so that only the desired developers have access to the instance.

6. Removing/scrambling specific private HR data.

7. Nulling out specific email addresses to prevent workflow from emailing end-users from the DEV instance.

8. Putting a hold on all regularly scheduled production concurrent requests in the DEV instance.

9. Updating all profile option values, swapping out the prod instance name for the DEV instance name. Additional updates for the server names also run.

10. Updates for all custom environment code paths and settings.

11. Updates for any and all printer setting changes.

Retrieve complete INST_TOP in Oracle Applications when lost

When complete INST_TOP is lost in Oracle Applications R12 we can retrieve it using below steps.


As context file also resides in INST_TOP first we have to regenerate Context file using adclonectx.pl under $COMMON_TOP/clone/bin.

1)First create necessary directory structure  for context file

$ mkdir -p /u01/applmgr/inst/Context_Name/appl/admin

2)Move your directory to $COMMON_TOP/clone/bin

$ cd $COMMON_TOP/clone/bin

3)Run adclonectx.pl script to regenerate contextfile

$ perl adclonectx.pl retrieve

Respond to Prompts as required

4)Check your Context file.

5)Now run adconfig.pl under $AD_TOP/bin

$ cd $AD_TOP/bin
$ perl adconfig.pl contextfile <Full path of contextfile>

6)Check whether all files are created under INST_TOP.



Note:- When we run adclonectx.pl script which resides under $AD_TOP/bin, this script may through an error "Please ensure that Classpath is set properly"

Differences between EXP,IMP and EXPDP,IMPDP

Differences between EXP,IMP and EXPDP,IMPDP

1)Impdp/Expdp has self-tuning utilities. Tuning parameters that were used in original Export and Import, such as BUFFER and RECORDLENGTH, are neither required nor supported by Data Pump Export and Import.

2)Data Pump represent metadata in the dump file set as XML documents rather than as DDL commands.
3)Impdp/Expdp use parallel execution rather than a single stream of execution, for improved performance.

4)In Data Pump expdp full=y and then impdp schemas=prod is same as of expdp schemas=prod and then impdp full=y where in original export/import does not always exhibit this behavior.

5)Expdp/Impdp access files on the server rather than on the client.

6)Expdp/Impdp operate on a group of files called a dump file set rather than on a single sequential dump file.

7)Sequential media, such as tapes and pipes, are not supported in oracle data pump.But in original export/import we could directly compress the dump by using pipes.

8)The Data Pump method for moving data between different database versions is different than the method used by original Export/Import.

9)When you are importing data into an existing table using either APPEND or TRUNCATE, if any row violates an active constraint, the load is discontinued and no data is loaded. This is different from original Import, which logs any rows that are in violation and continues with the load.

10)Expdp/Impdp consume more undo tablespace than original Export and Import.

11)If a table has compression enabled, Data Pump Import attempts to compress the data being loaded. Whereas, the original Import utility loaded data in such a way that if a even table had compression enabled, the data was not compressed upon import.

12)Data Pump supports character set conversion for both direct path and external tables. Most of the restrictions that exist for character set conversions in the original Import utility do not apply to Data Pump. The one case in which character set conversions are not supported under the Data Pump is when using transportable tablespaces.


13)There is no option to merge extents when you re-create tables. In original Import, this was provided by the COMPRESS parameter. Instead, extents are reallocated according to storage parameters for the target table.Di