Thursday, July 24, 2014

crsctl command to start and stop crs in Oracle RAC Database


Hi DBAs,

We basically have the below command to stop/start crs in Oracle RAC Database.

crsctl command to stop and start the cluster on a specific node noderac1 & noderac2:


We need to login with 'root' or specific sudo user having permissions to run the crs commands.

/u01/app/11.2.0.3/grid/bin/crsctl stop crs
/u01/app/11.2.0.3/grid/bin/crsctl disable crs

/u01/app/11.2.0.3/grid/bin/crsctl enable crs
/u01/app/11.2.0.3/grid/bin/crsctl start crs

where /u01/app/11.2.0.3/grid =$GRID_HOME
 
crsctl command to stop and start on the Clusterware(noderac1 &noderac2) will be

/u01/app/11.2.0.3/grid/bin/crsctl stop cluster -all
 /u01/app/11.2.0.3/grid/bin/crsctl disable crs

/u01/app/11.2.0.3/grid/bin/crsctl enable crs
/u01/app/11.2.0.3/grid/bin/crsctl start resource -all

where /u01/app/11.2.0.3/grid =$GRID_HOME


Enjoy doing RAC tasks...

Thanks,

Rafi

How to check Pre-requistics Patches while applying Database Patch using opatch

Hi DBAs,

Lot of time we apply Database patches using the opatch,It is mandatory to know the Pre-requistics patches for applying the Patch.We can find it out with the below steps.


Step 1:Go to the Directory where patch is copied(PATCH TOP)
>cd /home/oracle/patch/18308717

Step 2:Execute below command
>$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/11.2.0.3/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
node1(TESTDB1)  /home/oracle/patch/18308717
>ls -altr
total 24
-rw-rw-r-- 1 oracle oinstall 5905 May 14 03:35 README.txt
drwxr-xr-x 3 oracle oinstall 4096 May 14 03:35 files
drwxr-xr-x 4 oracle oinstall 4096 May 14 03:35 etc
drwxrw-rw- 3 oracle oinstall 4096 Jul 24 11:30 ..
drwxrwxr-x 4 oracle oinstall 4096 Jul 24 11:30 .
node1(TESTDB1)  /home/oracle/patch/18308717


Step 3:Verify the Log

>vi /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log

[Jul 24, 2014 5:52:50 PM]    PREREQ session
[Jul 24, 2014 5:52:50 PM]    OPatch invoked as follows: 'prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc '
[Jul 24, 2014 5:52:50 PM]    OUI-67077:
                             Oracle Home       : /u01/app/oracle/product/11.2.0.3/dbhome_1
                             Central Inventory : /u01/app/oraInventory
                                from           : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc
                             OPatch version    : 11.2.0.3.6
                             OUI version       : 11.2.0.3.0
                             OUI location      : /u01/app/oracle/product/11.2.0.3/dbhome_1/oui
                             Log file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log
[Jul 24, 2014 5:52:50 PM]    Patch history file: /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
[Jul 24, 2014 5:52:50 PM]    Invoking prereq "checkconflictagainstohwithdetail"
[Jul 24, 2014 5:52:51 PM]    Patch 18308717 is not subset of any other patch processed till now
[Jul 24, 2014 5:52:51 PM]    Patch ID  18308717
[Jul 24, 2014 5:52:51 PM]    Patches that conflict: [  ]
[Jul 24, 2014 5:52:51 PM]    Checking conflicts for patch: 18308717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:18308717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:17076717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:17296419
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:16475860
[Jul 24, 2014 5:52:51 PM]    Prereq "checkConflictAgainstOHWithDetail" passed.
[Jul 24, 2014 5:52:51 PM]    Finishing PrereqSession at Thu Jul 24 17:52:51 IST 2014 

Message:Patch 18308717 is not subset of any other patch processed till now 
From the above logs we can confirm that there are no-prerequistics patches to be applied before applying 18308717.


Enjoy doing DBA tasks...

Happy DBA tasks..

Thanks,

Rafi











Friday, July 4, 2014

How to activate Periodic Alert Scheduler in Oracle EBS Application


Hi Apps DBAs,

 We can activate Periodic Alert Scheduler in Oracle EBS Application

Step 1:Login to ORACLE EBS Application HOME PAGE with Alert Manager Responsibility

We need to Login the Oracle EBS Application HOME PAGE with Alert Manager Responsibility or else assign the responsibility to the User

In the Database we can login with 'Apps' user and check the user exists

SQL> SELECT USER_NAME from fnd_user where  USER_NAME like '%RAFI%';

USER_NAME
--------------------------------------------------------------------------------
RAFI

How to check responsibilities assigned to a user:

SQL> SELECT B.RESPONSIBILITY_NAME
     FROM FND_USER_RESP_GROUPS A,
     FND_RESPONSIBILITY_VL B,
     FND_USER C
     WHERE A.responsibility_id = B.responsibility_id AND
     C.user_id = A.user_id AND
     (to_char(A.end_date) IS NULL
     OR A.end_date > sysdate)
     AND C.user_name like '%RAFI%';


RESPONSIBILITY_NAME
--------------------------------------------------------------------------------
TL System Administrator (Read Only)
TL System Administrator
Alert Manager

Step 2: We need to have alert Manager Responsibily to do this task.

We need to Navigate to Alert Manager Responsibility

Navigate to alert ->Define ;Go to Request->Schedule

there we can see periodic Alert Scheduler =>Active it (Click on Activate button)

Screenshot below for reference.





Enjoy Apps DBA tasks....


Thanks,

Rafi






















































Sunday, June 29, 2014

Killing multiple sessions in Oracle Database

Hi,

We often get task to kill sessions which are active,inactive and the session related with a particular program.When we have one or two sessions we can kill it easily,if we have multiple sessions we can do it as follows:


Step 1:Check the Name of the Database
sqlplus "/as sysdba"

SQL>Select name from v$database;

Step 2:How to kill all inactive session in Database

sqlplus "/as sysdba"

SQL>set heading off

SQL>spool kill12.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
 FROM v$session
 WHERE status ='INACTIVE' and type != 'BACKGROUND';

SQL>spool off

then execute the sql script

SQL> @kill12.sql

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

How to kill all active session in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' and type != 'BACKGROUND';

SQL>spool off

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to kill all ODI sessions in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>set lines 1000
SQL>set linesize 2000
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' AND USERNAME LIKE '%ODI%';

SQL>spool off


Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to get the list of Users and Processes running ODI sessions:

SQL> SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,s.sid,s.serial#,p.spid, s.username, s.program
 FROM   gv$session s
 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' AND S.USERNAME LIKE '%ODI%'; 

 How to kill a particular object blocking session:

 1.Find the tables(objects) which are locked:

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id and o.object_name='XX_OBJECT';

2.Killing the session holding the lock:

--Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID=667;

SERIAL#
----------
21091

SQL> alter system kill session '667,21091';

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

Enjoy DBA tasks...

Happy DBA learning..


Best regards,

Rafi






Wednesday, June 18, 2014

Reorganization of table in Oracle Database

Hi All,


As we all know reorganization help in better disk space management and performance management in oracle Database,so this task is often done in companies for Tables,Tablespaces and Databases.It helps to reduce the fragmentation( which affects the performance of the Oracle).

Below are the Steps I follow for doing reorganization:

Step 1:Check the last analyzed, tables size before Reorganization:


-- Query 1:Check the last Analyzed and number of rows:
SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 06-JUN-14 NO 71374990


--Query 2 :Check the size of the table

SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 41643 5330304


Note: top -c enter 1 to get the list of cpus =>8 we can give parallel upto 16


ALTER INDEX APPS.XHL_ORDER_TAB_NU5 REBUILD NOLOGGING PARALLEL 12


Note:CUSTOM_SCHEMA,APPS and ORD_SCHEMA are the important schemas used.

Step 2: Get the list of all the INDEXES associated with the tables used for Reorganization.

set lines 2000
set linesize 2000
select 'alter index ORD_SCHEMA.'||index_name||' rebuild NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


If db is up:
=================


select 'alter index ORD_SCHEMA.'||index_name||' rebuild online NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


INDEXES NEEDS TO BE REBUILDED:
=================================


--alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild online parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;



Step 3:Make sure to make it noparallel and  logging after indexes are rebuild:

Make it no parallel to release the CPU channels

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';


Step 3:Move the tables to reduce fragmentation.

SQL> spool move_table_10June2014.out
SQL> ALTER TABLE ORD_SCHEMA.ORDER_TAB MOVE;
SQL> spool off

Table altered.



Step 4: Rebuild indexes online using VNC session(vncserver)

We need to rebuild indexes using VNC session or run it in the form of shell scripts using nohup as this operation takes time.

spool rebuild_10Jun2014.out
alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;
spool off


Step 5:Check index status again after rebuild

Check index status again after rebuild,if they are in unusable status for some reason,please rebuild them again and make sure
it comes to VALID Status.



SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
APPS                           ORDER_TAB_NU17      UNUSABLE
ORD_SCHEMA                            ORDER_TAB_F2        UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU3   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N10           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N11           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N12           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U1            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N6            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U2            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU1   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N9            UNUSABLE

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
CUSTOM_SCHEMA                           XHL_ORDER_TAB_NU4   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N1            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU2   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N7            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N8            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N2            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N3            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N4            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N5            UNUSABLE
APPS                           XHL_ORDER_TAB_NU5   UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_R1    UNUSABLE

22 rows selected.


To check unusable indexes for table:
==========================================


SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

no rows selected

SQL>




Step 6:Make it no parallel and release the channels:

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';





Step 7:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');


Step 8:Run the gather stats for the tables in VNC session:

We run gather statistics to choose the best posible execution plan.

spool gather_stats_tables_TESTDB.out



sqlplus "/as sysdba"

SQL>

Begin DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'ORD_SCHEMA',
tabname => 'ORDER_TAB',
degree => 10,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/


Step 9:Verify the number of rows,statistics ,tables size after reorganization

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 11-JUN-14 NO 71348700

SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 42534 5444352




Hope it helps.Enjoy DBA tasks...



Thanks,

Rafi

Thursday, February 13, 2014

Changing the look and feel of Oracle EBS environment



Changing the look and feel of Oracle EBS environment:

Changing the Oracle EBS look and feel help us to change the colour of the HTML and java based form page in Oracle EBS,the advantage is,it helps us differentiate between PRODUCTION and NON-PRODUCTION environment .

We can follow the below steps to achieve it:


Step 1:Login to Oracle EBS Home page with ‘sysadmin’ user.

Step 2:Navigate to  System Administrator Responsibility =>system profile form page

Step 3:Navigate to the System Profile option Name =>Java Look and Feel
And Site =>oracle   ,Screenshot below for reference.



Step 4: :Navigate to the System Profile option Name =>Java Colour scheme
And site =>teal
This choose the teal colour   ,Screenshot below for reference



Step 5: :Navigate to the System Profile option Name ,profile to change the =>Oracle Applications Look and Feel
And site =>Base Desktop look and feel.  ,Screenshot below for reference.



Step 6:Signout and login Oracle EBS application again to see the new look and feel it.

Changing the look and feel of Oracle EBS environment from Backend:

We can do the same task from backend,where we have the requirement to do it frequently as below:

Step 1:Creat a sql file banner_colour_change.sql

banner_colour_change.sql
spool banner_colour_change.out
UPDATE fnd_profile_option_values fpv
SET fpv.PROFILE_OPTION_VALUE='TEAL'
WHERE fpv.PROFILE_OPTION_ID in ( SELECT t2.PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS_TL t1,FND_PROFILE_OPTIONS t2
WHERE t1.USER_PROFILE_OPTION_NAME = 'Java Color Scheme'
AND t1.LANGUAGE = 'US' AND t2.PROFILE_OPTION_NAME = t1.PROFILE_OPTION_NAME);
commit;
spool off
exit


Step 2:Create a shell script as Apps_DBA_Scripts.sh

Apps_DBA_Scripts.sh
[ rafidba@node1 /home/rafidba/DBA_SCRIPTS ] vi Apps_DBA_Scripts.sh
.  $HOME/.bash_profile
sqlplus apps/apps_pass @/home/rafidba/DBA_SCRIPTS/banner_colour_change.sql


Step 3:Execute the Apps_DBA_Scripts.sh with nohup

nohup sh Apps_DBA_Scripts.sh>Apps_DBA_Scripts.out &


Step 4:Monitor and Validate the logs

tail -f Apps_DBA_Scripts.out 
less  Apps_DBA_Scripts.out 


Step 5:Check the colour by login to Oracle EBS page by opening form page

Now we can login to Oracle EBS Home Page and click any form page,the colour must have changed to TEAL.

 


 

 Enjoy Apps DBA tasks,Happy Apps DBA learning...
 


Best regards,

Rafi.



Sunday, February 2, 2014

Using Data mover psdmt utility to refresh table data or move data from one evironment to other

 Hi DBAs,

PSDMT (Data mover) utility is very useful to refresh table data from one environment to other.In the below example DEVDB(Source) and TESTDB(Target).



We can prefer this utility when we have huge amount of tables data to move from one environment to other.
.
Step 1:Go to Peoplesoft Datamover client utility psdmt.
We have to go to People Tools Home and navigate to psdmt.exe


Step 2:Login to Datamover with DEVDB(source) as below:
Login to DEVDB(Source):

user:ADM1
pass:Welcome123

Note:Remove files from temp directory  before performing below steps.
=====


Step 3: Copy the below script in Datamover

Set log c:\temp\log_exp.log;
Set output c:\temp\data.dat;
Export SGA_BVN_MAPPING;
Export SRVC_GROUP_AREA;


Step 4: Import into TESTDB database using datamover(psdmt)

Login to TESTDB(Target):

User:ADM1
pass:welcome123

Step 5:Copy the below script in Datamover

Set log c:\temp\log_imp.log;
Set input c:\temp\data.dat;
import SGA_BVN_MAPPING;
import SRVC_GROUP_AREA;

Step 6:Verify the COUNT OF ROWS in tables on source(DEVDB) and target(TESTDB):
SOURCE:DEVDB

CONN USER1 and give below:

SQL> select name from v$database;

NAME
---------
DEVDB

SQL> select count(*) from USER1.SGA_BVN_MAPPING;

  COUNT(*)
----------
    262742

SQL> select count(*) from USER1.SRVC_GROUP_AREA;

  COUNT(*)
----------
   9864049

TARGET:(TESTDB)

CONN USER1 and give below:

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select count(*) from USER1.SGA_BVN_MAPPING;

  COUNT(*)
----------
    262742

SQL> select count(*) from USER1.SRVC_GROUP_AREA;

  COUNT(*)
----------
   9864049

Step 7:Send mail
Completed refresh of tables SGA_BVN_MAPPING and SRVC_GROUP_AREA in TESTDB.


Enjoy PeopleSoft DBA tasks.



Best regards,
Rafi