Remote IT Support!! Please contact us

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

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