Saturday, December 28, 2013

FND_DCP.REQUEST_SESSION_LOCK error on starting Concurrent Managers

Following error reported in Concurrent Manager logfile on starting CM :

Starting ERP_1228@ERP Internal Concurrent Manager -- shell process ID 5996

          Reviver is ENABLED

Routine &ROUTINE has attempted to start the internal concurrent manager.  The ICM is already running.  Contact you system administrator for further assistance.afpdlrq received an unsuccessful result from PL/SQL procedure or function FND_DCP.Request_Session_Lock.
Routine FND_DCP.REQUEST_SESSION_LOCK received a result code of 1 from the call to DBMS_LOCK.Request.
Possible DBMS_LOCK.Request resultCall to establish_icm failed
The Internal Concurrent Manager has encountered an error.

This must be due to a blocking session in the database.

Either bounce the database and re-start CM or follow :

1. Run sql to check for blocking session :

SQL> SELECT v$access.sid, v$session.serial#
FROM v$session,v$access
WHERE v$access.sid = v$session.sid and v$access.object = 'FND_CP_FNDSM'
GROUP BY v$access.sid, v$session.serial#;  2    3    4

       SID    SERIAL#
---------- ----------
       523         17


2. Kill blocking session :

SQL> alter system kill session '523,17';

System altered.


Start CM.

Sunday, December 22, 2013

Resolving Gaps in Data Guard Apply Using Incremental RMAN Backup

On the standby get the current scn which will be used in your RMAN backup script as a starting point. 

SQL>select current_scn FROM V$DATABASE;


 On the primary run the RMAN script with the supplied current_scn number from the standby:

run {
allocate channel c1 type disk;
backup as compressed backupset skip readonly incremental from scn 11133157117269 database;

Also create a new standby controlfile:
RMAN> backup current controlfile for standby format '/backup/control.bkp';

On the standby server catalog backup pieces and restore controlfile: 

RMAN> catalog start with ' /backup/';
RMAN> restore standby controlfile from '/backup/control.bkp';

And the recover script:

RMAN> recover database;

But recover failed with following error:

RMAN> recover database;

Starting recover at 22-DEC-13
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/22/2013 09:27:53
RMAN-06094: datafile 63 must be restored


Go back to Primary:

RMAN> backup datafile 63;
On Standby:
RMAN> restore datafile 569;

Saturday, December 14, 2013

Renaming A Datafile In A Physical Standby Environment

Stop EBS application services

1. Set standby_file_management to manual on primary as well as both standby database.

On Primary
SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

SQL> alter system set standby_file_management=MANUAL scope=both;
SQL> show parameter standby_file_management

On standby
show parameter standby_file_management
alter system set standby_file_management=MANUAL scope=both;
show parameter standby_file_management

2. Take the tablespace offline on the primary database

alter tablespace USERS offline;

3. Copy files to /oradataB location

cp -i /oradataA/users01.dbf /oradataB/

4. Rename the datafile in the primary database

alter tablespace USERS rename datafile '/oradataA/users01.dbf' to '/oradataB/users01.dbf';

5. Bring the Tablespace back online in the primary database

alter tablespace USERS online;

6.  On standby
a) stop redo apply on standby database.

recover managed standby database cancel;

b) Modify db_file_name_convert and  log_file_name_convert parameter of Standby init file to include /oradataB mount point .

Modify Value:
alter system set db_file_name_convert='/oradataA','/oradataB' scope=spfile;
alter system set log_file_name_convert='/oradataA','/oradataB' scope=spfile;

c) Shutdown the standby database.

 shutdown immediate

d) Move the datafile to the new location at the first standby database.

cp -i /oradataA/users01.dbf /oradataB/

e) start and mount the first standby database.

startup mount

f) Rename the Datafile in the standby database.

alter database rename file '/oradataA/users01.dbf' to '/oradataB/users01.dbf';

g) Start redo apply standby database.

recover managed standby database disconnect from session;

7. Set standby_file_management as auto on both primary and both standby database.

alter system set standby_file_management=auto scope=both;

On Primary
show parameter standby_file_management
alter system set standby_file_management=AUTO scope=both;
show parameter standby_file_management

On Standby
show parameter standby_file_management
alter system set standby_file_management=AUTO scope=both;
show parameter standby_file_management

8. If everything working fine, remove the old datafile from Primary and Standby

Verify mount point and Start EBS application services

Friday, December 13, 2013

Enabling / Disabling Flashback Database in 11gR2 without recycling database

Flashback database offer a simple way for performing a point in time recovery. This feature was introduced in oracle 10g.
Let’s take a scenario, where we have generated huge amount of flashback logs. Now to reclaim the space, we can reduce the db_flashback_retention_target parameter to a very small value, which will make the logs obsolete after some time & will delete them in case of space pressure in FRA (Flash / Fast Recovery Area).
But if we want to reclaim the space immediately, we can trun off the flashback.
In 10g we’ll have to
shutdown immediate 
startup mount 
alter database flashback off; 
alter database open;
But with 11gR2, Oracle introduced a new feature. We can now turn flashback on / off, when database is OPEN
SQL> select * from v$version;
Oracle Database 11g Enterprise Edition 
Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production
NLSRTL Version - Production

SQL> select flashback_on, status from v$database, v$instance;
------------------ ------------

SQL> alter database flashback off;
Database altered.

SQL> select flashback_on, status from v$database, v$instance;
------------------ ------------

SQL> alter database flashback on;
Database altered.

SQL> select flashback_on, status from v$database, v$instance;
------------------ ------------

Wednesday, December 11, 2013

Manually creating a new database on 11gR2

These are notes about creating a database manually. The DBCA can also be used to do this much more easily.

Create the directories

First, create the directories you need for the datafiles. On a non production system, I like to keep all the files for a database under a single directory so it is easy to delete the database later, eg:
mkdir -p /mnt/raid/dborafiles/11gr2/datafiles
mkdir -p /mnt/raid/dborafiles/11gr2/redo
For a production setup, each of these areas is probably a separate mount point on different disks etc.

Create a minimal init.ora

This file should go into $ORACLE_HOME/dbs and be called initSID.ora:
control_files = (/mnt/raid/dborafiles/ora11gr2/datafiles/control01.ora,

undo_management = auto
db_name = ora11gr2
db_block_size = 8192

# 11G (oracle will create subdir diag and all the required subdirs)
# This is a non-default location for the diag files. Normally they are created
# under $ORACLE_BASE, but for non production setups I like to keep all the files
# for a database instance under a single folder.
diagnostic_dest      = /mnt/raid/dborafiles/ora11gr2

Set the SID for your session

export ORACLE_SID=ora11gr2

Connect to SQLPLUS

$ sqlplus /nolog
SQL11g> connect / as sysdba

Create the SPFILE

$ create SPFILE from PFILE='/dboracle/product/11.2.0/dbhome_1/dbs/init11gr2.ora'

Startup the instance

SQL11g> startup nomount

Create the database

create database ora11gr2
  logfile   group 1 ('/mnt/raid/dborafiles/ora11gr2/redo/redo1.log') size 10M,
            group 2 ('/mnt/raid/dborafiles/ora11gr2/redo/redo2.log') size 10M,
            group 3 ('/mnt/raid/dborafiles/ora11gr2/redo/redo3.log') size 10M
  character set          utf8
  national character set utf8
  datafile '/mnt/raid/dborafiles/ora11gr2/datafiles/system.dbf' 
            size 50M
            autoextend on 
            next 10M
            extent management local
  sysaux datafile '/mnt/raid/dborafiles/ora11gr2/datafiles/sysaux.dbf' 
            size 10M
            autoextend on 
            next 10M 
  undo tablespace undo
            datafile '/mnt/raid/dborafiles/ora11gr2/datafiles/undo.dbf'
            size 10M
            autoextend on
  default temporary tablespace temp
            tempfile '/mnt/raid/dborafiles/ora11gr2/datafiles/temp.dbf'
            size 10M
            autoextend on
( TODO - unsure about setting max files sizes on these files )

Create the catalogue etc:

SQL11G> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL11G> @$ORACLE_HOME/rdbms/admin/catproc.sql

As SYSTEM (not SYS) run the following:

SQL11G> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
(not doing this doesn't cause any harm, but a warning is displayed when logging into SQLPLUS if it is not run)
The database is now basically ready to use, but there no users and no users tablespace. Note it is also NOT in archive log mode, so is certainly not production ready, but may be good enough for a non-backed up dev instance.

Create the users tablespace, local, auto allocate

SQL>CREATE TABLESPACE users DATAFILE '/mnt/raid/dborafiles/ora11gr2/datafiles/users_01.dbf' 
    SIZE 50M
    autoextend on 
    maxsize 2048M

Create a user:

SQL11G> create user sodonnel 
        identified by sodonnel 
        default tablespace users 
        temporary tablespace temp;

SQL11G> alter user sodonnel quota unlimited on users;

SQL11G> grant connect, create procedure, create table, alter session to sodonnel;

Ensure the database comes up at startup time

Add a line to /etc/oratab to tell Oracle about the instance. This is used by the dbstart command, which will start all the database specified in this file:
To start all instances use dbstart and to stop use dbshut.
TODO - control script to autostart databases when the machine boots.

Setup the listener

At this point, only people on the local machine can connect to the database, so the last step is to setup the listener. All you need to do here is add a file called listener.ora in $ORACLE_HOME/network/admin, and have it contain something like the following:
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
Creating a tnsnames.ora file at this point would be a good idea too. It also goes into $ORACLE_HOME/network/admin:
ora11gr2 =
     (ADDRESS = (PROTOCOL = TCP)(Host = localhost)(Port = 1521))
   (SERVICE_NAME = ora11gr2)

Adding 16k Tablespaces in a Database with default 8k db block size

In a database with default db_block_size as 8k, while creating a tablespace with default block size 16k, I encountered the following error.

ORA-29339: tablespace block size 16384 does not match configured block sizes

This effort was to create tablespace with a different database block size

Database default block size is 8k

SQL> sho parameter db_16k
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
If you have enough memory you should not need to reduce anything else but in case you don't you will need to reduce your db_cache_size. Normally you don't.
To add 16k tablespace so we will need to adjust the db_16k_cache_size:
SQL>  alter system set db_16k_cache_size=32M;
System altered.

This basically allows us to allocate 16k buffers inside our sga. This way we can you non-standard blocksizes in the database.

Now it will allow us to create the tablespace with 16k block size.

Thursday, December 5, 2013

Intermittent Oracle Reports REP-0069: REP-57054

Error in concurrent request logfile :

Enter Password:
REP-0069: Internal error
REP-57054: In-process job terminated:Finished successfully but output is voided

As per Metalink Doc : Intermittent Oracle Reports REP-0069: REP-57054: Error (Doc ID 1237834.1)

In short, cacheSize other than zero in $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf is a workaround.
The workaround will cause the cache to grow and it will require manual clean up.

The permanent fix is the application of Patch 11669923.

1.  Download and review the readme before applying Patch 11669923 or more recent Patch 14374587.  Follow the patch readme for any prerequisite and/or post-installation steps.

Note 1: Patch 14374587 Readme states:
# This  fix is merged with previous MLR 11669923.
# Please ignore any conflict message for patch 11669923"

As per Doc ID 437878.1, patch 11669923 supersedes patch 8602992; therefore, any conflict with patch 8602992 can also be ignored.

2.  Ensure / set the cacheSize parameter to a value of "0" within the file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf as follows:

a. Create a custom directory under $FND_TOP/admin/template
mkdir $FND_TOP/admin/template/custom

b. Copy the file $FND_TOP/admin/template/rwbuilder_conf_1012.tmp to this custom directory.

in this file under the section


Note 2: The above two actions prevents the accumulation of new temporary files from being created & retained; however, any temp files created before the fix will need to be deleted manually.

d. Now run autoconfig so that $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf is generated with new content.

Otherwise, the temp file will grow within the directory $INST_TOP/logs/ora/10.1.2/reports/cache as documented in Note 859255.1 "Reports Cache Directory in Oracle E-Business R12 is Growing Rapidly"

3.  In order to prevent Autoconfig from making changes introduced by Patch 11669923, follow Note 1322704.1 "Patch 11669923 Post Patch Instructions Would Get Overridden By Autoconfig" or apply Techstack (TXK) one-off Patch 11776182 "Add New Property NOVOIDEDOUTPUTERROR In RWBuilder Template File".

adpatch options

You can execute adpatch by logging in as the applications OS user and sourcing the appropriate environment file.

# su - applmgr
$ . ./APPSORA.env
$ adpatch

By default adpatch does not take any clause, but there are some clauses that you could use with adpatch

Running a patch in test mode
You can use the apply clause with adpatch to specify weather to run the patch in TEST mode or not, when you run the patch in test mode it does not do any changes but runs generates a log fiule with all the actions it would have performed.

$ adpatch apply=n|y
The default is apply=y

Pre-install Mode
You can also run a patch in pre install mode, this would be done normally during an upgrade or consolidated update. When a patch is applied in a preinstall mode the all the AD utilities are updated before the upgrade or update.

$ adpatch preinstall=y
The default is preinstall=n

Other Options with adpatch
You can use the options clause to specify some of the other options available with adpatch.

You can use the options=noautoconfig top specify autopatch that you do not wish to run autoconfig as a part of the patch application. This can be useful when applying a large number of patches when they are not merged. By default autoconfig is run as a part of adpatch.

$ adpatch options=noautoconfig

The chekfile option of adpatch tells adpathc to check for already executed exec, SQL, and exectier commands.You can use options=nocheckfile skips this check, however this can cause performance overheds so should be used only when specified.

$ adpatch options=nocheckfile

Compile Database
By defaulty autopatch compiles the invalid objects after the patch application, in case you wish not to do so you can specify options=nocompiledb along with autopatch.

$ adpatch options=nocompiledb

Compile JSP
By defaulty autopatch compiles the java server pages (jsp) after the patch application, in case you wish not to do so you can specify options=nocompilejsp along with autopatch.

$ adpatch options=nocompilejsp

Copy Portion
If you wish adpatch not to execute the commands present in the copy driver portion of the patch you can use the options=nocopyportion.

$ adpatch options=nocopyportion

Database Portion
If you wish adpatch not to execute the commands present in the database driver portion of the patch you can use the options=nodatabaseportion.

$ adpatch options=nodatabaseportion

Generate Portion
If you wish adpatch not to execute the commands present in the generate driver portion of the patch you can use the options=nogenerateportion

$ adpatch options=nogenerateportion

Maintenance Mode
If you wish to apply a patch regardless of the system being in maintenance mode you can use options=hotpatch.

$adpatch options=hotpatch

Integrity Check
If you wish to check the integrity of the patch you can use the options=integrity. Since metalink patches are pre checked for their integrity it is generally not required to do an explicit check and the default value is nointegrity.

$ adpatch options=integrity

Maintain MRC
You can use the maintainmrc option to specify weather you wish adpatch to execute the Maintain MRC schema as a part of the patch application or not. By default maintain MRC is done for standard patches and is disbaled for tarnslation and documentation patches.

$ adpatch options=nomaintainmrc

Pre requisite Patch Check
If you wish adpatch not to check for pre requisite patches before application of the main patch you can use options=noprereq.By default pre requsite checking is enabled.

$ adpatch options=noprereq

Validate Schemas
If you wish adpatch to explicitly validate all the registed schems by making a connection you can use options=validate. By default this validation is not performed.

$ adpatch options=validate

Java Classes
If you wish adpatch not to copy new java classes from the patch you can use options=nojcopy.By default java classes are copied.

$ adpatch options=nojcopy

Force Copy
By default adpatch copies the files without check the version of the existing files already present on the system.If you do not wish the newer version of the file to be replaced by the older version contained in the patch use options=noforcecopy.

$ adpatch options=noforcecopy

If you wish adpatch not do perform relinking you can use options=nolink.

$adpatch options=nolink

Generate Forms
If you wish adpatch not to generate the forms files you can specify options=nogenform.

$ adpatch options=nogenform

Generate Reports
If you wish adpatch not to generate the report files you can specify options=nogenrep.

$ adpatch options=nogenrep

You could specify multiple options at the command line using the , delimiter.

$ adpatch options=hotpatch,nojcopy