To Find Session Details

To find info about OS Process ID

top command will give you the PID info

ps -ef | grep PID

(It will give you the info. when it was created and which module it is)

select sid, serial#, program, osuser, client_identifier, module, machine, action from v$session 

where process='PID';

To check the Blocking Sessions:

select distinct decode(vlock.request,0,'Holder','Waiter') Type, vses.CLIENT_IDENTIFIER, vlock.block, vlock.request, vses.sid, vses.serial#, vses.status, round(vses.last_call_et/60) mins, vses.event, vses.module, vses.sql_id, vses.machine from v$session vses, v$lock vlock where vlock.sid=vses.sid and (vlock.id1, vlock.id2, vlock.type) in (SELECT id1, id2, type FROM v$lock where request>0) order by 1;

To Check the Concurrent Request Info

ps -ef | grep sqlplus  -- To know PID

oracle    12345   09876   0  22:12   pts/2    00:00:00   sqlplus

select sid, serial#, event, sql_id, blocking_session, blocking_instance from gv$session

where process='12345';

select inst_id, sql_id, program, module, action, last_call_et, status, event, blocking_session from gv$session where sid=SID and inst_id=ID;

--We can see the program, module and action

select audsid from gv$session where sid=SID and serial#=SerialNO;

select request_id, phase_code, status_code, actual_start_data, sysdate from fnd_concurrent_requests

where oracle_session_id=AUDSID;

Export / Import

 

Here I am creating a user and directory to export and import.

Creating a sample table and a row

Exporting  emp table to dba_directory as a dumpfile

Importing into same DB and Schema with new table name

New table created with export dump

Now I am updating a row

And adding a new row

====================================================================

Now exporting modified table with New dumpfile name 

Importing into the same table with option table_exists_action=append

We can see It has created duplicate records 

Again I am inserting a row in the same table

Exporting the table 

Importing the table into same table with data_option

Result

Now I am adding another row to the source table

Exported with New Dumpfile name

Importing into same target table

Result 

Importing with no options in the same table

Result

Oracle Database Architecture

How to apply EBS application patch in R12.1

ADPATCH in Oracle E-Business Suite

Check the location of adadmin and adpatch
$ which adadmin
$ which adpatch

Unzip the patch. And set the permission
$ unzip p30456216_R12.XDO.B_R12_GENERIC.zip
$ chmod 755 30456216

root@r12 ~]# su - oracle
$ sqlplus apps/*****
SQL> select * from AD_BUGS where bug_number='30456216';
no rows selected

SQL> select count(*) from dba_objects where status='INVALID';
SQL> select owner,object_name,object_type,status from dba_objects where status='INVALID';

Run the environment variable from application tier
$ cd /opt/oracle/VIS/apps/apps_st/appl/ or $APPL_TOP
$. ./APPSVIS_r12.en

Stop the application with adstpall.sh utility. Database and Listener should be up
./adstpall.sh

Enable the Maintenance Mode
/opt/oracle/VIS/apps/apps_st/appl/ad/12.0.0/bin
$./adadmin
Filename [admin.log]: patchnumber.log
provide system and apps password

Select an option 5 and press enter key to Change Maintenance Mode..
Then, Select an option 1 and press enter to Enable Maintenance Mode.
Press enter to continue.
Select option 3 and press Enter key to return to main menu .
Press enter to Exit AD Administration

Run autopatch (adpatch) from the patch directory by entering the following command:
$pwd
/opt/oracle/VIS/apps/apps_st/appl/ad/12.0.0/bin
$ ls -l adpatch
-rwxrwxrwx 1 oracle oinstall 9380 Mar 31  2009 adpatch
$./adpatch

After applying successfully patch disable the Maintenance mode as below.
Disabling options and choose as 5->2->3->6.

After disabling the Maintenance Mode, we need to start the services.
$ps –ef|grep applmgr |wc –l
Or
$ps –ef|grep FNDLIBR
Or
$ps –ef|grep FND*

SQL>Select count(*) from dba_objects where status='INVALID';
SQL> select owner,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where STATUS='INVALID';

If there is any new invalid objects then need to recompile.

Could not execute auto check for display colors

            Could not execute auto check for display colors using command / 

                                Check if the DISPLAY variable is set

Error:

Checking Temp space: must be greater than 500 MB.   Actual 74963 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 23830 MB    Passed

Checking monitor: must be configured to display at least 256 colors

    >>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set.    Failed <<<<

Sol:

1) log into console as root
2) DISPLAY=:0.0
3) export DISPLAY
4) xhost +
5) su - oracle
6) DISPLAY=:0.0
7) export DISPLAY

Now try to run the installer in VNC, it will work

Restore Database from RMAN backups

Restore the database from production backup

Start the database nomount stage

sqlplus / as sysdba

SQL> startup nomount pfile=’<location>/initPROD.ora’;


rman target /

RMAN>restore controlfile from '/u02/prod_backup/prod_Control_Copy.ctl';

RMAN>alter database mount;

RMAN>catalog start with '/u02/prod_backup/';

RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK clear; - This will clear the disk type from production RMAN configurations

RMAN>restore database preview;



Restoration Script:

run {

set newname for database to '/u01/oracle/test/db/data/%U';

set until scn 8854465393;

restore database;

SWITCH DATAFILE ALL;

recover database;

}

Rename the redo log files:

alter database rename file '/u01/oracle/prod/db/data/log05.dbf' to '/u01/oracle/test/db/data/log01.log';

alter database rename file '/u01/oracle/prod/db/data/log06.dbf' to '/u01/oracle/test/db/data/log02.log';

alter database rename file '/u01/oracle/prod/db/data/log07.dbf' to '/u01/oracle/test/db/data/log03.log';

alter database rename file '/u01/oracle/prod/db/data/log08.dbf' to '/u01/oracle/test/db/data/log04.log';


alter database clear unarchived logfile group 8;

Rename the tempfile:

alter database rename file '/u01/oracle/prod/db/data/temp01.dbf' to '/u01/oracle/test/db/data/temp01.dbf';

Create pfile

Change the database name to TEST

$nid target=sys/<sys_password> dbname=TEST

Rename the pfile to initTEST.ora

Change the database name to TEST

Start the database in nomount and create spfile.

Bounce the database

ADOP Phases