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.
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.
Granting select access to v$ Tables
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.
Example:
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.
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.
Example:
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.
Oracle Grid Home Clone Guide
Oracle GRID home cloning can be done using below steps
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 clone.pl -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 root.sh as a root or sudo
/u01/app/grid/product/11.2.0/root.sh
8.De-configure the current HA Service Settings as root by force as below from root or sudo
cd $GRID_HOME/crs/install
./roothas.pl -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/roothas.pl
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.
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 clone.pl -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 root.sh as a root or sudo
/u01/app/grid/product/11.2.0/root.sh
8.De-configure the current HA Service Settings as root by force as below from root or sudo
cd $GRID_HOME/crs/install
./roothas.pl -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/roothas.pl
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.
Finding session using UNDO tablespace
The below query can be used to find the session which is consuming more UNDO tablespace.
Query:
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;
Query:
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;
Configure Flashback Database and Create Restore Point
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;
exit
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;
exit
srvctl start database -d <database name>
Run crs_stat -t to confirm that the database is backup okay.
NOTES
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>;
exit
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;
exit
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;
exit
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'
ALTER DATABASE FLASHBACK OFF;
exit
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;
exit
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;
exit
srvctl start database -d <database name>
Run crs_stat -t to confirm that the database is backup okay.
NOTES
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>;
exit
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;
exit
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;
exit
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'
ALTER DATABASE FLASHBACK OFF;
exit
Difference between dbtechstack,dbtier and dbconfig
When running adcfgclone on database node we had three modes in which it can be executed.
perl adcfgclone.pl 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 adcfgclone.pl 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 adcfgclone.pl 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 adcfgclone.pl dbconfig target_context_file
Where Target Context File is:
$RDBMS_ORACLE_HOME/appsutil/target_context_file.xml
perl adcfgclone.pl 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 adcfgclone.pl 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 adcfgclone.pl 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 adcfgclone.pl dbconfig target_context_file
Where Target Context File is:
$RDBMS_ORACLE_HOME/appsutil/target_context_file.xml
Gather Schema Program not running for Custom Schema
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”)
Reason:
Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables
ie FND_ORACLE_USERID and FND_APPLICATIONS_TL
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.
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”)
Reason:
Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables
ie FND_ORACLE_USERID and FND_APPLICATIONS_TL
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.
Privileges issue while starting database with SRVCTL
Running srvctl command giving insufficient privileges issue.
./srvctl start database -d DEV
Solution:
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
$RDBMS_ORACLE_HOME/bin/osdbagrp
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.
./srvctl start database -d DEV
PRCR-1079 : Failed to start resource ora.dev.db
ORA-01031: insufficient privileges
CRS-5017: The resource action "ora.dev.db start"
encountered the following error:
ORA-01031: insufficient privileges
Solution:
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
$RDBMS_ORACLE_HOME/bin/osdbagrp
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.
Few Oracle Apps Patching Related Tables
Below are few tables that are might come handy and useful .These are used by ADPATCH utility which applying a application patch.
AD_APPL_TOPS
The table holds the various APPL-TOP’s in the Oracle Applications installation that have ever been patched.
AD_APPLIED_PATCHES
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
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
AD_PATCH_DRIVERS
This table holds information about the patch drivers that comprise a patch.
AD_FILE_VERSIONS
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
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”
AD_PATCH_DRIVER_LANGS
This table holds that language (or multiple languages). NLS patches (or more specifically, NLS patch drivers) pertain to a language or multiple languages.
AD_PATCH_DRIVER_MINIPKS
This table holds information about the various Mini Packs contained in a patch (driver)
AD_PATCH_RUN_BUG_ACTIONS
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.
AD_PATCH_RUN_BUGS
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
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
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.
AD_PATCH_COMMON_ACTIONS
This table holds distinct information about the various actions that are (often repeatedly) performed by Autopatch as part of applying patches.
AD_APPL_TOPS
The table holds the various APPL-TOP’s in the Oracle Applications installation that have ever been patched.
AD_APPLIED_PATCHES
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
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
AD_PATCH_DRIVERS
This table holds information about the patch drivers that comprise a patch.
AD_FILE_VERSIONS
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
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”
AD_PATCH_DRIVER_LANGS
This table holds that language (or multiple languages). NLS patches (or more specifically, NLS patch drivers) pertain to a language or multiple languages.
AD_PATCH_DRIVER_MINIPKS
This table holds information about the various Mini Packs contained in a patch (driver)
AD_PATCH_RUN_BUG_ACTIONS
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.
AD_PATCH_RUN_BUGS
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
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
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.
AD_PATCH_COMMON_ACTIONS
This table holds distinct information about the various actions that are (often repeatedly) performed by Autopatch as part of applying patches.
/tmp filesystem 100% fill but no files visible
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.
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.
ERROR: failed to establish dependency between database and diskgroup resource
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
Cause:
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.
Solution:
The resource details can be found by executing the crs_stat -t command.
Go to $ORACLE_HOME/bin
Execute
Once this is added Please re-run crs_stat -t from grid home and we can see a new resource added with name ora.dev.db
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.
ERROR: failed to establish dependency between database DEV and diskgroup resource ora.DATA.dg
Cause:
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.
Solution:
The resource details can be found by executing the crs_stat -t command.
Go to $ORACLE_HOME/bin
Execute
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 ora.dev.db
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.
Query to Check Workflow Mailer Status
Below query can be used to check the workflow mailer status.
Query:
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');
Query:
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');
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 odiparams.sh
3. Execute the command,It will ask for the password to be encoded.
./encode.sh
Enter password to encode:
4. Update the encoded password to odiparams.sh in the following location
ODI_SUPERVISOR=SUPERVISOR
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.
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 odiparams.sh
3. Execute the command,It will ask for the password to be encoded.
./encode.sh
Enter password to encode:
4. Update the encoded password to odiparams.sh in the following location
ODI_SUPERVISOR=SUPERVISOR
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.
Error : Function not available for this responsibility in R12
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.
Solution:
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:
APPL_TOP=/dev/applmgr/DEV/apps/apps_st/appl
CUSTOM_TOP=/dev/applmgr/DEV/apps/apps_st/custom
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.
Solution:
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:
APPL_TOP=/dev/applmgr/DEV/apps/apps_st/appl
CUSTOM_TOP=/dev/applmgr/DEV/apps/apps_st/custom
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.
Subscribe to:
Posts
(
Atom
)
3 comments :
Post a Comment