Thalai Muthu Oracle Apps DBA: May 2011

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

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

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

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

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

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

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

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

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

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

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

$ $INST_TOP/admin/scripts/adautocfg.sh

Source the Applications environment.
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.
Restart the Applications listener processes on each application tier node.
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.
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.
Set the $APPLCSF environment variable on all the Concurrent Processing nodes to point to a log directory on a shared file system.
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.)
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

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

SQL>shutdown immediate;

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

_lm_global_posts=TRUE
_immediate_commit_propagation=TRUE

Start the instances on all database nodes, one by one.
Start up the application services (servers) on all nodes.
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.
Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction managers.
Restart the concurrent managers.
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

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.

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

Example:-

Then restart your application tier it will work .