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

Daily Used Queries

To delete old / unused files:

find /u01/inst/apps/QAT/logs/appl/conc/out -type f -mtime +30 -exec rm -f {} \;    --To delete files older than 30 days

find /path/to/ -type f -mtime +7 -name '*.gz' -execdir rm -- '{}' \;                        --To delete gzip files older than 7days

find . -mtime +10 -print -exec gzip {} \;   --for gzip files                                       --To gzip files older than 10days

find . -mtime +10 | xargs  tar -czvPf  trace_log_$(date +%F).tar.gz                       --To tar files older than 10days

find . -type f -mtime +30 -exec rm -f {} \;                                                           --To remove files older than 30days

delete noprompt archivelog until time 'SYSDATE-15';                                         -- To remove archives from rman older than 15days

To check Inactive / Blocking sessions

select sid,serial#,username,status,schemaname,logon_time from v$session where status='INACTIVE' and username is not null;

select distinct vlock.id1,decode(vlock.request,0,'Holder','Waiter') Type,vses.client_identifier,vses.sid,vses.serial#,vses.inst_id,vses.status,round(vses.last_call_et/60) mins,vses.event,vses.module,vses.sql_id,vses.machine,vses.row_wait_obj#

from gv$session vses, gv$lock vlock

where vlock.sid=vses.sid and vlock.inst_id=vses.inst_id

and (vlock.id1,vlock.id2,vlock.type) in (select id1, id2, type from gv$lock where request>0)

and vlock.ctime/60 > 5

order by id1 ;

To Drop a User who is connected:

DECLARE

  v_user_exists NUMBER;

  user_name CONSTANT varchar2(20) := 'XXEIS';

BEGIN

  LOOP

    -- Kill any currently active sessions

    FOR c IN (SELECT s.sid, s.serial# FROM v$session s WHERE upper(s.username) = user_name)

    LOOP

      EXECUTE IMMEDIATE

        'alter system kill session ''' || c.sid || ',' || c.serial# || ''' IMMEDIATE';

    END LOOP;

    -- Try to drop the user.

    BEGIN

      EXECUTE IMMEDIATE 'drop user ' || user_name || ' cascade';

      EXCEPTION WHEN OTHERS THEN

      IF (SQLCODE = -1940) THEN

        -- Ignore ORA-01940 'Cannot drop user that is currently connected' exception

        NULL;

      ELSE

        RAISE;

      END IF;

    END;

    -- Continue looping, killing sessions and retrying to drop until successful

    BEGIN

      SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = user_name;

      EXIT WHEN v_user_exists = 0;

    END;

  END LOOP;

END;

/

To Get error from the files:

egrep -i "PLS-|ORA-|unab-|Unab-|Error-" *.log

grep -iE "ORA-|Error-|Unab-"  filename

Data Pump:

CREATE DIRECTORY XXSBIWFA AS '/u02/XXSBIWFA';

select *from dba_directories;

GRANT  read,  write  ON  DIRECTORY  XXSBIWFA  TO  XXSBIWFA;

GRANT  DATAPUMP_EXP_FULL_DATABASE  TO  hr;

GRANT  read,  write  ON  DIRECTORY  SBI_DIR  TO  XXEBS;

expdp \"/ as sysdba\" schemas=XXOCA_M directory=XXOCA_M_EXP dumpfile=XXOCA_M.dmp logfile=XXOCA_M.log

expdp XXSBIWFA/XXSBIWFA schemas=XXSBIWFA directory=XXSBIWFA dumpfile=XXSBIWFA3rdSep.dmp logfile=expdp_XXSBIWFA.log

impdp \'/ AS SYSDBA\' remap_schema=XXSBIWFA:XXSBIWFA2 dumpfile=XXSBIWFA3rdSep.dmp directory=XXSBIWFA  logfile=XXSBIWFAimp.log

select *from tab;

To Compile Invalid Objects:

select owner,object_name,object_type,status from dba_objects where STATUS='INVALID' and OWNER='XXSBIWFA';

EXEC DBMS_UTILITY.compile_schema(schema => 'XXSBIWFA2');

 @/u01/DEMO2_DB/db/tech_st/11.2.0/rdbms/admin/utlrp.sql

SELECT COUNT (*) FROM ALL_OBJECT WHERE STATUS='INVALID' ;

exec DBMS_UTILITY.compile_schema(schema => 'XXEIS');

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

EXEC DBMS_UTILITY.compile_schema(schema => 'XXSPLASH_STOWERSLST') ;

To Check the Quota of Tablespace Size:

set linesize 300

set pagesize 300

select a.tablespace_name, b.total,

       c.available, d.large_extent,

       (b.total - c.available) used,

       round((((b.total - c.available)*100)/b.total),2) pctused

  from (select tablespace_name, (sum(bytes)/1024)/1024 total

          from dba_data_files group by tablespace_name)  b,

       (select tablespace_name,  nvl(round(((sum(bytes)/1024)/1024),2),0) available

          from dba_free_space group by tablespace_name)  c,

       (select tablespace_name, (max(bytes)/1024)/1024 large_extent

          from dba_free_space group by tablespace_name)  d,

       dba_tablespaces  a

where a.tablespace_name = b.tablespace_name(+)

   and a.tablespace_name = c.tablespace_name(+)

   and a.tablespace_name = d.tablespace_name(+)

/

 select default_tablespace from dba_users where username='XXWFAOCA';

select file_name,file_id,bytes/1024/1024/1024 from dba_data_files where tablespace_name='TEMP';

To get version of File name at OS Level

strings -a | grep '$Header' file_name

adident Header file_name

Convert Trace files with TKPROF

tkprof <trace_File>.trc <tkprof>.txt sys=no explain=apps/<apps pwd>

ADOP options in 12.2

ADOP Options in 12.2

ADOP stands for Application DBA Online Patching and this was introduced in EBS R12.2
This is new feature that we can able to patch running system without taking any down time for a significant period of time
ADOP uses dual file system to support online patching

If you want to know all the available options in ADOP, try below command
$adop -examples

To check the status of ADOP session
$adop -status

To get the list of log files along with the snippet of the error message corresponding to each log file try this command
$adopscanlog -latest=yes

Onlilne Patching Cycle: Consists of 5phases which needs to be execute in order.
Source the environment variable
adop phase=prepare
adop phase=apply patches=000000
adop phase=finalize
adop phase=cutover
source the environment variable
adop phase=cleanup



Also multiple phases can be executed in a single adop command
Example: adop phase=finalize,cutover,cleanup

Prior to cutover phase, it is possible to execute additional 'apply' and 'finalize' phases as needed.
Exaple: adop phase=prepare
        adop phase=apply patches=000000
adop phase=finalize
adop phase=apply patches=000000
adop phase=cutover
adop phase=cleanup

If you apply any patches after the finalize, you need to run the finalize again before cutover

Parameters applicable to all phases:

workers=<number> Number of parallel workers used to execute tasks. Default value is computed according to CPU cores.
Example: adop phase=prepare workers=8

prompt=(yes/no) [default:yes]
    This parameter specifies whether adop should prompt for user input on warnings.
By default adop will ask user whether to continue or exit on some warning messages. If this parameter is set to 'no' adop will remain fully non-iterative, and will continue past any warning messages without user confirmation.
Example: adop phase=cutover prompt=no

Prepare Parameters:
skipsyncerror=(yes/no) [default:no]
    This specifies whether to ignore errors that may occur during incremental file system synchronization. This might happen if you applied a patch in the previous patching cycle
Example: adop phase=prepare skipsyncerror=yes

Apply Parameters:

apply=(yes/no) [default:yes]
    This parameter controls adop acually applies the patch. In test environment we can use this parameter to complete the patch cycle without applying the patch
Example: adop phase=apply apply=no patches=000000

Apply two patches in series:
adop phase=apply patches=123456,987654

Apply patch to a user-specific directory
patchtop=<directory-name> [default:$PATCH_TOP]
Example: adop phase=apply patches=000000 patchtop=/u01/fs_ne/EBSapps/patches

apply_mode=(online/downtime/hotpatch) [default:online]
 Use online mode to apply a patch to the patch edition during an online patching cycle.
 Downtime mode to apply a patch to the run edition when services are down.
 Hotpatch mode to apply a patch to the run edition when services are up and running. [Use only when Oracle recommends]

Example: adop phase=apply patches=000000 apply_mode=downtime


adop phase=fs_clone
It will check for any pending cleanup actions
It will clone patch file system from run file system
It will check if there are any blocking managed server ports