Tuesday, November 26, 2013

Missing Pfile

Missing Pfile or Spfile:

step1:
vi initxxx.ora
Go to alert log location, from that
------------------------------------------------------
processes = 150
memory_target = 1584M
control_files = "/orau01/app/oracle/oradata/prod/control01.ctl"
control_files = "/orau01/app/oracle/flash_recovery_area/prod/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
db_recovery_file_dest = "/orau01/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 3882M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=prodXDB)"
audit_file_dest = "/orau01/app/oracle/admin/prod/adump"
audit_trail = "DB"
db_name = "prod"
open_cursors = 300
diagnostic_dest = "/orau01/app/oracle"
-----------------------------------------------------------
copy and paste...

step2:
connect sqlplus
startup
Error came :ORA-00845: MEMORY_TARGET not supported on this system

SQL> !df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
21G 17G 2.9G 86% /
tmpfs 1000M 420K 1000M 1% /dev/shm <<<<------------------ increase this value
/dev/sda1 485M 41M 419M 9% /boot

from root
[root@localhost ~]# mount -t tmpfs shmfs -o size=2048m /dev/shm

/etc/fstab

/dev/mapper/VolGroup-lv_root / ext4 defaults 1 1
UUID=9a9b0c22-cacf-491c-ad27-b636c69da3cf /boot ext4 defaults 1 2
/dev/mapper/VolGroup-lv_swap swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
shmfs /dev/shm tmpfs size=2048m 0 0 <<<<<<<<<---- add

following line

SQL> startup ;
ORACLE instance started.

Total System Global Area 1255473152 bytes
Fixed Size 1344652 bytes
Variable Size 754977652 bytes
Database Buffers 486539264 bytes
Redo Buffers 12611584 bytes
Database mounted.

go to listener. ora and edit like this

SALES =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ssgtrn.com)(PORT = 1521))
)
)
)

SID_LIST_SALES =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SALES)
(ORACLE_HOME = /orau01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = SALES)
)
)

go to tnsnames.ora and edit like this

SALES
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ssgtrn.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SALES
)
)

check the listener by lsnrctl status sales
lsnrctl stop sales
lsnrctl start sales

Hope it's useful for u...

Wednesday, November 13, 2013

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF'

Cause :-

I checked the following location( C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU) and found out that the file PRABHU01.dbf was not present.

Solution :-

There are two solutions to the above problem.
1. If data in this file is not that much important that remove this file information from database and open your database.

2. If data in this file is important and you have backup available for this file and if your database is in archivelog mode than you can recover the data in this file upto the last commit.But if database is in NOARCHIVELOG mode then restoration of data possible only upto the last backup.

Solution 1 :- Make the file Offline and then open your database after that drop that tablespace.

SQL> set lin 400
SQL> col name for a55
SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;

FILE# FILE_NAME TABLESPACE_NAME STATUS

---------- ----------------------------------------------------------------------------------- ----------------------------------------- ------------------

1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSTEM01.DBF SYSTEM SYSTEM

2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\UNDOTBS01.DBF UNDOTBS1 ONLINE

3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSAUX01.DBF SYSAUX ONLINE

4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\USERS01.DBF USERS ONLINE

5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF PRABHU ONLINE

SQL> drop tablespace PRABHU including contents and datafiles;

drop tablespace PRABHU including contents and datafiles

ERROR at line 1:

ORA-01109: database not open

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;

FILE# FILE_NAME TABLESPACE_NAME STATUS

---------- ----------------------------------------------------------------------------------- ----------------------------------------- ------------------

1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSTEM01.DBF SYSTEM SYSTEM

2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\UNDOTBS01.DBF UNDOTBS1 ONLINE

3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSAUX01.DBF SYSAUX ONLINE

4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\USERS01.DBF USERS ONLINE

5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF PRABHU OFFLINE

SQL> drop tablespace PRABHU including contents and datafiles;

Tablespace dropped.

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

Solution 2 :- Restore and recover the lost file through RMAN

C:\Documents and Settings\prabhu.subburaj>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 26 12:43:18 2013

connected to target database: PRABHU (DBID=2171747492, not open)

RMAN> list backup of datafile 5;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

19 Full 152.00K DISK 00:00:23 26-FEB-13

BP Key: 19 Status: AVAILABLE Compressed: YES Tag: TAG20130226T123606

Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRABHU\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP

List of Datafiles in backup set 19

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

5 Full 1028343 26-FEB-13 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF

RMAN> restore datafile 5 preview;

Starting restore at 26-FEB-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

19 Full 152.00K DISK 00:00:23 26-FEB-13

BP Key: 19 Status: AVAILABLE Compressed: YES Tag: TAG20130226T123606

Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRABHU\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP

List of Datafiles in backup set 19

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

5 Full 1028343 26-FEB-13 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF

using channel ORA_DISK_1

List of Backup Sets

===================

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

26 2.50K DISK 00:00:01 26-FEB-13

BP Key: 26 Status: AVAILABLE Compressed: YES Tag: TAG20130226T123711

Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRABHU\BACKUPSET\2013_02_26\O1_MF_ANNNN_TAG20130226T123711_8LRQS0JY_.BKP

List of Archived Logs in backup set 26

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 20 1028338 26-FEB-13 1028364 26-FEB-13

Media recovery start SCN is 1028343

Recovery must be done beyond SCN 1028343 to clear data files fuzziness

Finished restore at 26-FEB-13

RMAN> restore datafile 5;

Starting restore at 26-FEB-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00005 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF

channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRABHU\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP

channel ORA_DISK_1: restored backup piece 1

piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRABHU\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP tag=TAG20130226T123606

channel ORA_DISK_1: restore complete, elapsed time: 00:00:27

Finished restore at 26-FEB-13

RMAN> recover datafile 5 ;

Starting recover at 26-FEB-13

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:01:02

Finished recover at 26-FEB-13

RMAN> exit

Recovery Manager complete.

C:\Documents and Settings\prabhu.subburaj>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 26 12:46:12 2013

Enter user-name: /as sysdba

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select open_mode from v$database;

OPEN_MODE

----------

MOUNTED

SQL> alter database open;

Database altered.

SQL> set lin 400

SQL> col name for a55

SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;

FILE# FILE_NAME TABLESPACE_NAME STATUS

---------- ----------------------------------------------------------------------------------- ----------------------------------------- ------------------

1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSTEM01.DBF SYSTEM SYSTEM

2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\UNDOTBS01.DBF UNDOTBS1 ONLINE

3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\SYSAUX01.DBF SYSAUX ONLINE

4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\USERS01.DBF USERS ONLINE

5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PRABHU\PRABHU01.DBF PRABHU ONLINE

NOTE :- If database is in NOARCHIVELOG mode and you want to restore and recover your lost file, then after restoration of file from backup if recovery is possible through the redo logs currently available then you can open your database but if while in the process of recovery it asks for archive logs then you cannot open your database, you have to restore your full database from the backup to open your database. For Recovery upto last commit keep your database in ARCHIVELOG mode.

I hope this article helped you.

Tuesday, November 12, 2013

Start and stop Concurrent Manager in 11i Apps

 adcmctl.sh is one of the scripts in oracle applications 11i  for starting and stopping  Concurrent Manger service. It requires apps username and password to run this script. Using this predefined script we can start, stop, abort and check the status of Concurrent Manger Service.

Starting Service of CM:

Go To

cd $APPLCSF/scripts/<SID>_<localhost_name>

sh adcmctl.sh start apps/appspw

Sample Output:

mydb:applprod$sh adcmctl.sh start apps/apps

You are running adcmctl.sh version 115.31

Starting concurrent manager for PROD_806_BALANCE …
Starting PROD_0725@PROD_806_BALANCE Internal Concurrent Manager
Default printer is noprint

adcmctl.sh: exiting with status 0

mydb:

Stoping Service of CM:

Go To

cd $APPLCSF/scripts/<SID>_<localhost_name>

sh adcmctl.sh stop apps/appspw

Sample Output:

mydb:applprod$sh adcmctl.sh stop apps/apps

You are running adcmctl.sh version 115.31

Shutting down concurrent managers for PROD_806_BALANCE …
Submitted request 15895555 for CONCURRENT FND SHUTDOWN
adcmctl.sh: exiting with status 0

mydb:applprod$

Status of CM:

Go To

cd $APPLCSF/scripts/<SID>_<localhost_name>

sh adcmctl.sh status apps/appspw

Sample Output:

mydb:applprod$sh adcmctl.sh status apps/apps

You are running adcmctl.sh version 115.31

Internal Concurrent Manager is Active.

adcmctl.sh: exiting with status 0

mydb:applprod$

Please check is there any concurrent process is there before starting concurrent processing server. If the concurrent process still exists after shutdown kindly wait for some time for clearing the requests

The bellow command will tell you how many concurrent processes are running.Here applprod is the user for the application file system.

For finding FNDLIBR process:

ps -ef|grep applprod|grep FNDLIBR

For counting FNDLIBR process

ps -ef|grep applprod|grep FNDLIBR|wc –l

For killling FNDLIBR process

kill -9 <PID>

Cloning Steps

It explains cloning of Oracle Applications 11i and R12 from RAC to NON RAC (Single Node) Instance with 11g database. In the following note, I used the terms Target server is “UAT” which we are going to do clone of the server. And the source system is “PROD” which is using currently.

The cloning steps of RAC to Non RAC Instance in Oracle Applications 11i and R12 with 11g Database.

Source Server: PROD
Target Server: UAT

Step 1: Run the adpreclone.pl on Database Tier and Application Tier on Primary nodes.
perl adpreclone dbTier
perl adpreclone appsTier      

Step 2: Make a tar ORACLE_HOME location and copied to the Target server.

Step 3: Take a RMAN Full Database Backup and copied to the Target Server.

Step 4: Provide necessary permissions to the Target Server user for accessing directories.

Step 5: Set the Environment settings of as per the Target source
vi /home/oracle/uat.env

export ORACLE_HOME=/oraprod1/uat11g/

export ORACLE_SID=UAT #--(NEW SID )    

export PATH=$ORACLE_HOME/bin:$PATH  

export TNS_ADMIN=/oraprod1/UAT11g/network/admin/UAT_sun1 

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Step 6: Create an Oracle Password File for the Auxiliary Instance

orapwd file=orapw$ORACLE_SID entries=5 

password=welcome123 force=y

Step 7: Copy the initialization parameter file to the new name

cp initPROD1.ora initUAT.ora

Step 8: Open new initUAT.ora file and modify the required changes as per new current environment.

      vi initUAT.ora 

UAT.__db_cache_size=956301312

UAT.__java_pool_size=50331648

UAT.__large_pool_size=16777216

UAT.__pga_aggregate_target=1073741824

UAT.__sga_target=2147483648

UAT.__shared_io_pool_size=0

UAT.__shared_pool_size=1073741824

UAT.__streams_pool_size=16777216

*.db_name='UAT'

*.diagnostic_dest='/oradata1/UAT11g/data'

*.control_files='/oradata1/UAT11g/data/cntrl01.dbf',

'/oradata1/UAT11g/data/cntrl02.dbf',

'/oradata1/UAT11g/data/cntrl03.dbf'

*.DB_CREATE_FILE_DEST ='/oradata1/UAT11g/data/'

*.db_file_name_convert=('/oradata1/proddata/PROD/',

'/oradata1/UAT11g/data/','/oradata4/proddata/PROD/','/oradata1/UAT11g/data/')

*.log_file_name_convert=('/oradata1/proddata/PROD/','/oradata1/UAT11g/data/',

'/oradata4/proddata/PROD/','/oradata1/UAT11g/data/')

*.db_recovery_file_dest='/oradata2/UAT11g/UATarch'

*.db_recovery_file_dest_size=129496729600

*.utl_file_dir='/usr/tmp','/oradata1/UAT11g/utl'

*.compatible='11.2.0'

*.remote_login_passwordfile='EXCLUSIVE'

wq!

Step 9: Review required changes in oraInst.loc and modify it.

$ cat "oraInst.loc"

#Oracle Installer Location File Location

#Mon Sep 13 15:55:47 GMT+01:00 2010

inst_group=dba

inventory_loc=/oraprod1/UAT11g/oraInventory

Step 10: Start-up database no mount stage in the auxiliary instance on target node.In this case database will be start in  no mount stage.

sqlplus sys/welcome123 as sysdba

startup nomount pfile=/oraprod1/UAT11g/dbs/initUAT.ora

Step 11: Connect to RMAN as auxiliary database and run the following query from target node. This command will rebuild the database.

$rman auxiliary / 

RMAN>duplicate database to "UAT" backup location 
'/oradata2/UAT11g' nofilenamecheck;

Step 12: Check the status of the new target database after completing the rebuilding database.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

UAT             OPEN

SQL>

Step 13: Run the adcfgclone.pl dbTechStack on dbTier and respond prompts as per your environment.

UAT:oracle$perl adcfgclone.pl dbTechStack

Enter the APPS password [APPS]:

Step 14: Connect to sqlplus and run the following sqlplus script for building libraries:

$sqlplus "/ as sysdba" @adupdlib.sql [libext]

$sqlplus "/ as sysdba" @adupdlib.sql 'sl'               ##For exapmple -HP-UX

Where [libext] should be set to ‘sl’ for HP-UX, ‘so’ for any other UNIX platform, or ‘dll’ for Windows.

Step 15: Run the dbconfig on Database Tier and respond the prompts.

$ cd [RDBMS ORACLE_HOME]/appsutil/clone/bin

$ perl adcfgclone.pl dbconfig [Database target context file]

$UAT:oracle$perl adcfgclone.pl dbconfig /oraprod1/UAT11g/appsutil/UAT_sun1.xml

Enter the APPS password [APPS]:

Step 16: Clean the concurrent tables by using the following command after finished the cloning process.

SQL>EXEC FND_CONC_CLONE.SETUP_CLEAN;

SQL>COMMIT;

The database cloning process is completed on single node. Now we start the Application cloning process

Step 17: Copy all the Application file system from source to target as application user

Step 18: Provide required permissions to the apps user for accessing directories and files.

Step 19: Run adpreclone on AppsTier and resond the prompts. This process make new apps clone system.

Go to $COMMON_TOP/clone/bin

perl adcfgclone.pl appsTier

Step 20: Connect to Oracle sql plus and run it @afdcm037.sql as Apps User.This PL/SQL script to create libraries for FNDSM and FNDSVC and create Managers for Pre-existing Nodes. Future nodes will be handled by db trigger.

cd to $FND_TOP/patch/115/sql

SQL>@afdcm037.sql

Step 21: Your clone system is finished. Now you can check the application services and can connect to application URL.

Prabhu Subburaj Apps DBA

Tuesday, November 26, 2013

Missing Pfile

Wednesday, November 13, 2013

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

Tuesday, November 12, 2013

Start and stop Concurrent Manager in 11i Apps

Cloning Steps

About Me