Friday, January 28, 2011

Create Control file in 11g. Diff between Resetlogs & Noresetlogs when creating control file


In 11g, a new parameter called "diagnostic_dest" determines where files will are created.

In 11g, diagnostic_dest replaces background_dump_dest, user_dump_dest and core_dump_dest.

Two ways to backup Control File:

1. Control file in binary format.
2. Control file in human readable format.

Backing up the contol file in binary format.

sql> alter database backup controlfile to '/some/arbitrary/path';

Backing up the contol file in human readable format.

sql > alter database backup control file to trace;
This will give you control file script.
check you diagnostics location for obtaining the trace file and will be of name SID_ora_pid.trc

If you want trace file to be generated to some other location apart from diagnostics location

sql> alter database backup controlfile to trace as controlfile.txt;
By default this will create controlfile in $ORACLE_HOME/dbs location.

sql> alter database backup controlfile to trace as '/some/arbitrary/path';

This will create control file script the location you have mentioned.


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "demo" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 449
LOGFILE
  GROUP 1 '/path/oracle/dbs/t_log1.f'  SIZE 500K,
  GROUP 2 '/path/oracle/dbs/t_log2.f'  SIZE 500K
# STANDBY LOGFILE
DATAFILE
  '/path/oracle/dbs/t_db1.f',
  '/path/oracle/dbs/dbu19i.dbf',
  '/path/oracle/dbs/tbs_11.dbf',
  '/path/oracle/dbs/smundo.dbf',
  '/path/oracle/dbs/demo.dbf'
CHARACTER SET WE8DEC
;

NOTE : Donot leave any line empty while in control file script otherwise it will throw error SP-

Diff Betwen Reuse and set Database option of control file

REUSE : Specify REUSE to indicate that existing control files identified by the initialization
parameter CONTROL_FILES can be reused and overwritten.

SET : Use SET DATABASE to change the name of the database.

ARCHIVELOG | NOARCHIVELOG

Specify ARCHIVELOG to archive the contents of redo log files before reusing them.
This clause prepares for the possibility of media recovery as well as instance or system failure recovery.
If you omit both the ARCHIVELOG clause and NOARCHIVELOG clause, then Oracle Database chooses noarchivelog mode by default.
After creating the control file, you can change between archivelog mode and noarchivelog mode with the ALTER DATABASE statement.


RESETLOGS

Specify RESETLOGS if you want Oracle Database to ignore the contents of the files listed in the LOGFILE clause.These files do not have to exist. Each redo_log_file_spec in the LOGFILE clause must specify the SIZE parameter.The database assigns all online redo log file groups to thread 1 and enables this thread for public use by any instance.After using this clause, you must open the database using the RESETLOGS clause of the ALTER DATABASE statement.

NORESETLOGS

Specify NORESETLOGS if you want Oracle Database to use all files in the LOGFILE clause as they were when the database was last open.These files must exist and must be the current online redo log files rather than restored backups.The database reassigns the redo log file groups to the threads to which they were previously assigned and reenables the threads as they were previously enabled.

Alter database open Resetlogs/Noresetlogs.

RESETLOGS : resets the current log sequence number to 1 and invalidates all redo entries in the online and
archived redo log files. You must use this option to open the database after performing media recovery
with a backup controlfile. After opening the database with this option you should perform a complete database backup.

NORESETLOGS

leaves the log sequence number and redo log files in their current state.




Troubleshoot long running concurrent request in Oracle Apps 11i/R12



Step 1 : Check Concurrent Request ID of long running concurrent request from front end
Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)
Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)
Step 4 : Disable trace (once you are happy with trace size)
Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu
Step 6 : Check TKPROF out file to find root cause of slow concurrent request


Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 2355)

Step 2 : Run below command to find SPID, provide concurrent request ID (2355 in my case) when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = ‘R’;

   REQUEST_ID  SID      SERIAL#    OSUSER       PROCESS    SPID
   —————-
   2355        514         28      applmgr      17794      1633.


Step 3.1 : Check and confirm SPID on Database Node

oravis$ ps-ef | grep 1633

oravis  1633     1  0 13:30:43 ?        0:03 oraclevis11i (LOCAL=NO)

Step 3.2 : Set OSPID (1633 in my case) for ORADEBUG

SQL> oradebug setospid 1633

Oracle pid: 68, Unix process pid: 1633, image: oraclevis11i@onlineappsdba


Step 3.3 : Enable trace for 10046 event with level 12

SQL> oradebug event 10046 trace name context forever, level 12

Step 3.4 : Locate Trace file as

SQL>oradebug tracefile_name

/oracle/apps/vis11idb/10.2.0/admin/oravis_abc/udump/oravis_ora_1633.trc

Wait for 15-20 minutes

Step 4 : Disable trace

SQL> oradebug event 10046 trace name context off

Step 5: Create tkprof file like

tkprof  ‘/oracle/ apps/ vis11idb/ 10.2.0/ admin/ oravis_abc/ udump/ oravis_ora_1633.trc’   ’/oracle/ apps/ vis11idb/ 10.2.0/ admin/oravis_abc/ udump/ tkprof_1633.txt’ explain=apps/[apps_passwd]  fchela …

Step 6 : Check TKPROF file to find root cause of slow concurrent requet

Tuesday, January 25, 2011

Cloning R12 adcfgclone.pl. Unable to locate all utilities with system path

$ perl adcfgclone.pl appsTier or dbTier

                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA

                        Oracle Applications Rapid Clone

                                 Version 12.0.0

                      adcfgclone Version 120.31.12010000.1

Enter the APPS password :

Checking for make...    found - /usr/bin/make
 Checking for ld...     found - /usr/bin/ld
 Checking for cc...     found - /usr/bin/cc
 Checking for aCC...
 Unable to locate 'aCC' utility in path
 Checking for ar...     found - /usr/bin/ar
 Unable to locate all utilities with system path.
 PATH = /u01/erpdev/apps/apps_st/comn/clone/bin/../jre/bin:/usr/bin:/usr/ccs/bin:/usr/contrib/bin:/usr/contrib/Q4/bin:/opt/perl/bin:/opt/gvsd/bin:/opt/ipf/bin:/opt/nettladm/bin:/opt/fcms/bin:/opt/wbem/bin:/opt/wbem/sbin:/opt/sas/bin:/opt/graphics/common/bin:/opt/atok/bin:/usr/bin/X11:/usr/contrib/bin/X11:/opt/sec_mgmt/bastille/bin:/opt/caliper/bin:/opt/drd/bin:/opt/dsau/bin:/opt/dsau/sbin:/opt/resmon/bin:/opt/firefox:/opt/gnome/bin:/opt/propplus/bin:/usr/contrib/kwdb/bin:/opt/perl_32/bin:/opt/perl_64/bin:/opt/prm/bin:/opt/sfm/bin:/opt/swm/bin:/opt/sec_mgmt/spc/bin:/opt/ssh/bin:/opt/swa/bin:/opt/hpsmh/bin:/opt/thunderbird:/opt/sentinel/bin:/opt/langtools/bin:/opt/gwlm/bin:.


Action :

check whereis acc_link installed.
# whereis aCC_link.
aCC_link: /usr/ccs/bin/aCC_link /usr/share/man/man1.Z/aCC_link.1

Create symbolic link

# ln -s /usr/ccs/bin/aCC_link /usr/bin/aCC

Sunday, January 23, 2011

Mounting and Unmouting the external Harddisk or Harddrive on HP-UX Server

login as root and run:

#ioscan -fnC disk To check for new hardware

o/p will be like this

Class     I  H/W Path     Driver S/W State   H/W Type     Description
disk      8  255/1/0.1.0  sdisk   CLAIMED     DEVICE       Seagate FreeAgent Go (External hardrive)
                                                 /dev/dsk/c1t1d0    /dev/rdsk/c1t1d0

/dev/dsk/c1t1d0 is called LUN Paths.

Make directory where you will mount external harddisk or harddrive

#cd /u01
#mkdir segate
#pwd
/u01/segate

Mounting the external Harddisk or Harddrive

#mount /dev/dsk/c1t1d0 /u01/segate

Unmounting the external Harddisk

#umount /u01/segate

Issues While Mounting the external Hard Disk.

While mounting if you get error

UX:vxfs mount: ERROR: V-3-21268 /dev/dsk/c1t1d0 is corrupted. needs checking

 #fsck -F vxfs /dev/dsk/c1t1d0

If this command gives error or after doing it you are still getting the same error, then try with full fsck. ie.

#fsck -F vxfs -o full,nolog /dev/dsk/c1t1d0

If this command does not solve your purpose then try the following one (performs all the log replys)

#fsck -F vxfs -o full -y /dev/dsk/c1t1d0

If you run ioscan -fnC disk and you are not getting LUN path for it.

#ioscan -m lun  (Display lun to lunpath mapping)

Issues while unmounting the external harddisk


# umount /mountpoint    e.g # umount /backup/segate
umount: cannot unmount /dev/disk/disk23 : Device busy
umount: return error 1.


First Run fuser then try umount
$ fuser -kuc /mountpoint e.g # fuser -kuc /backup/segate

#umount /backup/segate