DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, October 16, 2017

Missing command fuser


Issue:

opatchauto apply is failing with below errors:

    Prerequisite check “CheckSystemCommandAvailable” failed.
    The details are: Missing command :fuser

Solution:

Install below package as a root user:

    yum install psmisc

The package contains the following programs:

    fuser - identifies what processes are using files.
    killall - kills a process by its name, similar to a pkill found in some other Unices.
    pstree - Shows currently running processes in a tree format.
    peekfd - Peek at file descriptors of running processes.

High Level Steps to integrate Oracle EBS R12 with OAM for Single Sign-On


Follow below high-level steps to integrate Oracle E-Business Suite with Oracle Access Manager
1. Install Database for IAM (OID/OAM)
2. Install Oracle Internet Directory (OID)
3. Install Oracle Access Manager (OAM)
4. Integrate OAM with OID
5. Integrate EBS with OID
6. Install Oracle HTTP Server (OHS)
7. Install WebGate
8. Integrate EBS with OAM
9. Test OAM-EBS Integration

Lost SYSMAN password - 13c


If the current SYSMAN password is unknown, then do the following:

1.    Stop OMS instances:

    cd <OMS_HOME>/bin

    emctl stop oms

2.    Modify the SYSMAN password:

    cd <OMS_HOME>/bin

    emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd <sys user password> -new_pwd <new sysman password>

    The '-use_sys_pwd' is used to connect to the database as a SYS user and modify the SYSMAN password in the Repository database.

    The current SYSMAN password is not prompted for and only the new password needs to be entered. This will allow the reset of the old password to the new password entered.

    The password will be modified at the Repository Database and the monitoring credentials for the 'OMS and Repository' target.

    Along with the SYSMAN password, this command will modify the password for the EM users (SYSMAN_MDS, BIP, SYSMAN_OPSS, SYSMAN_APM, SYSMAN_RO) created in the Repository Database.
3.    Stop the Admin server and re-start all the OMS:

    cd <OMS_HOME>/bin

    emctl stop oms -all

    emctl start oms

Oracle Packages Used for Performance Tuning





Oracle Packages Used for Performance Tuning:
--------------------------------------------

DBMS_ADDM
This package provides procedures to manage Oracle Automatic Database Diagnostic Monitor
Procedures
The most relevant procedures are:
ANALYZE_DB: creates an ADDM task to analyze the database and execute it
ANALYZE_INST: creates an ADDM task for analyzing in instance analysis mode
and executes it
GET_REPORT: retrieves the default text report of an executed ADDM task

DBMS_ADVISOR
This package helps in managing the Advisors, a set of expert systems that identify and
help resolve performance problems related to various database server components.
Procedures
The most relevant procedures are:
SET_DEFAULT_TASK_PARAMETER: sets the default values for task parameters
QUICK_TUNE: performs an analysis on a single SQL statement
EXECUTE_TASK: executes the specified task

DBMS_JOB
Schedules and manages jobs in the database job queue.
Oracle recommends using the DBMS_SCHEDULER package.
Procedures
The most relevant procedures are:
SUBMIT: submits a new job to the job queue
RUN: forces a specified job to run
NEXT_DATE: alters the next execution time for a specified job
BROKEN: deletes a job execution
REMOVE: removes the specified job from the job queue


DBMS_LOB
This package provides procedures to work with BLOBs, CLOBs, NCLOBs, BFILEs, and
temporary LOBs.
Procedures
The most relevant procedures are:
GET_LENGTH: gets the length of the LOB value
FILEOPEN: opens a file
LOADFROMFILE: loads LOB data from a file
APPEND: appends the contents of a source LOB to a destination LOB
OPEN: opens an LOB
READ: reads data from the LOB starting at the specified offset
WRITE: writes data to the LOB from a specified offset
CLOSE: closes a previously opened LOB


DBMS_MVIEW
This package helps the management of Materialized Views, refreshes them and helps
understanding the capabilities for materialized views and potential materialized views.
Procedures
The most relevant procedures are:
EXPLAIN_MVIEW: explains what is possible with a materialized view or potential
materialized view
EXPLAIN_REWRITE: explains why a query failed to rewrite or why the optimizer
chose to rewrite a query with a particular materialized view(s)
REFRESH: refreshes one or more materialized views
REFRESH_ALL_MVIEWS: refreshes all the materialized views


DBMS_OUTLN
This package contains the functional interface to manage stored outlines.
To use this package the EXECUTE_CATALOG_ROLE role is needed. There is also a public
synonym OUTLN_PKG.

Procedures
The most relevant procedures are:
CLEAR_USED: clears the outline "used" flag
DROP_BY_CAT: drops outlines which belong to a specific category
UPDATE_BY_CAT: updates the category of outlines to a new category
DROP_UNUSED: drops outlines never applied in the compilation of a SQL statement

DBMS_OUTLN_EDIT
This package contains the functional interface to manage stored outlines.
The public role has execute privileges on DBMS_OUTLN_EDIT, which is defined with
invoker's rights.
Procedures
The most relevant procedures are:
CREATE_EDIT_TABLES: creates outline editing tables in calling a user's schema;
beginning from Oracle 10g, you will not need to use this procedure because the
outline editing tables are part—as temporary tables—of the SYSTEM schema
REFRESH_PRIVATE_OUTLINE: refreshes the in-memory copy of the outline,
synchronizing its data with the edits made to the outline hints
DROP_EDIT_TABLES: drops the outline editing tables from the calling user's schema

DBMS_SHARED_POOL
This package allows access to information about sizes of the objects stored in the shared
pool and marks them for keeping or not-keeping.
Procedures
The most relevant procedures are:
KEEP: keeps an object in the shared pool, so it isn't subject to aging
UNKEEP: unkeeps an object from the shared pool
PURGE: purges the object
SIZES: shows objects in the shared pool larger than the specified size


DBMS_SPACE
This package enables the analysis of segment growth and space requirements.
Procedures
The most relevant procedures are:
CREATE_TABLE_COST: determines the size of a table
CREATE_INDEX_COST: determines the size of an index
FREE_BLOCKS: returns information about free blocks in an object
SPACE_USAGE: returns information about free blocks in a segment
managed by automatic space management



DBMS_SPM
This package provides an interface to manipulate plan history and SQL plan baselines.
Procedures
The most relevant procedures are:
LOAD_PLANS_FROM_CURSOR_CACHE: loads one or more plans from
the cursor cache for a SQL statement
LOAD_PLANS_FROM_SQLSET: loads plans stored in a SQL tuning set into
SQL plan baselines
EVOLVE_SQL_PLAN_BASELINE: evolves SQL plan baselines associated with
one or more SQL statements, changing them to accepted if they are found to be
better than the SQL plan baseline performance and if the user asks such action
DROP_SQL_PLAN_BASELINE: drops a single plan or all the plans associated
with a SQL statement


DBMS_SQL
This package provides an interface to use dynamic SQL to parse both DML and DDL
statements using PL/SQL.

Procedures
The most relevant procedures are:
EXECUTE: executes a cursor
OPEN_CURSOR: returns the cursor ID number of the new cursor
PARSE: parses the given statement
BIND_VARIABLE: binds a given value to a given variable
CLOSE_CURSOR: closes a given cursor and frees associated memory


DBMS_SQLTUNE
This package provides an interface to tune SQL statements.
Procedures
The most relevant procedures related to the SQL tuning set are:
CREATE_SQLSET: creates a SQL tuning set object in the database
DROP_SQLSET: drops a SQL tuning set if not active
SELECT_SQLSET: collects SQL statements from an existing SQL tuning set
LOAD_SQLSET: populates the SQL tuning set with a set of selected SQL statements
SELECT_CURSOR_CACHE: collects SQL statements from the cursor cache
The most relevant procedures to manage SQL tuning tasks are:
CREATE_TUNING_TASK: creates a tuning of a single statement or tuning set
EXECUTE_TUNING_TASK: executes a previously created tuning task
REPORT_TUNING_TASK: displays the results of a tuning task
INTERRUPT_TUNING_TASK: interrupts the currently executing tuning task
RESUME_TUNING_TASK: resumes a previously interrupted tuning task



DBMS_STATS
This package allows you to view and modify optimizer statistics.

Procedures
The most relevant procedures are:
GATHER_SCHEMA_STATS: gathers optimizer statistics for a schema class
GATHER_DATABASE_STATS: gathers optimizer statistics for a database class
GATHER_TABLE_STATS: gathers table statistics
GATHER_INDEX_STATS: gathers index statistics
CREATE_STAT_TABLE: creates the user statistics table
DROP_STAT_TABLE: drops the user statistics table
EXPORT_SCHEMA_STATS: exports schema statistics to a user statistics table
IMPORT_SCHEMA_STATS: import schema statistics from a user statistics table


DBMS_UTILITY
This package provides various utility subprograms.
Procedures
The most relevant procedures are:
ANALYZE_SCHEMA: analyzes all the tables, indexes, and clusters in a schema
ANALYZE_DATABASE: analyzes all the tables, indexes, and clusters in a database
GET_TIME: returns the current time in hundredths of a second

DBMS_WORKLOAD_REPOSITORY
This package allows management of Workload Repository.
Procedures
The most relevant procedures are:
CREATE_SNAPSHOT: creates a manual snapshot
MODIFY_SNAPSHOT_SETTINGS: modifies the snapshot settings
CREATE_BASELINE: creates a single baseline







changing the redo transport user





changing the redo transport user:
----------------------------------

If we often need to change the SYS user's password in the primary database, it may be
troublesome to copy the password file to the standby site every time, especially when
there's more than one standby destination. In this case, the REDO_TRANSPORT_USER
parameter comes to our rescue. It's possible to change the default redo transport user from
SYS to another database user by setting this parameter.

Follow these steps to change the redo transport user in the Data Guard configuration:
1. Create a new database, which will be used for redo transport in the primary
database. Grant the SYSOPER privileges to this user and ensure that the standby
database has applied these changes:
SQL> CREATE USER DGUSER IDENTIFIED BY SOMEPASSWORD;
SQL> GRANT SYSOPER to DGUSER;

note:Don't forget that if the password expires periodically for this user,
this will pose a problem in Data Guard redo transport. So ensure
that the default profile does not include the PASSWORD_LIFE_
TIME and PASSWORD_GRACE_TIME settings. If it does, choose
another profile for this user.

2. Stop the redo transport from the primary database to the standby databases. We
can execute the DEFER command to defer the log destination with the ALTER
SYSTEM statement:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'DEFER';
3. Change the redo transport user by setting the REDO_TRANSPORT_ USER parameter
in the primary and standby databases:
SQL> ALTER SYSTEM SET REDO_TRANSPORT_USER = DGUSER;
4. Copy the primary database's password file to the standby site:
$ cd $ORACLE_HOME/dbs
$ scp orapwTURKEY standbyhost:/u01/app/oracle/product/11.2.0/
dbhome_1/dbs/orapwINDIAPS
5. Start redo transport from the primary database to the standby databases:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'ENABLE';
6. Check whether the redo transport service is running normally by switching redo logs
in the primary database:
SQL> ALTER SYSTEM SWITCH LOGFILE;
Check the standby database processes or the alert log file to see redo transport
service status:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY ;



Resolving UNNAMED datafile errors





Resolving UNNAMED datafile errors:
-----------------------------------


ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: ' /u01/app/oracle2/product/11.2.0/dbhome_1/dbs/
UNNAMED00010'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file


Now we'll see how to resolve an UNNAMED datafile issue in a Data Guard configuration:
1. Check for the datafile number that needs to be recovered from the standby
database:
SQL> SELECT * FROM V$RECOVER_FILE WHERE ERROR LIKE '%MISSING%';
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------- ---------- ----------
10 ONLINE ONLINE FILE MISSING 0
2. Identify datafile 10 in the primary database:
SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=10;
FILE# NAME
---------- -----------------------------------------------
536 /u01/app/oracle2/datafile/ORCL/users03.dbf

3. Identify the dummy filename created in the standby database:
SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=10;
FILE# NAME
---------- -------------------------------------------------------
536 /u01/app/oracle2/product/11.2.0/dbhome_1/dbs/
UNNAMED00010
4. If the reason for the creation of the UNNAMED file is disk capacity or a nonexistent
path, fix the issue by creating the datafile in its original place.
5. Set STANDBY_FILE_MANAGEMENT to MANUAL:
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
6. Create the datafile in its original place with the ALTER DATABASE CREATE
DATAFILE statement:
SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle2/
product/11.2.0/dbhome_1/dbs/UNNAMED00010' AS '/u01/app/oracle2/
datafile/ORCL/users03.dbf';
Database altered.
If OMF is being used, we won't be allowed to create the datafile with the preceding
statement. We'll come across the following error:
SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle2/
product/11.2.0/dbhome_1/dbs/UNNAMED00010' AS '/u01/app/oracle2/
datafile/ORCL/users03.dbf';
*
ERROR at line 1:
ORA-01276: Cannot add file
/u01/app/oracle2/datafile/ORCL/users03.dbf. File has an Oracle
Managed Files file name.
In order to avoid the error, run the following command:
SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle2/
product/11.2.0/dbhome_1/dbs/UNNAMED00010' AS NEW;
Database altered.


7. Set STANDBY_FILE_MANAGEMENT to AUTO and start Redo Apply:
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;
System altered.


SQL> SHOW PARAMETER STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
----------------------------------- ----------- ------------------
standby_file_management string AUTO
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT FROM SESSION;
Database altered.

8. Check the standby database's processes, or the alert log file, to monitor Redo Apply:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY;
                                                             Output Post Processing 

Opp issue:
After the restart of application or server OPP process actual is 0 and target is 3.

Cause: OPP process is still alive in the system.


Solution:
Click on the concurrent manager then go to the processes  button You will see the active processes.
Kill those process in the system corresponding to the node.

E.g If node 1:
ps -ef | grep process-id <23242>
Kill -9 23242



Then verify the internal concurrent manager  then you will see the OPP process will get start




Fixing NOLOGGING changes in the standby database with incremental database backups




Fixing NOLOGGING changes in the standby database with incremental database backups:
-----------------------------------------------------------------------------------

Determine the SCN that we'll use in the RMAN incremental database backup by
querying the minimum FIRST_NONLOGGED_SCN column of the V$DATAFILE view
in the standby database:
SQL> SELECT MIN(FIRST_NONLOGGED_SCN) FROM V$DATAFILE WHERE FIRST_
NONLOGGED_SCN>0;
MIN(FIRST_NONLOGGED_SCN)
------------------------
20606544
2. Stop Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. Now we'll take an incremental backup of the database using the FROM SCN
keyword. The SCN value will be the output of the execution of the query in the
first step. Connect to the primary database as the RMAN target and execute the
following RMAN BACKUP statement:
RMAN> BACKUP INCREMENTAL FROM SCN 20606344 DATABASE FORMAT '/data/
DB_Inc_%U' TAG 'FOR STANDBY';
4. Copy the backup files from the primary site to the standby site with FTP or SCP:
scp /data/DB_Inc_* standbyhost:/data/
5. Connect to the physical standby database as the RMAN target and catalog the
copied backup files to the control file with the RMAN CATALOG command:
RMAN> CATALOG START WITH '/data/DB_Inc_';
6. Recover the standby database by connecting it as the RMAN target. RMAN will use
the incremental backup automatically because those files were registered to the
control file previously:
RMAN> RECOVER DATABASE NOREDO;
7. Run the query in the first step again to ensure that there're no more datafiles with
NOLOGGING changes:
SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE
FIRST_NONLOGGED_SCN > 0;

8. Start Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT;

note:

If the state of a tablespace that includes the affected datafiles is READ
ONLY, those files will not be backed up with the RMAN BACKUP
command. We need to put these tablespaces in the read-write mode
before the backup operation. Change the state of a tablespace with the
following statements:
SQL> ALTER TABLESPACE <TABLESPACE_NAME> READ WRITE;
SQL> ALTER TABLESPACE <TABLESPACE_NAME> READ ONLY;

9. Put the primary database in the FORCE LOGGING mode:
SQL> ALTER DATABASE FORCE LOGGING;

Renew the standby control file



Renew the standby control file:
---------------------------------


This action shows how to renew the standby control file in a Data Guard environment
with OMF.
1. In the primary database, create a backup of the standby control file with the
following RMAN statements:
$rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 19
22:18:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All
rights reserved.
connected to target database: ORCL (DBID=1319333016)
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT 'standbyctl.
bkp';

You'll see that a file named standbycf.bkp is generated under the $ORACLE_
HOME/dbs directory. This file will be used to restore the standby control file in the
standby database.
2. Copy this backup file from the primary database to the standby site by using the scp
or ftp protocols:
scp $ORACLE_HOME/dbs/standbyctl.bkp standbyhost:/tmp/standbyctl.
bkp


3. Query the current online and standby logfile paths in the physical standby database:
SQL> SELECT * FROM V$LOGFILE WHERE TYPE = 'ONLINE';
GROUP# STATUS TYPE MEMBER IS_
------ ------ ------ ----------------------------------------- ---
3 ONLINE /u01/app/oracle2/datafile/ORCL/redo03.log NO
2 ONLINE /u01/app/oracle2/datafile/ORCL/redo02.log NO
1 ONLINE /u01/app/oracle2/datafile/ORCL/redo01.lo NO
SQL> SELECT * FROM V$LOGFILE WHERE TYPE = 'STANDBY';
GROUP# STATUS TYPE MEMBER IS_
------ ------- ---- ------------------------------------------ ---
4 STANDBY /u01/app/oracle2/.../o1_mf_4_85frxrh5_.log YES
5 STANDBY /u01/app/oracle2/.../o1_mf_5_85fry0fc_.log YES
6 STANDBY /u01/app/oracle2/.../o1_mf_6_85fry7tn_.log YES
7 STANDBY /u01/app/oracle2/.../o1_mf_7_85fryh0n_.log YES
4. Shut down the standby database and delete all the online and standby logfiles:
$ sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE
$ rm /u01/app/oracle2/datafile/ORCL/redo0*.log
$ rm /u01/app/oracle2/fra/INDIA_PS/onlinelog/o1_mf_*.log
Depending on whether you use the filesystem or the ASM to store the database
files, you must run the rm command on the shell or on asmcmd respectively.
5. Start up the physical standby database in the NOMOUNT mode:
$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT
6. On the standby server, connect to RMAN and restore the standby control file from
the backup file:
$rman target /
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/standbyctl.bkp';

7. Mount the standby database as follows:
RMAN> ALTER DATABASE MOUNT;
database mounted
released channel: ORA_DISK_1
8. If OMF is not being used, and the datafile paths and names are the same for both
the primary and standby databases, skip this step and continue with the next step.
At this stage, in an OMF-configured Data Guard environment, the physical standby
database is mounted, but the control file doesn't show the correct datafile names
because it still contains the primary database's datafile names. We need to change
the datafile names in the standby control file. Use the RMAN CATALOG and SWITCH
commands for this purpose:
RMAN> CATALOG START WITH '/oradata/datafile/';
For ASM, use the following commands:
RMAN> CATALOG START WITH '+DATA1/MUM/DATAFILE/';
RMAN> SWITCH DATABASE TO COPY;
9. If the flashback database is ON, turn it off and on again in the standby database:
SQL> ALTER DATABASE FLASHBACK OFF;
Database altered.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
10. If standby redo logs exist in the primary database, we only need to execute the clear
logfile statement in the standby database so that they will be created automatically
(the log_file_name_convert parameter must already be set properly):
SQL> SELECT GROUP# FROM V$STANDBY_LOG;
GROUP#
----------
4
5
6
7
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;
Database altered.
If standby redo logs don't exist in the primary database, the following query will not
return any rows. In this case, we need to create the standby redo logs manually:
SQL> SELECT GROUP# FROM V$STANDBY_LOG;
no row selected
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 50M;
Database altered.
11. Start a media-recovery process in the physical standby database. The online logfiles
will be cleared automatically.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT FROM SESSION;
Database altered.



                                          Query to check the block corruption 


Query
--------

This query is used to find the block corruption present in the database.


SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
     , greatest(e.block_id, c.block#) corr_start_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
       - greatest(e.block_id, c.block#) + 1 blocks_corrupted
     , null description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
     , header_block corr_start_block#
     , header_block corr_end_block#
     , 1 blocks_corrupted
     , 'Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
     , greatest(f.block_id, c.block#) corr_start_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
       - greatest(f.block_id, c.block#) + 1 blocks_corrupted
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;



                              Query to find the concurrent process are locking in rac.


This query is used for find out the concurrent process are locked while bouncing them in RAC environment , If you return any values logon to the respective nodes and kill them , using alter sytem kill session.


Query
---------

SELECT gv$access.sid, gv$session.serial#,gv$session.inst_id,gv$session.status,gv$session.process  FROM gv$session,gv$access WHERE gv$access.sid = gv$session.sid and gv$access.object = 'FND_CP_FNDSM' GROUP BY gv$access.sid, gv$session.serial#,gv$session.inst_id,gv$session.status,gv$session.process;
              Warning: Missing charsets in String to FontSet conversion Warning:




PROBLEM:

[oracle@rac1 Desktop]$ xclock
Warning: Missing charsets in String to FontSet conversion
Warning: Unable to load any usable fontset

Before i start installing 12c installation  , i was pre-checking the status at Unix (RHEL 6) level to make sure my installation go smooth and found "xclock" was not working and it says,
# xclock
-bash: xclock: command not found

I have installed it using $ yum install xorg-x11-apps.x86_64
After installation i was getting below error and i don't see any solution in any of the website.


SOLUTION:
After surfing multiple Unix e-books and found the solution:

Add LC_ALL=en_US; export LC_ALL=en_US in .bashrc or in .bash_profile
[oracle@rac1 ~]$ . .bashrc


Issue fixed.

STEPS TO RENAME THE UNKNOW FILE IN THE STANDBY DATABASE

ALERT LOG:-
File #45 added to control file as 'UNNAMED045' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log /oracle/Doyen/archive/10G_Doyen_660003944_1_257734.arc
MRP0: Background Media Recovery terminated with error 1274
Sat Sep 25 07:22:47 2010
Errors in file /oracle/Doyen/home/admin/Doyen/bdump/Doyen_mrp0_24491.trc:
ORA-01274: cannot add datafile '/oracle/Doyen/user02.dbf' - file could not be created
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Sat Sep 25 07:22:50 2010
Errors in file /oracle/Doyen/home/admin/Doyen/bdump/Doyen_mrp0_24491.trc:
ORA-01274: cannot add datafile '/oracle/Doyen/user02.dbf' - file could not be created
Sat Sep 25 07:22:50 2010
MRP0: Background Media Recovery process shutdown (Doyen)


----File #45 added to control file as 'UNNAMED045' because

select FNNAM,FNONM from x$kccfn where FNFNO=45;
/oracle/Doyen/home/product/10.2.0.4/dbs/UNNAMED045
/oracle/Doyen/user02.dbf


alter database create datafile '/oracle/Doyen/home/product/10.2.0.4/dbs/UNNAMED04554' as '/oracle/Doyen/user02.dbf';

alter database datafile 45 online;

if you are going make  standby_file_management as "AUTO in the init parameter. oracle will use the
db_file_name_convert option to put the files in the respective directory in the standby database.

Steps to re create Oracle Inventory

1)  No down time is required for recreating the global inventory (oraInventory)
2)  if you have corrupted oracle inventory or improper oracle inventory, you can rename the directory to avoid the confusion.

      mv oraInventory oraInventory_orig

Central Inventory
-----------------
Central Inventory contains the information relating to all Oracle products
Installed on a host. Central inventory (oraInventory) is an inventory that
lists ORACLE_HOMEs installed in the system using the inventory.xml file.
Each central inventory consists of a file called inventory.xml, which
contains the list of Oracle Homes installed.

Local Inventory
---------------
Oracle home inventory or local inventory is present inside each Oracle home.
It contains information relevant to the particular Oracle home only.
This inventory contains, among other things, a file called comps.xml,
which contains all the components  as well as patchsets or interim patches
installed in the ORACLE_HOME.

To determine where oraInventory is located

/var/opt/oracle/oraInst.loc or /etc/oraInst.loc depending upon the Platform.

Sample oraInst.loc file

/var/opt/oracle/oraInst.loc
inst_group=dba
inventory_loc=/apps/oracle/product/oraInventory

ORACLE_HOME/oraInst.loc
inst_group=dba
inventory_loc=/apps/oracle/product/oraInventory




To find the ORACLE_HOME & ORACLE_HOME_NAME

if you have oracle old inventory, then you can view from
/apps/oracle/product/oraInventory/ContentsXML/inventory.xml
you can see ORACLE_HOME & ORACLE_HOME_NAME

SAMPLE OUTPUT

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 2009 Oracle Corporation. All rights Reserved -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>10.2.0.5.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="orahome_102" LOC="/apps/oracle/product/10.2.0.2" TYPE="O" IDX="1"/>
<HOME NAME="agent10g" LOC="/apps/oracle/product/agent10g" TYPE="O" IDX="2"/>
</HOME_LIST>
</INVENTORY>


Go to Oracle Universal installer location for creating Oracle Inventory
$ORACLE_HOME/oui/bin

if you have more than one Oracle product, you have to update the inventory for all the oracle home
Ex: Oracle DB home(different versions includes)
    Oracle Agent

Note: when you are running for different homes, you have run the oui in there respective home only
   
Ex: output for ORACLE_HOME(run it in $ORACLE_HOME/oui/bin)

SAMPLE OUTPUT

$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/apps/oracle/product/10.2.0.2" ORACLE_HOME_NAME="orahome_102"
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

>>> Ignoring required pre-requisite failures. Continuing...

The inventory pointer is located at /var/opt/oracle/oraInst.loc
The inventory is located at /apps/oracle/product/oraInventory
'AttachHome' was successful.

Ex: output for AGENT_HOME(run it in $AGENT_HOME/oui/bin)




SAMPLE OUTPUT

$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/apps/oracle/product/agent10g" ORACLE_HOME_NAME="agent10g"
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

>>> Ignoring required pre-requisite failures. Continuing...

The inventory pointer is located at /var/opt/oracle/oraInst.loc
The inventory is located at /apps/oracle/product/oraInventory
'AttachHome' was successful.



We can apply the patch by specify oracle inventory location by the below option.

-- Apply the Patch with the address of the Oracle Inventory

$ opatch apply -invPtrLoc /app/oracle/oraInst.loc

$ opatch lsinventory -invPtrLoc /app/oracle/oraInst.loc

Step to recover OCR & Voting Disk When It got corrupted

When we have lost or having a corruption issue on the OCR & VOTING Disk, we have follow the below procedure to bring it back.

When using an ASM disk group for CRS there are typically 3 different types of files located in the disk group that potentially need to be restored/recreated for function of the cluster.
Oracle Cluster Registry file (OCR)
Voting files
 Shared SPFILE for the ASM instances
In this scenario, we are trying to restore the corrupted OCR Disk & Voting Disk from the backup.


Step #1 Stop cluster on each node(Root user).

# crsctl stop crs -f

Step #2 we are starting the cluster in the excusive mode(Root user)

As root start GI in exclusive mode on one node only:
In 11201 RAC, we have to use below option to start the cluster in the exclusive mode.
# crsctl start crs -excl

In 11202 RAC, we have to use below option to start the cluster in the exclusive mode.
# crsctl start crs -excl -nocrs

Note: A new option '-nocrs' has been introduced with  11.2.0.2, which prevents the start of the ora.crsd resource. It is vital that this option is specified; otherwise the failure to start the ora.crsd resource will tear down ora.cluster_interconnect.haip, which in turn will cause ASM to crash.


If you don’t have the OCR DISK GROUP, then create it else move to restoring OCR DISK


Step #3 OCR RESTORE

To Know the OCR Location on the cluster environment
$ cat /etc/oracle/ocr.loc  -- In Linux

To Check whether ocrcheck is corrupted or not

# ocrcheck

Check whether ocrcheck is able to complete it successfully

OCR CHECK Ex
# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       4404
         Available space (kbytes) :     257716
         ID                       : 1306201859
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
         Cluster registry integrity check succeeded

         Logical corruption check succeeded
     

Note: 1) Check whether cluster registry integrity check is successful.
          2) When you run as oracle user, logical corruption check will be bypassed. You can see this line end of the “ocrcheck” output.

Recreate the Voting file (root user)
The Voting file needs to be initialized in the CRS disk group
# crsctl replace votedisk +OCR_DISK
Note: 1) Above command will Re-create/move your voting disk in the specified ASM Disk Group, if you query the voting disk it will display your voting disk location in the DISK Group which has been specified above.
2)  Voting File is that it is no longer supported to take a manual backup of it with dd.  Instead, the Voting File gets backed up automatically into the OCR.

Query Voting Disk location

# $GRID_HOME/bin/crsctl query css votedisk

Note: You cannot create more than 1 voting disk in the same or on another/different Disk group disk when using External Redundancy in 11.2. The rules are as follows:
External = 1 voting disk
Normal= 3 voting disk
High= 5 voting disk

Step #5 Stop & start the cluster

Shutdown CRS è CRS is running in exclusive mode, it needs to be shutdown (Root User).

# crsctl stop crs -f

Start CRS è Start the CRS in one node, if everything is ok then start the CRS in other nodes (root user).

# crsctl start crs

CRS Status è Once it is start, you can check the status of the CRS(Root / Oracle user)

# crsctl stat res –t –init      à if you are checking for one node
# crsctl check cluster –all  à if you are checking for entire cluster.

RETRIEVING DROPPED TABLE IN ORACLE USING RMAN AND FLASHBACK TECHNOLOGY



Step 1: Check whether DB has recyclebin on or off

SYS@xxxx> sho parameter recyclebin;

NAME TYPEVALUE
------------------------------------ ----------- ------------------------------
recyclebin   string  ON


SELECT * FROM RECYCLEBIN;

ALTER SESSION SET recyclebin = OFF; 

ALTER SYSTEM SET recyclebin = OFF;

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON;



Step 2:  drop table oracle;

Table dropped.

SQL> select original_name from dba_recyclebin; or show recyclebin;

ORIGINAL_NAME 
-------------------------------- 
oracle 

SQL> flashback table oracle to before drop;

Flashback complete.

SQL> select * from oracle;

ID 
---------- 

6

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

Recover the tables PMP and DEPTER using the following clauses in the RECOVER command: DATAPUMP DESTINATION, DUMP FILE, REMAP TABLE, and NOTABLEIMPORT.
The following RECOVER command recovers the PMP and DEPTER tables.( Here i am using SYSDATE 1 day before ) You can also use Until Sequence )

RECOVER TABLE SCOTT.PMP, SCOTT.DEPTER
UNTIL TIME 'SYSDATE-1'
AUXILIARY DESTINATION '/tmp/oracle/recover'
DATAPUMP DESTINATION '/tmp/recover/dumpfiles'
DUMP FILE 'pmp_depter_exp_dump.dat'
NOTABLEIMPORT;




Steps to Flashback to Particular Restore Point

set pagesize 1000

col name format a70

col scn format 9999999999999999999

col time format a50

select SCN,NAME,TIME,STORAGE_SIZE from v$restore_point;

shutdown immediate

startup mount

exit



flashback the database to before deployment

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

rman target /



flashback database to restore point xxxxxxxxxxx;

 ( if you want to execute from sqlplus remove quotes and if you want to execute from rman you need quotes)

alter database open resetlogs;



exit



drop all restore points including the one which used as restore point

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

dba



set pagesize 1000

select 'drop restore point '||NAME||';' from v$restore_point;

Wednesday, October 11, 2017

Steps for Database Failover

Steps for Database Failover




Check the archive log sync gap in DR if we found gap we need to sync. Check the gap by using bellow query .


1. select name,open_mode from v$database;

2. select name,open_mode,database_role from v$database;

3.SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE#"Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#




4. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


5. select name,open_mode,database_role from v$database;

6. ALTER DATABASE ACTIVATE STANDBY DATABASE;

7. select name,open_mode,database_role from v$database;


8. alter database open;
9. select name,open_mode,database_role from v$database;

10.Shut immediate;

11. Startup;



 

Creating the Autoconfig Script in DBTier

Steps:
      1. Creating the Context file
      2. Creating the autoconfig script folder in DBTier
      3.Executing the autoconfig file.


1) Creating the Context file:

[oracle@myebs bin]$ perl adbldxml.pl appsuser=apps appspasswd=apps jtop=/u01/mydb/db/tech_st/11.1.0/appsutil_bkp/jre

Starting context file generation for db tier..
Using JVM from /u01/mydb/db/tech_st/11.1.0/appsutil_bkp/jre/bin/java to execute java programs..

The log file for this adbldxml session is located at:
/u01/mydb/db/tech_st/11.1.0/appsutil/log/adbldxml_10110135.log

Enter the value for Display Variable:

Invalid input, reenter the value for Display Variable: 2

The context file has been created at:
/u01/mydb/db/tech_st/11.1.0/appsutil/PROD_myebs.xml
[oracle@myebs bin]$ cd /u01/mydb/db/tech_st/11.1.0/appsutil/
[oracle@myebs appsutil]$ ls
bin  html  java  log  media  perl  PROD_myebs.xml  sql  template
[oracle@myebs appsutil]$ cd bin/

2) Creating the Autoconfig Script folder in DBTier:

[oracle@myebs bin]$ sh adconfig.sh contextfile=/u01/mydb/db/tech_st/11.1.0/appsutil/PROD_myebs.xml appspass=apps

The log file for this session is located at: /u01/mydb/db/tech_st/11.1.0/appsutil/log/PROD_myebs/10110138/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
        Using ORACLE_HOME location : /u01/mydb/db/tech_st/11.1.0
        Classpath                   : :/u01/mydb/db/tech_st/11.1.0/jdbc/lib/ojdbc6.jar:/u01/mydb/db/tech_st/11.1.0/appsutil/java/xmlparserv2.jar:/u01/mydb/db/tech_st/11.1.0/appsutil/java:/u01/mydb/db/tech_st/11.1.0/jlib/netcfg.jar:/u01/mydb/db/tech_st/11.1.0/jlib/ldapjclnt11.jar

        Using Context file          : /u01/mydb/db/tech_st/11.1.0/appsutil/PROD_myebs.xml

Context Value Management will now update the Context file

        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db111
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.

[oracle@myebs PROD_myebs]$ pwd
/u01/mydb/db/tech_st/11.1.0/appsutil/scripts/PROD_myebs
[oracle@myebs PROD_myebs]$ ls -ltr
total 60
-rw------- 1 oracle oinstall  1303 Oct 11 01:40 adstrtdb.sql
-rw------- 1 oracle oinstall   805 Oct 11 01:40 adstopdb.sql
-rwx------ 1 oracle oinstall  7384 Oct 11 01:40 adexecsql.pl
-rwx------ 1 oracle oinstall  5274 Oct 11 01:40 adchknls.pl
-rwx------ 1 oracle oinstall 15247 Oct 11 01:40 adpreclone.pl
-rwx------ 1 oracle oinstall  3450 Oct 11 01:40 adlsnodes.sh
-rwx------ 1 oracle oinstall  6749 Oct 11 01:40 addlnctl.sh
-rwx------ 1 oracle oinstall  2760 Oct 11 01:40 addbctl.sh
-rwx------ 1 oracle oinstall  1518 Oct 11 01:40 adautocfg.sh
[oracle@myebs PROD_myebs]$

3) Executing the Auto Config:

[oracle@myebs PROD_myebs]$ sh adautocfg.sh
Enter the APPS user password:
The log file for this session is located at: /u01/mydb/db/tech_st/11.1.0/appsutil/log/PROD_myebs/10110140/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
        Using ORACLE_HOME location : /u01/mydb/db/tech_st/11.1.0
        Classpath                   : :/u01/mydb/db/tech_st/11.1.0/jdbc/lib/ojdbc6.jar:/u01/mydb/db/tech_st/11.1.0/appsutil/java/xmlparserv2.jar:/u01/mydb/db/tech_st/11.1.0/appsutil/java:/u01/mydb/db/tech_st/11.1.0/jlib/netcfg.jar:/u01/mydb/db/tech_st/11.1.0/jlib/ldapjclnt11.jar

        Using Context file          : /u01/mydb/db/tech_st/11.1.0/appsutil/PROD_myebs.xml

Context Value Management will now update the Context file

        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db111
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.

[oracle@myebs PROD_myebs]$

Tuesday, October 10, 2017

ORA-20100: Error: FND_FILE failure. Unable to create file in the directory




Problem details:
The Concurrent program is getting completed successfully, able to view output & logfile. But in the log file, it shows below error.
Oracle error 20100: java.sql.SQLException: ORA-20100: Error: FND_FILE failure. Unable to create file, o0165247.tmp in the directory, /testebs1/applcsf/tmp.
You will find more information in the request log.
ORA-06512: at "APPS.FND_FILE", line 417
ORA-06512: at "APPS.FND_FILE", line 526
ORA-06512: at "APPS.FND_CONCURRENT", line 1335
ORA-06512: at line 1
has been detected in FND_CONCURRENT.SET_INTERIM_STATUS.Successfully resubmitted concurrent program FNDOAMCOL with request ID 8907654 to start at 25-JAN-2017 22:38:56 (ROUTINE=AFPSRS)

Resolution:
Step 1: Stop the application.
Step 2: Manually delete the files under $APPLCSF/$APPLLOG
Step 3: Restart the application and retest the issue.


adformsctl.sh: exiting with status 150



If adoacorectl.sh,adformsctl.sh,adoafmctl.sh not coming up after start the application services
adoacorectl.sh: exiting with status 150
adformsctl.sh: exiting with status 150
adoafmctl.sh: exiting with status 150

Check the log file for these three processes:
$LOG_HOME/appl/admin/log/adoacorectl.txt
$LOG_HOME/appl/admin/log/adoafmctl.txt 
$LOG_HOME/appl/admin/log/adoaformsctl.txt 
If you see error like below

"opmn id=app01.rajeev01.com:6200
    no processes or applications matched this request

adoafmctl.sh: exiting with status 150"



Stop all three services. 
Clean the directory, under  persistence from below directory and restart all three services
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence
$ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence
$ORA_CONFIG_HOME/10.1.3/j2ee/forms/persistence

Autoadjustments Errors with Frm-40735, ORA-04062: signature of package.


Autoadjustments fails with


FRM-40735: PRE-COMMIT trigger raised unhandled exception ORA-04062.
ORA-04062: signature of package "APPS.FND_REQUEST" has been changed

CAUSE

Library files need to be recompiled

The form ARXSUBAA was compiled successfully, however it is the library that is calling fnd_request(ARXSUBAA.pld)


SOLUTION

First, compile the library which is calling fnd_request - ARXSUBAA.pld
Then Compile the form - ARXSUBAA.fmb

Rerun the autoadjustments.

Change oc4j administrator password

Step to change oc4j admin password manually:
---------------------------------------------------------

Step 1:
--------
Stop OC4J and the Application Server Control.

Enter the following command in the Oracle home of the application server instance:

(UNIX) ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=OC4J
(Windows) ORACLE_HOME\opmn\bin\opmnctl stopproc ias-component=OC4J

Step 2:
---------

Locate and open the following file in a text editor:

(UNIX)ORACLE_HOME/j2ee/home/config/system-jazn-data.xml
(Windows)ORACLE_HOME\j2ee\home\config\system-jazn-data.xml

Step 3:
---------
Locate the line that defines the credentials property for the oc4jadmin user.

The following example shows the section of system-jazn-data.xml with the encrypted credentials entry in boldface type:



    jazn.com
   
     .
     .
     .
     
          oc4jadmin
          OC4J Administrator
          OC4J Administrator
            {903}4L50lHJWIFGwLgHXTub7eYK9e0AnWLUH
     
Step 4:
---------

Replace the existing encrypted password with the new password.

Be sure to prefix the password with an exclamation point (!). For example:

!mynewpassword123

The password for the oc4jadmin user should conform to following guidelines:

Must contain at least five characters, but not more than 30 characters.

Must begin with an alphabetic character. It cannot begin with a number, the underscore (_), the dollar sign ($), or the number sign (#).

At least one of the characters must be a number.

Can contain only the following characters; numbers, letters, and the following special characters: US dollar sign ($), number sign (#), or underscore (_).

Cannot contain any Oracle reserved words, such as VARCHAR.

Step 5:
---------
Delete cached password data by deleting the contents of the following directory:

(UNIX)
ORACLE_HOME/j2ee/oc4jinstance /persistence/ascontrol/ascontrol/securestore/
(Windows)
ORACLE_HOME\j2ee\oc4jinstance\persistence\ascontrol\ascontrol\securestore/

Start OC4J and the Application Server Control.

Step 6:
----------
After the restart, the Application Server Control will use your new Administrator (oc4jadmin) password, which will be stored in encrypted format within the system-jazn-data.xml file.

Unable to open the log file and output file in oracle apps 11i

Error:
-------

11i error- An error occurred while attempting to receive the output arguments of the remote procedure. Function ncrorou returned error code 3010. The Applications File Server proces


Solution
-----------

Step 1:

Check for the space in mount point

Step 2:

  Clear unnecessary files from the mount point

Step 3:

    Clear the space in tmp directory

Reproduce the issue

11i reports going in to error

11i instance concurrent requests going in to error due to oracle tool kit error. 


Error
-------




Solution
----------

1.Set the display properly.
2.Login as the oracle user and set the env and type vncserver

                  During the R12.2 cloning process adcfgclone fails with error 

  Error:

                      Below Oracle Homes are already registered in the global inventory:
                      /u01/oracle/R122/fs1/FMW_Home/oracle_common
                      /u01/oracle/R122/fs1/FMW_Home/webtier
                      /u01/oracle/R122/fs1/FMW_Home/Oracle_EBS-app1


 Cause:

                  The above Oracle Homes are unregistered from the global inventory


 Solution:

             Make sure that the above Oracle Homes are unregistered from the global inventory. we                         should always clean the oraInventory directory by using the following commands.

             cd $ORACLE_HOME/oui/bin
 
              ./runInstaller -silent -deinstall REMOVE_HOMES=                                 {“/u01/oracle/R122/fs1/FMW_Home/Oracle_EBS-app1”}
              ./runInstaller -silent -deinstall REMOVE_HOMES={“/u01/oracle/R122/fs1/FMW_Home/oracle_common”}
              ./runInstaller -silent -deinstall REMOVE_HOMES={“/u01/oracle/R122/fs1/FMW_Home/webtier”}
   

                                        Then restart the adcfgclone.pl appsTier




STANDBY- ORA-01275: Operation RENAME is not allowed if standby file management is automatic

Issue:-

While re-locating oracle datafile's below error we are getting.

SQL> alter database rename file '/u01/oradata/SAMPLE/data10.dbf' to '/u04/oradata/SAMPLE/data10.dbf';
alter database rename file '/u01/oradata/SAMPLE/data10.dbf' to '/u04/oradata/SAMPLE/data10.dbf'
*
ERROR at line 1:
ORA-01275: Operation RENAME is not allowed if standby file management is
automatic.

Cause:-

The standby file_management is set to auto on standby database.

Solution:

Run below command and try to re-run the query

alter system set standby_file_management=manual

once re-locating of datafile completed you can change back to "AUTO"

alter sytem set standby_file_management=auto

ORA-01516: nonexistent log file, data file, or temporary file

Issue:

SQL> alter database rename file '/u01/oradata/SAMPLE/index19.dbf'     to '/u06/oradata/SAMPLE/index19.dbf';
alter database rename file '/u01/oradata/SAMPLE/index19.dbf'     to '/u06/oradata/SAMPLE/index19.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/oradata/SAMPLE/index19.dbf"

Causes:-

1.This file is not available the mentioned location
2.check the name of the datafile(case sensitive)

Solution:-

1. Check the fild# and location from v$datafile and re run the query.

Missing command :fuser Prerequisite check "CheckSystemCommandAvailable" failed.

Problem:
Opatch failed with following error message
Missing command :fuser
Prerequisite check "CheckSystemCommandAvailable" failed.

Solution:

Solution Description:
Go to patch home/bin directory. go to $ORACLE_HOME/bin and create a file with name fuser. This will solve your issue.
cd $GRID_HOME/bin
touch fuser
chmod 755 fuser
ls -tlr fuser
-rwxr-xr-x 1 dba oinstall 0 Aug 25 01:51 fuser

Re- run opatch session and check.

OPatch failed to locate Central Inventory/OPatch failed with error code 73

Solution:

The following problem was caused by our recent migration, the inventory was pointing to a home that was no longer on the server.

We need to attach the current home to the inventory, by using below command we can solve this issue.

$cd $OH/oui/bin

$ ./runInstaller -silent -invPtrLoc "/etc/oraInst.loc" -attachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0.4" ORACLE_HOME_NAME="ORACLEHOME"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 18432 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oracle/oraInventory

'AttachHome' was successful.

Monday, October 9, 2017

                   How to Apply Opatch on Oracle RAC instances



Steps:

       1) Check Oracle service status
               
                                       crs_stat -t


   2)Stop database instance one by one
 
                                      srvctl stop instance -i DB_NAME 1 -d INSTANCE_NAME 
                srvctl stop instance -i DB_NAME 2 -d INSTANCE_NAME

   3)Stop ASM instance on each node one by one

                                      srvctl stop asm -n rac-node1
                srvctl stop asm -n rac-node2

   4)Go to the downloaded patch folder 

                                      export OPATCH  path
 
               export PATH=$PATH:$ORACLE_HOME/Opatch                                

       5)Check whether patch is applied or not

                                       opatch lsinventry
                                
        6)Start apply patch

                               opatch apply                    
     

After applying opatch  successfully

        7)Restart the database instance and asm instance

                                       srvctl start asm -n rac-node1
                                       srvctl start asm -n rac-node2
                                       srvctl start instance -i DB_NAME 1 -d INSTANCE_NAME 
                                       srvctl start instance -i DB_NAME 2 -d INSTANCE_NAME


    8)Then take the invalid count on both nodes