Remote IT Support!! Please contact us

For Any Remote Support,Implementation/Upgrade Projects,Queries,Collaborations please mail us at

Database opening with RESETLOGS

Whenever we perform incomplete recovery or recovery with a backup control file, you must reset the online logs when you open the database. The new version of the reset database is called a new incarnation.

Action performed while opening database base with RESETLOGS options

1.Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1. For example, if the current online redo logs are sequence 1000 and 1001 when you open RESETLOGS, then the database archives logs 1000 and 1001 and then resets the online logs to sequence 1 and 2.

2.Creates the online redo log files if they do not currently exist.

3.Reinitializes the control file metadata about online redo logs and redo threads.

4.Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

Because the database will not apply an archived log to a datafile unless the RESETLOGS SCN and time stamps match, the RESETLOGS prevents you from corrupting datafiles with archived logs that are not from direct parent incarnations of the current incarnation.


Post a Comment

Granting select access to v$ Tables

No comments
To provide grant select access on v$ tables (like v$session,V$ASM_DISK etc) error will encountered as these are synonyms.

The grant has to be provided on its views like (v_$session,V_$ASM_DISK etc).
After this grant the user can view details from these tables.


grant select on V_$ASM_DISK to hsingh;
grant select on V_$SESSION to hsingh;

connect hsingh/******
select * from v$session;

Now the details will be visible.

No comments :

Post a Comment

Oracle Grid Home Clone Guide

No comments
Oracle GRID home cloning can be done using  below steps


1.Copy source GRID Home to target server.
2.Make sure grid home user and group are properly setup.
3.Set proper .profile or .bash_profile to setup environment variable.
4.Make sure proper inventory location is pointed in /etc/oraInst.loc file.
5.As a grid osuser run below to detach the grid home from oracle inventory, ORACLE_HOME mentioned is the path of grid home itself.
ORACLE_HOME_NAME can be obtained from oracle inventory <$PATH>/oraInventory/ContentsXML/inventory.xml

cd $GRID_HOME/oui/bin or cd $ORACLE_HOME/oui/bin
./runInstaller -silent -detachHome ORACLE_HOME=/u01/app/grid/product/11.2.0/grid ORACLE_HOME_NAME="Ora11g_prod_grid_home"

6.As a grid osuser run below script

cd $GRID_HOME/clone/bin or cd $ORACLE_HOME/clone/bin
perl -silent ORACLE_BASE=/u01/app/grid ORACLE_HOME=/u01/app/grid/product/11.2.0/grid ORACLE_HOME_NAME="Ora11g_dev_grid_home" INVENTORY_LOCATION=/u01/app/oraInventory

7.Execute as a root or sudo


8.De-configure the current HA Service Settings as root by force as below from root or sudo

cd $GRID_HOME/crs/install
./ -deconfig -force

9.Execute below as root or sudo.The below command has to be executed in single line.

/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I /u01/app/grid/product/11.2.0/grid/perl/lib -I /u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/

10.Update the inventory, run it from grid. This will be required for nodes that are going to remain part of your cluster.In standalone system its not required.

./runInstaller -updateNodelist ORACLE_HOME=/u01/app/grid/product/11.2.0/grid defaultHomeName CLUSTER_NODES= CRS=TRUE

Value of defaultHomeName='Ora11g_dev_grid_home' in my case.

10.Modify host names in listener.ora of GRID & ORACLE_HOME and verify other details like Oracle home path in listener.ora,sqlnet.ora and tnsnames.ora.

11.Add asm,listener and database(optional else we can see a harmless error in Database alert log.)

srvctl add asm
srvctl add listener
srvctl add database -d DEV -o /u01/app/ora11g/product/11.2.0/dbhome_1

12.Start listener,asm services

srvctl start listener
srvctl start asm

13.Make sure ASMoracle cluster synchronization service domain are running

ps -ef| grep -i css

orgrid   17069     1  0 May04 ?        00:07:00 /u01/app/grid/product/11.2.0/grid/bin/cssdagent
orgrid   17086     1  0 May04 ?        00:01:30 /u01/app/grid/product/11.2.0/grid/bin/ocssd.bin

14. ASM instance would be started up without any disk groups mounted.

Status Check: Run below command
cd $GRID_HOME/bin
./crs_stat -t

Mount Diskgroup
Connect as sysasm from grid osuser
alter system set asm_diskstring='/dev/oracleasm';
alter diskgroup Data01 mount;
alter diskgroup Redo01 mount;
alter diskgroup Redo02 mount;
show parameter disk
select * from v$asm_disk;

15.Modify Oracle database pfile as per environment and correct path for control file, db name, dump locations utl_dir_loc etc...
Perform database recovery and & start DB.


No comments :

Post a Comment

Finding session using UNDO tablespace

1 comment
The below query can be used to find the session which is consuming more UNDO tablespace.


SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc;

1 comment :

Post a Comment

Configure Flashback Database and Create Restore Point

No comments
Flashback can be used to revert back database to previous point in time.
 How to Set-up Flashback Database

1. Ensure db_recovery_file_dest is set.

sqlplus '/ as sysdba'
alter system set db_recovery_file_dest='<FRA mount point>' SCOPE=spfile;

2. Ensure db_recovery_file_dest_size is set

alter system set db_recovery_file_dest_size=200G SCOPE=spfile;

3. Stop and start the database in mount stage

sqlplus '/ as sysdba'
shutdown immediate;
startup mount;

Turn flashback on using the following command if required to flashback to a any previous point in time.
alter database flashback on;
alter database open;
alter system set db_flashback_retention_target=2880;

The retention value is in mins

Verify Flashback Database is Already Enabled

1. Run the following commands to determing Flashback is turned on.

sqlplus '/ as sysdba'
select flashback_on from v$database;

Creating and Using Flashback Restore points

Creating a Restore point

Create a restore point whenever the database is at a state that it may needed to be flashed back to. Use the optional GUARANTEE FLASHBACK DATABASE clause to ensure that the restore point is not aged out of the flashback recovery area (FRA) as dictated by the db_flashback_retention_target parameter.

1. Put the database into mount mode now.

2. Create a restore point

sqlplus '/ as sysdba'
create restore point <restore point name> [GUARANTEE FLASHBACK DATABASE];

Rolling Back to a Restore Point

1. Identify the Restore point

sqlplus '/ as sysdba'
select name, time,guarantee_flashback_databse from v$restore_point;

2. For a non RAC environment use the following commands to flashback to a restore point.

sqlplus '/ as sysdba'
shutdown immediate;
startup mount;
flashback database to restore point <restore point name>;
alter database open resetlogs;

3. For RAC instances use the following commands.

One one of the nodes run, srvctl stop database -d <database name> -o immediate
sqlplus '/ as sysdba'
startup mount;
flashback database to restore point <restore point name>;
alter database open resetlogs;
shutdown immediate;
srvctl start database -d <database name>
Run crs_stat -t to confirm that the database is backup okay.

 Any tables created and updated without the LOGGING option will be suseptable to block curruption errors when the database is flashed back. These can be remedied by issuing the TRUNCATE TABLE command against the affected object(s).

Dropping a Restore Point

1. Restore points can be dropped with the database open using the following commands

sqlplus '/ as sysdba'
drop restore point <restore point name>;

Monitoring Flashback Logging

After enabling flashback logging, Oracle keeps track of the amount of logging generated. This can be queried from v$flashback_database_log, the estimate gets better with age. Note that this is the size of the flashback logs only and does not include space used by archive logs and RMAN backups.

sqlplus '/ as sysdba'
select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;

Finding the Earliest Flashback Point

Querying V$flashback_database_log will show you the earliest point you can flashback your database to based on the size of the FRA and the currently available flashback logs.

sqlplus '/ as sysdba'
select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

Disabling Flashback Database

Full any previous point in time flashback can be disabled with the database open.
Any unused Flashback logs will be automatically removed at this point and a message detailing the file deletion written to the alert log.

sqlplus '/ as sysdba'

No comments :

Post a Comment

Difference between dbtechstack,dbtier and dbconfig

When running adcfgclone on database node we had three modes in which it can be executed.
perl dbTier
 It will configure the ORACLE_HOME on the target database tier node and  recreate the controlfiles.
 This is specially used in case of standby database and/or hot backups. It will take care of all the steps. 

perl dbTechStack
It will configure the ORACLE_HOME on the target database tier node only. Relink the oracle home.
The below steps has to be performed manually
1. Create the Target Database control files.
2. Start the Target System Database in open mode
3. Run the library update script against the Database

cd $RDBMS_ORACLE_HOME/appsutil/install/[CONTEXT NAME]
sqlplus "/ as sysdba" @adupdlib.sql [libext]

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

perl dbconfig
It is used to configure the database with  context file.Database should be in open mode.
cd $RDBMS_ORACLE_HOME/appsutil/clone/bin
perl dbconfig target_context_file

Where Target Context File is:


Post a Comment

Gather Schema Program not running for Custom Schema

No comments
To gather schema statistics we have program in Oracle Apps (Gather Schema Statistics).
But when we select ALL as parmater to run gather schema , it will not consider the custom registered schema's and run gather stats for only default schema's.
 Concurrent –> Request –> Submit Request –> Gather Schema statistics (Paramerters “ALL”)
Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables

However , when Gather schema statistics is submitted it uses the below query to get schema information
select distinct upper(oracle_username) sname
from fnd_oracle_userid a,
fnd_product_installations b
where a.oracle_id = b.oracle_id
order by sname;

Note : When custom schemas are created the entry is not made in fnd_product_installations and hence it is not picked up in the above query.

Solution :

Make an entry in fnd_product_installations so that it is picked up by Gather Schema Stats. Follow below steps
Select Responsibility Alert Manager — > Systems –> Installations
Define custom application in this form , go the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.
Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schemas as well.

No comments :

Post a Comment

Privileges issue while starting database with SRVCTL

No comments
Running srvctl command giving insufficient privileges issue.
./srvctl start database -d DEV

PRCR-1079 : Failed to start resource
ORA-01031: insufficient privileges
CRS-5017: The resource action " start" encountered the following error:
ORA-01031: insufficient privileges 
There may be two issues due to which the database would not get started with srvctl
1. Grid Infrastructure owner is not part of OSDBA group for the database thats being start up.
The owner of the GRID_HOME should have osdba group as its secondary group.
To find the OSDBA group execute below command

To find groups associated with grid home user exeute
groups orgrid

 2. Grid Infrastructure owner doesn't have write permission to database dbs ($ORACLE_HOME/dbs) directory.

Make sure that grid home owner is having permission to write on $RDBMS_ORACLE_HOME/dbs location.


No comments :

Post a Comment

Few Oracle Apps Patching Related Tables

No comments
Below are few tables that are might come handy and useful .These are used by ADPATCH utility which applying a application patch.


The table holds the various APPL-TOP’s in the Oracle Applications installation that have ever been patched.


AD_APPLIED_PATCHES holds information about the “distinct” Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. “merged” patches), then they are considered distinct and this table will therefore hold 2 records.It will contain only main patch not the bugs fixed by patch.


AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.It will contains all the bugs that will be fixed by one patch


This table holds information about the patch drivers that comprise a patch.


This table holds the various versions of Oracle Applications files (real files, not “pseudo-files”), that have ever been patched or executed in the Oracle Applications installation.It will contain the latest file versions after patch application.


AD_FILES is the “files repository”. It contains information about the various files that have been patched in the Oracle Applications installation.
 Some entries are “pseudo-files” and not real files, (eg. directories) in which case some of the columns are not applicable and would then hold the value “DUMMY”


This table holds that language (or multiple languages). NLS patches (or more specifically, NLS patch drivers) pertain to a language or multiple languages.


This table holds information about the various Mini Packs contained in a patch (driver)


AD_PATCH_RUN_BUG_ACTIONS holds the various actions present in “applied” bug (fix). If Autopatch determined not to apply a bug (fix), then this table will not hold any records for that “unapplied” bug fix.
Even though a patch may have been applied on an Oracle Applications installation, some actions in some of its included bugs (fixes) may not have got executed if the “Autopatch” utility determined that it was not necessary to execute those actions. In such cases, EXECUTED_FLAG is set to N.


This table holds information about the bugs fixed in a specific run of Autopatch.
 AD_PATCH_RUN_BUGS holds information about the various bugs fixed in a specific run of Autopatch.

Even though a patch may have been applied on an Oracle Applications installation, some bugs (fixes) contained in it may not get applied due to some reason. In such cases, the REASON_NOT_APPLIED column holds the reason.


AD_PATCH_RUNS holds information about the various invocations of Autopatch for applying Oracle Applications patches to a specific release of an Oracle Applications installation.

If multiple drivers are run in one invocation of Autopatch, they result in multiple records in this table. These multiple records will all have the same SESSION_ID (because they arose from one Autopatch invocation), but different TASK_NUMBER’s. The TASK_NUMBER’s in this case will be numbered sequentially as 1, 2, 3, etc.
 Note that when the database driver of a Maintenance Pack is applied, it bumps up the release version by creating a new record in AD_RELEASES, which is then pointed to by the UPDATED_TO_RELEASE_ID column of the old record.


AD_RELEASES holds the various Oracle Applications releases that an installation of Oracle Applications has gone through in its entire life cycle.

It should be noted that START_DATE_ACTIVE, END_DATE_ACTIVE and BASE_RELEASE_FLAG are loosely-maintained informational columns and are not accurately maintained, and therefore should not be relied upon heavily.


This table holds distinct information about the various actions that are (often repeatedly) performed by Autopatch as part of applying patches.

No comments :

Post a Comment

/tmp filesystem 100% fill but no files visible

No comments
Recently I observed that the /tmp on my Oracle Apps web tier (linux server) was 100% fill but when checking the /tmp location no files were visbible.

To find out the culprit execute

 lsof | grep /tmp |grep deleted

appsdev  23232  owner   50u   REG       1,22 1945679248         29 /tmp/filehdsj2.TMP (deleted)

The above file shows as deleted but still process is holding it. We need to identify the process and kill or restart the process\service.

ps -ef|grep  23232

Kill -9 23232

Once the process is free the /tmp space will be released.

No comments :

Post a Comment

ERROR: failed to establish dependency between database and diskgroup resource

1 comment
If we observe the below error in the database alert log file

ERROR: failed to establish dependency between database DEV and diskgroup resource ora.DATA.dg


This error will be occur when there is ASM configured with you database.
It is due to the resource missing in the cluster registry services (CRS).But the error is harmless and will have no impact on database.


The resource details can be found by executing the crs_stat -t command.

Go to $ORACLE_HOME/bin


srvctl add database -d DEV -o /u01/app/ora11g/product/11.2.0/dbhome_1

Once this is added Please re-run crs_stat -t from grid home and we can see a new resource added with name

Then we can observe that the error in the database alert log will not occur and it will state successful establishment of connection between database and diskgroups.


1 comment :

Post a Comment

Query to Check Workflow Mailer Status

No comments
Below query can be used to check the workflow mailer status.
SELECT component_status
  FROM apps.fnd_svc_components
 WHERE component_id =
          (SELECT component_id
             FROM apps.fnd_svc_components
            WHERE component_name = 'Workflow Notification Mailer');

No comments :

Post a Comment

SUPERVISOR user password change in Oracle Data Integrator

To change the supervisor password of ODI we need to follow below steps

First Method

1. Start the ODI client, and openODI Studio.

2. Connect to Repository with current supervisor password.

3.  Navigation to change password
View > ODI Security Navigator >  click on users > click on "Supervisor " > on right pane click on "change password"

Second Method

1. On ODI server go to  cd $ODI_HOME/oracledi/agent/bin

2. Take the backup of the file

3. Execute the command,It will ask for the password to be encoded.

Enter password to encode:

4. Update the encoded password to in the following location

  ODI_SUPERVISOR_ENCODED_PASS='New encoded password'

5. Restart services.

6. Try logging in as SUPERISOR / Newpassword

The encrypted password is also stored in the snp_user table for supervisor user.


Post a Comment

Error : Function not available for this responsibility in R12

No comments
If there is an error " Function not avaibale for this responsibility" then ther might be the issue with the default.env file in Oracle Apps R12.The custom top entry should be present in the file.


1. Login to middle tier with application os user.

2. Go to $INST_TOP/ora/10.1.2/forms/server  or $ORA_CONFIG_HOME/10.1.2/froms/server directory.

3. Please update  CUSTOM_TOP's details  in the default.env file.

 For Example:

Make sure to make this changes in all the middle tier in case of multi-node environment.

4. Bounce the middle tier services.

5. Retest the issue.

No comments :

Post a Comment