22 May 2011

How To Find Out On Which Instance FNDSM Is Currently Running

How To Find Out On Which Instance FNDSM Is Currently Running?

Execute below Query :-

SELECT concurrent_queue_name, p.node_name, db_instance
  FROM fnd_concurrent_processes p, fnd_concurrent_queues q, fnd_cp_services s
 WHERE     p.queue_application_id = q.application_id
       AND p.concurrent_queue_id = q.concurrent_queue_id
       AND q.manager_type = s.service_id
       AND p.process_status_code = 'A';

Output:-

CONCURRENT_QUEUE_NAME NODE_NAME DB_INSTANCE
INVTMRPM LITHIUM UAT
INVTMRPM LITHIUM UAT
FNDSM_LITHIUM LITHIUM UAT
FNDICM LITHIUM UAT
INVTMRPM LITHIUM UAT
XDP_APPL_SVC LITHIUM
WFWSSVC LITHIUM
FNDCPOPP LITHIUM
WFALSNRSVC LITHIUM
WFMLRSVC LITHIUM
XDP_CTRL_SVC LITHIUM
XDP_Q_ORDER_SVC LITHIUM
XDP_Q_FA_SVC LITHIUM
XDP_Q_FE_READY_SVC LITHIUM
XDP_Q_EVENT_SVC LITHIUM
XDP_Q_IN_MSG_SVC LITHIUM
XDP_Q_TIMER_SVC LITHIUM
XDP_SMIT_SVC LITHIUM
FNDOPP10999 LITHIUM
FNDCRM LITHIUM UAT
XDP_MANAGER LITHIUM UAT
STANDARD2 LITHIUM UAT
STANDARD2 LITHIUM UAT
STANDARD2 LITHIUM UAT
STANDARD LITHIUM UAT
STANDARD LITHIUM UAT
STANDARD LITHIUM UAT
STANDARD LITHIUM UAT
STANDARD LITHIUM UAT
STANDARD LITHIUM UAT
REPORTS_lithium_7015 LITHIUM
XDP_Q_WI_SVC LITHIUM

16 May 2011

Step by Step Oracle Database Hot Backup Cloning

Step by Step Oracle Database Hot Backup Cloning.
Source Database :-

[oracle@testserver oracle]$ cd /oracle/product/10.2.0/db_1/
[oracle@testserver db_1]$ . profile
[oracle@testserver db_1]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 12:37:15 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  2021440 bytes
Variable Size             159385536 bytes
Database Buffers          281018368 bytes
Redo Buffers                2170880 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@testserver db_1]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 16-MAY-2011 12:38:03

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testserver.muthu.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                16-MAY-2011 12:38:05
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testserver.muthu.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@testserver db_1]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 12:39:21 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/archive
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oracle/create.bkp';

Database altered.

SQL>exit
[oracle@testserver oracle]$ mv create.bkp create.sql
[oracle@testserver oracle]$ chmod 755 create.sql
[oracle@testserver oracle]$ vi create.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/oradata/TEST/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/TEST/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/TEST/redo03.log'  SIZE 50M
-- STANDBY LOGFILE

DATAFILE
  '/oracle/oradata/TEST/system01.dbf',
  '/oracle/oradata/TEST/undotbs01.dbf',
  '/oracle/oradata/TEST/sysaux01.dbf',
  '/oracle/oradata/TEST/users01.dbf'
CHARACTER SET AL32UTF8
;

  
:wq
Creating Password File :-
Export  New Databse Home and Sid
[oracle@testserver dbs]$ export ORACLE_HOME=/oracle/product/10.2.0/db_1
[oracle@testserver dbs]$ export ORACLE_SID=TEST
[oracle@testserver dbs]$ export PATH=$PATH:$ORACLE_HOME/bin
 [oracle@testserver dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwTEST password=oracle

Source DATABASE :-

[oracle@testserver TEST]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 14:52:56 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database begin backup;

Database altered.

SQL>exit

Copy Source Location to Destination Location:-

[oracle@testserver oradata]$ cp -r ORA10g/ /oracle/oradata/TEST
[oracle@testserver oradata]$ cd TEST/
[oracle@testserver TEST]$ ls
control01.ctl  control02.ctl  control03.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@testserver TEST]$ rm -rf *.ctl
[oracle@testserver TEST]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 15:41:15 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database end backup;

Database altered.

SQL>ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Execute the below query you can get the archive log file location and Change: -

File change :-

select    max(first_change#) chng
from    v$archived_log;

Output:-

CHNG
614593

Archivelog file name and location :-

select name
from v$archived_log
where first_change# >= 614593
order by name;      

Output :-
NAME
/oracle/archive/1_10_744307208.dbf

[oracle@testserver TEST]$cd /oracle/product/10.2.0/db_1/admin
[oracle@testserver admin]$ mkdir TEST
[oracle@testserver admin]$ cd TEST/
[oracle@testserver TEST]$ mkdir adump  bdump  cdump  dpdump  pfile  udump
[oracle@testserver TEST]$ ls
adump  bdump  cdump  dpdump  pfile  udump
[oracle@testserver TEST]$cd dbs/
[oracle@testserver dbs]$ cp initORA10g.ora initTEST.ora
[oracle@testserver dbs]$ cp orapwORA10g orapwTEST
[oracle@testserver dbs]$ ls
hc_ORA10g.dat  initdw.ora  init.ora  initORA10g.ora  initTEST.ora  lkORA10G  orapwORA10g  orapwTEST  snapcf_ORA10g.f  spfileORA10g.ora

Init.ora:-

Change you old dbname to New name    Old DB Name  :-  ORA10G    New DB Name   :-  TEST

[oracle@testserver dbs]$ vi initTEST.ora
TEST.__db_cache_size=306184192
TEST.__java_pool_size=4194304
TEST.__large_pool_size=4194304
TEST.__shared_pool_size=125829120
TEST.__streams_pool_size=0
*.audit_file_dest='/oracle/product/10.2.0/db_1/admin/TEST/adump'
*.background_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/oradata/TEST/control01.ctl','/oracle/oradata/TEST/control02.ctl','/oracle/oradata/TEST/control03.ctl'
*.core_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='TEST'
*.db_recovery_file_dest='/oracle/product/10.2.0/db_1/flash_recovery_area1'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=146800640
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=441450496
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/udump'
:wq!
[oracle@testserver dbs]$cd ../
[oracle@testserver db_1]$ mkdir flash_recovery_area1
[oracle@testserver oracle]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 14:20:30 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  2021440 bytes
Variable Size             134219712 bytes
Database Buffers          306184192 bytes
Redo Buffers                2170880 bytes
SQL> CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS  ARCHIVELOG
  2  MAXLOGFILES 16
  3  MAXLOGMEMBERS 3
  4  MAXDATAFILES 100
  5  MAXINSTANCES 8
  6  MAXLOGHISTORY 292
  7  LOGFILE
  8  GROUP 1 '/oracle/oradata/TEST/redo01.log'  SIZE 50M,GROUP 2 '/oracle/oradata/TEST/redo02.log'  SIZE 50M,GROUP 3 '/oracle/oradata/TEST/redo03.log'  SIZE 50M
  9  DATAFILE
 10  '/oracle/oradata/TEST/system01.dbf','/oracle/oradata/TEST/undotbs01.dbf','/oracle/oradata/TEST/sysaux01.dbf','/oracle/oradata/TEST/users01.dbf'
 11  CHARACTER SET AL32UTF8;

Control file created.

SQL>recover database using backup controlfile until cancel;
ORA-00279: change 617205 generated at 05/16/2011 15:42:39 needed for thread 1
ORA-00289: suggestion :
/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2011_05_16/o1_mf
_1_11_%u_.arc
ORA-00280: change 617205 for thread 1 is in sequence #11


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/archive/1_10_744307208.dbf AUTO
ORA-00310: archived log contains sequence 10; sequence 11 required
ORA-00334: archived log: '/oracle/archive/1_10_744307208.dbf'


SQL> alter database open resetlogs;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/TEST/temp01.dbf' SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.
SQL> select status from v$instance;

STATUS
------------
OPEN
SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
SYSTEM
SQL> alter database rename global_name to TEST;

Database altered.

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 444596224 bytes
Fixed Size                  2021440 bytes
Variable Size             134219712 bytes
Database Buffers          306184192 bytes
Redo Buffers                2170880 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@testserver dbs]$ nid target=/

DBNEWID: Release 10.2.0.1.0 - Production on Mon May 16 16:01:15 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to database TEST (DBID=4063179336)

Connected to server version 10.2.0

Control Files in database:
    /oracle/oradata/TEST/control01.ctl
    /oracle/oradata/TEST/control02.ctl
    /oracle/oradata/TEST/control03.ctl

Change database ID of database TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 4063179336 to 2049482059
    Control File /oracle/oradata/TEST/control01.ctl - modified
    Control File /oracle/oradata/TEST/control02.ctl - modified
    Control File /oracle/oradata/TEST/control03.ctl - modified
    Datafile /oracle/oradata/TEST/system01.dbf - dbid changed
    Datafile /oracle/oradata/TEST/undotbs01.dbf - dbid changed
    Datafile /oracle/oradata/TEST/sysaux01.dbf - dbid changed
    Datafile /oracle/oradata/TEST/users01.dbf - dbid changed
    Datafile /oracle/oradata/TEST/temp01.dbf - dbid changed
    Control File /oracle/oradata/TEST/control01.ctl - dbid changed
    Control File /oracle/oradata/TEST/control02.ctl - dbid changed
    Control File /oracle/oradata/TEST/control03.ctl - dbid changed
    Instance shut down

Database ID for database TEST changed to 2049482059.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

[oracle@testserver dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 16:01:35 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 444596224 bytes
Fixed Size                  2021440 bytes
Variable Size             134219712 bytes
Database Buffers          306184192 bytes
Redo Buffers                2170880 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@testserver dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 16:03:25 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 TEST
testserver.muthu.com
10.2.0.1.0        16-MAY-11 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO
 SQL>exit
[oracle@testserver dbs]$ ps -ef |grep smon
oracle    7404     1  0 12:37 ?        00:00:06 ora_smon_ORA10g
oracle   17265     1  0 16:02 ?        00:00:01 ora_smon_TEST
oracle   17380 16346  0 16:16 pts/2    00:00:00 grep smon
[oracle@testserver dbs]$

Listerner Entry :-
[oracle@testserver db_1]$ cd network/admin/
[oracle@testserver admin]$ vi listener.ora
# listener.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/listener.ora
SID_LIST_TEST =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TEST)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME = TEST)
    )
  )
TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testserver.muthu.com)(PORT = 1522))
  )

:wq
Tns Entry :-
[oracle@testserver admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testserver.muthu.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )
:wq

10 May 2011

Step by Step Configure Oracle Parallel Concurrent Processing ( Oracle PCP )

3.1 Configure Parallel Concurrent Processing

3.1.1 Check prerequisites for setting up Parallel Concurrent Processing


To set up Parallel Concurrent Processing (PCP), you must have more than one Concurrent Processing node in your environment

3.1.2 Set Up PCP


  1. Edit the applications context file via Oracle Applications Manager, and set the value of the variable APPLDCP to ON.
  2. Execute AutoConfig by running the following command on all concurrent processing nodes:

$ $INST_TOP/admin/scripts/adautocfg.sh

  1. Source the Applications environment.
  2. Check the tnsnames.ora and listener.ora configuration files, located in $INST_TOP/ora/10.1.2/network/admin. Ensure that the required FNDSM and FNDFS entries are present for all other concurrent nodes.
  3. Restart the Applications listener processes on each application tier node.
  4. Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator Responsibility. Navigate to Install > Nodes screen, and ensure that each node in the cluster is registered.
  5. Verify that the Internal Monitor for each node is defined properly, with correct primary and secondary node specification, and work shift details. For example, Internal Monitor: Host2 must have primary node as host2 and secondary node as host3. Also ensure that the Internal Monitor manager is activated: this can be done from Concurrent > Manager > Administrator.
  6. Set the $APPLCSF environment variable on all the Concurrent Processing nodes to point to a log directory on a shared file system.
  7. Set the $APPLPTMP environment variable on all the CP nodes to the value of the UTL_FILE_DIR entry in init.ora on the database nodes. (This value should be pointing to a directory on a shared file system.)
  8. Set profile option 'Concurrent: PCP Instance Check' to OFF if database instance-sensitive failover is not required. By setting it to 'ON', a concurrent manager will fail over to a secondary Application tier node if the database instance to which it is connected becomes unavailable for some reason.

 


3.1.3 Set Up Transaction Managers ,Internal Concurrent Manager ,Standard Manager


  1. Shut down the application services (servers) on all nodes
  2. Shut down all the database instances cleanly in the Oracle RAC environment, using the command:

SQL>shutdown immediate;

  1. Edit $ORACLE_HOME/dbs/<context_name>_ifile.ora. Add the following parameters:

      • _lm_global_posts=TRUE
      • _immediate_commit_propagation=TRUE

  1. Start the instances on all database nodes, one by one.
  2. Start up the application services (servers) on all nodes.
  3. Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator responsibility. Navigate to Profile > System, change the profile option ‘Concurrent: TM Transport Type' to ‘QUEUE', and verify that the transaction manager works across the Oracle RAC instance.
  4. Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction managers.
  5. Restart the concurrent managers.
  6. If any of the transaction managers are in deactivated status, activate them from Concurrent > Manager > Administrator.
Transaction Manager (Internal use only)

Standard Manager    


Internal Concurrent Manager      


 
Internal Monitor for Apps-1


Internal Monitor for Apps-2

CRP Manager


3.1.4 Set Up Load Balancing on Concurrent Processing Nodes


  1. Edit the applications context file through the Oracle Applications Manager interface, and set the value of Concurrent Manager TWO_TASK (s_cp_twotask) to the load balancing alias (OURPROD_balance>).

Note:  Windows users must set the value of "Concurrent Manager TWO_TASK" (s_cp_twotask context variable) to the instance alias.

  1. Execute AutoConfig by running $INST_TOP/admin/scripts/adautocfg.sh on all concurrent nodes.

Example:-

Then restart your application tier it will work .