How To Download And Install The Latest OPatch Version

How To Download And Install The Latest OPatch Version

1) Please download the latest OPatch version from My Oracle Support (MOS) using the next reference:

Patch 6880880

Or using the next URL:

https://updates.oracle.com/download/6880880.html

2) In the "Platform" field select the relevant platform that corresponds to the Oracle product.

3) Click the Download button

4) Upload the opatch.zip file to your OS host and unzip it:
Example:

% mv p6880880_101000_SOLARIS64.zip $ORACLE_HOME

% cd $ORACLE_HOME

(If a former OPatch directory already exist, then rename it)
% mv Opatch OPatch_100057_OLD
% unzip p6880880_101000_SOLARIS64.zip
Archive: p6880880_101000_SOLARIS64.zip
creating: OPatch/
creating: OPatch/docs/
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/README.txt
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/docs/bt1.txt
inflating: OPatch/docs/bt2.txt
inflating: OPatch/docs/tmp
inflating: OPatch/emdpatch.pl
creating: OPatch/jlib/
inflating: OPatch/jlib/opatch.jar
inflating: OPatch/opatch
inflating: OPatch/opatch.bat
inflating: OPatch/opatch.pl
creating: OPatch/perl_modules/
inflating: OPatch/perl_modules/Apply.pm
inflating: OPatch/perl_modules/AttachHome.pm
inflating: OPatch/perl_modules/Command.pm
inflating: OPatch/perl_modules/LsInventory.pm
inflating: OPatch/perl_modules/Query.pm
inflating: OPatch/perl_modules/RollBack.pm
inflating: OPatch/perl_modules/Version.pm
inflating: OPatch/perl_modules/XML.pm
inflating: OPatch/perl_modules/opatchIO.pm
inflating: OPatch/README.txt
creating: OPatch/ocm/
extracting: OPatch/ocm/ocm.zip
creating: OPatch/ocm/doc/
inflating: OPatch/ocm/doc/license.txt
creating: OPatch/ocm/lib/
inflating: OPatch/ocm/lib/emocmutl.jar
creating: OPatch/ocm/bin/
inflating: OPatch/ocm/bin/emocmrsp
% cd OPatch
% opatch version
Oracle Interim Patch Installer version 1.0.0.0.58
Copyright (c) 2007 Oracle Corporation. All Rights Reserved..
We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com
OPatch Version: 1.0.0.0.58

5)You can launch OPatch tool by using the absolute path :
ex.:
$ORACLE_HOME/OPatch/opatch version
Or you can set the OPatch directory in your PATH variable so you can execute the OPatch command (opatch <args>) from anywhere without using the absolute path.
ex.:
For Korn / Bourne shell:
% export PATH=$PATH:$ORACLE_HOME/OPatch
For C Shell:
% setenv PATH $PATH:$ORACLE_HOME/OPatch

Reference metalink Doc ID 274526.1

Opatch lsinventory Reports "OUI-67076 : OracleHomeInventory was not able to create a lock file" in Unix

Opatch lsinventory Reports "OUI-67076 : OracleHomeInventory was not able to create a lock file" in Unix

Sometimes the command "opatch lsinventory" reports the error:
OUI-67076:OracleHomeInventory was not able to create a lock file, probably due to a previous failed OPatch Session. The loaded inventory might not show correctly what you have in the Oracle Home.

Cause:

A previous "opatch apply" session failed and so a lock still exists on the local inventory

Solution:

1. Take a backup of  $ORACLE_HOME/.patch_storage

2. Remove $ORACLE_HOME/.patch_storage/patch_locked

% rm $ORACLE_HOME/.patch_storage/patch_locked

3. Create $ORACLE_HOME/.patch_storage/patch_free

% touch $ORACLE_HOME/.patch_storage/patch_free

4. Verify that "opatch lsinventory" no longer reports the error

% opatch lsinventory -detail

Reference metalink Doc ID 1086853.1

How to check Pre-requistics Patches while applying Database Patch using opatch

How to check Pre-requistics Patches while applying Database Patch using opatch

Checking any conflicts for the patches when applying any CPU, because this conflict's checking command is not there even in README.txt file that is distributed with patch.

Please use below command to check the conflicts aganist the oracle_home and avoid problems.

Step 1:unzip your patch zip directory

Step 2:run below command

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <patch_directory>

Example:
$ unzip p8657018_10204_linux.zip
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir 8657018

Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /u01/app/oracle/product/11.2.0.3/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

Step 3: verify the Logfile

/home/oracle/patch/8657018
>ls -altr
total 24
-rw-rw-r-- 1 oracle oinstall 5905 May 14 03:35 README.txt
drwxr-xr-x 3 oracle oinstall 4096 May 14 03:35 files
drwxr-xr-x 4 oracle oinstall 4096 May 14 03:35 etc
drwxrw-rw- 3 oracle oinstall 4096 Jul 24 11:30 ..
drwxrwxr-x 4 oracle oinstall 4096 Jul 24 11:30 .
node1(TESTDB1)  /home/oracle/patch/18308717

vi /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log

[Jul 24, 2014 5:52:50 PM]    PREREQ session
[Jul 24, 2014 5:52:50 PM]    OPatch invoked as follows: 'prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc '
[Jul 24, 2014 5:52:50 PM]    OUI-67077:
                             Oracle Home       : /u01/app/oracle/product/11.2.0.3/dbhome_1
                             Central Inventory : /u01/app/oraInventory
                                from           : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc
                             OPatch version    : 11.2.0.3.6
                             OUI version       : 11.2.0.3.0
                             OUI location      : /u01/app/oracle/product/11.2.0.3/dbhome_1/oui
                             Log file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log
[Jul 24, 2014 5:52:50 PM]    Patch history file: /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
[Jul 24, 2014 5:52:50 PM]    Invoking prereq "checkconflictagainstohwithdetail"
[Jul 24, 2014 5:52:51 PM]    Patch 18308717 is not subset of any other patch processed till now
[Jul 24, 2014 5:52:51 PM]    Patch ID  18308717
[Jul 24, 2014 5:52:51 PM]    Patches that conflict: [  ]
[Jul 24, 2014 5:52:51 PM]    Checking conflicts for patch: 18308717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:18308717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:17076717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:17296419
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:16475860
[Jul 24, 2014 5:52:51 PM]    Prereq "checkConflictAgainstOHWithDetail" passed.
[Jul 24, 2014 5:52:51 PM]    Finishing PrereqSession at Thu Jul 24 17:52:51 IST 2014

Message:Patch 8657018 is not subset of any other patch processed till now
From the above logs we can confirm that there are no-prerequistics patches to be applied before applying 8657018.

Turn Off Recyclebin In Oracle 10g/11g

Turn Off Recyclebin In Oracle 10g/11g

One of the coolest feature in Oracle starting from 10g is recyclebin but sometimes it is annoying to scroll down and see a long list objects dropped, especially in development environment where you drop more tables than you create.

SQL> SHOW PARAMETER RECYCLEBIN

NAME                                 TYPE        VALUE
———————————— ———– ——————————
recyclebin                           string      on

To turn the feature ON or OFF here what you have to do:

In Oracle 10g database:

SQL> ALTER SYSTEM SET recyclebin = OFF;
System altered.

SQL> ALTER SYSTEM SET recyclebin = ON;
System altered.

In Oracle 11g database:

SQL> ALTER SYSTEM SET recyclebin = OFF DEFERRED;
System altered.

SQL> ALTER SYSTEM SET recyclebin = ON DEFERRED;
System altered.

NOTE: DEFERRED is used because it takes effect in new sessions in the database.

You will face ORA-02096 in 11g if skipped DEFERRED i.e

SQL> ALTER SYSTEM SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
To enable the recycle bin for a session:

SQL> ALTER SESSION SET recyclebin = ON;
To disable the recycle bin for a session:

SQL> ALTER SESSION SET recyclebin = OFF;

RMAN - Logging Commandline RMAN Output

RMAN - Logging Commandline RMAN Output

Problem:

You want to log the output of RMAN commands you issue in command-line mode.

Solution:

If you want RMAN to log all its output when you use RMAN from the operating system command line, just add the keyword log to the command line, and supply the name of the log file to use. For example:

$ rman target / cmdfile commandfile1.rcv log /u01/app/oracle/outfile.txt

In this case, RMAN will write the output of the RMAN commands in the command file named commandfile.rcv to the log file outfile.txt. If you later want to run another set of RMAN commands and want to append the log messages to the same log file, you can do this by using the append option along with the log option. Here’s an example:

$ rman target / cmdfile commandfile2.rcv log /u01/app/oracle/outfile.txt append

The previous command will append the output from executing the command file commandfile2.rcv to the text file outfile.txt.

How It Works

The command-line argument log causes RMAN to send all its output to the log file you specify.Failure to add the keyword append when referring to an already existing log file will result in the overwriting of that older log file. If you are running RMAN interactively and you want to see output on your terminal screen as well as have it written to a log file, you can take advantage of the Unix/Linux tee command. The tee command sends output both to a text file and to the terminal. Here’s how you use the tee command:

$ rman | tee rman.log
RMAN>

All is not lost if you don’t specify a log file to capture the RMAN output. The view V$RMAN_OUTPUT returns detailed information about RMAN jobs in progress. For example, if your media manager runs into a problem with a tape drive, RMAN records the associated error messages in V$RMAN_OUTPUT and also outputs the message to the terminal or to a log file. As with all dynamic performance views, the contents of the V$RMAN_OUTPUT view are refreshed when you restart the database. The V$RMAN_STATUS view contains information about completed RMAN jobs as well as all RMAN jobs in progress

RMAN-Executing Operating System Commands From Within RMAN

RMAN-Executing Operating System Commands From Within RMAN

Problem:

You’ve invoked the RMAN client, and now you need to issue some operating system commands.

Solution:

Use the RMAN command host to invoke an operating system subshell. You can execute this command in two ways: you can issue it from the RMAN prompt, or you can execute it from inside a run block, which is a group of RMAN commands executed as a single unit. If you issue the host command stand-alone, without any parameters, RMAN will take you to the operating
system command line. Thus, the host command works the same in RMAN as it does from within SQL*Plus. If you issue the command host followed by a valid operating system command as a parameter, then RMAN will execute that operating system command and continue to process the rest of the commands in the run block, if there are any.
In the following example, we use the host command to list all files ending with dbf, after backing up a datafile from the RMAN prompt:

RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> backup datafile '/u01/app/oracle/oradata/targ/system01.dbf' format '/tmp/system01.dbf';
RMAN> host 'ls -l /tmp/*dbf';
RMAN> alter database open;

The following example uses the host command with no parameters to temporarily escape to the operating system level during an interactive RMAN session:

RMAN> backup datafile 3 format '/u01/app/oracle/oradata/targ_db/dbs01.cpy';
RMAN> host;
$ ls $ORACLE_HOME/oradata/dbs01.cpy /net/oracle/oradata/dbs01.cpy
$ exit
RMAN>

How It Works

As you can see in the two examples, you can use the host command with or without an operating system command as a parameter. If you run the host command as part of a series of RMAN commands, RMAN executes the host command and continues with the rest of the commands. When you execute the host command by itself, RMAN displays the operating
system command prompt and resumes after you exit the command-line subshell.

RMAN-Checking the Syntax of RMAN Commands

RMAN-Checking the Syntax of RMAN Commands

Problem:

You want to check the syntax of your RMAN commands without actually executing the commands.

Solution:

To check the syntax of RMAN commands, you must start the RMAN client with the operating system command-line argument checksyntax. You can easily check the syntax of commands prior to their execution either by entering them at the command prompt or by reading in the commands through a command file. Here’s how you check the syntax of a single RMAN command (run {backup database;}) by first starting the RMAN client with the checksyntax argument:

$. /rman checksyntax

Recovery Manager: Release 11.1.0.1.0 - Beta on Mon Apr 2 08:31:11 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> run {backup database;}
The command has no syntax errors
RMAN>

In this example, there were no errors in the syntax of the simple run block, and RMAN confirms that. You can also use the checksyntax argument to check the syntax of RMAN commands that are part of a command file. Simply specify the checksyntax argument before invoking the command file that consists of the RMAN commands. In the following example,
the file goodcmdfile contains a couple of restore and recovery commands:

$ rman checksyntax @/tmp/goodcmdfile
Recovery Manager: Release 11.1.0.1.0 - Beta on Mon Apr 2 08:31:11 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> # file with legal syntax
2> restore database;
3> recover database;
4>
The cmdfile has no syntax errors
Recovery Manager complete.
$

You can also open an RMAN session solely for the purpose of checking the syntax of commands
that you type interactively:

$ rman checksyntax

An important point about the checksyntax argument is that you can’t use it after starting RMAN. That is, you can’t include the checksyntax argument from the RMAN command line. You must pass checksyntax as an argument to the rman command when you start the RMAN client and without connecting to any target or recovery catalog.

How It Works
When you either execute an RMAN command file by preceding it with the checksyntax argument or enter any RMAN commands after starting RMAN with the checksyntax argument, RMAN won’t actually execute any RMAN commands. RMAN will check and report only on the syntax of those commands. If the RMAN commands that you type at the command line or that you include as part of a command file have no errors, you get the “the command (cmdfile) has no errors” message from RMAN. Otherwise, RMAN will issue an error, as shown in the following example:

$ rman checksyntax @/tmp/badcmdfile
Recovery Manager: Release 11.1.0.1.0 - Beta on Mon Apr 2 08:31:11 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> # file with illegal syntax
RMAN> run (backup database);
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "(": expecting one of: "{"
RMAN-01007: at line 1 column 5 file: standard input
RMAN>

The output of the checksyntax command reveals there is a syntax error in your run block. The checksyntax command is handy for checking scripts for syntax errors. With RMAN, there’s no need for a script to fail unexpectedly because you mangled the syntax of a command. If you’re surprised by an error, it’s because you didn’t test with checksyntax first.

Archives Not Applying On Standby Database

Archives Not Applying On Standby Database

Due to abnormal network outage for 10-12 hrs my standby database was behaving abnormally.

All the archives are moving properly from Primary node to DR. But it was not being applied.

Then we checked the gap using below command, and there were no gap.
select * from v$archive_gap;

Then we checked maximum sequence upto which archives had applied.
select THREAD#,max(SEQUENCE#),APPLIED from v$archived_log where APPLIED='YES' group by thread#,applied;

It showed sequence of 10 hrs old.It means my archives had not been applied for 10 hrs.

It means MRP process is hanged. Now we need to do manually recovery. We cancelled the recovery,
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

But our session got hanged. We waited for 20min but unluckily no progress.

Then we down our database by shutdown immediate command.Even it also got hanged.

Finally, we abnormally shutdown our database by shutdown abort and did manual recovery.

a) shutdown abort;

b) startup nomount;

c) alter database mount standby database;

--Manual recovery:

d) RECOVER AUTOMATIC FROM '/ora_arch/archivelog' STANDBY DATABASE UNTIL CANCEL;

e) alter database recover cancel;
--Manual recovery completed successfully

Now put the database in recovery mode.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

But unfortunately at the same time one more problem encountered.When i checked  gap,
SQL> select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
2          4470           4470

There was a gap of only 1 archive of node 2. We waited for 50 min but there is no progress.Then we manually registered that archive.

alter database register or replace logfile '/ora_arch/arch_2_4470_718297972.arc';

Finally,there was no gap and even archives were applying normally.

Common Commands Used In Physical Standby Database

Common Commands Used In Physical Standby Database

Mount Standby Database:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Check Modes of Standby Database:
SQL> select open_mode, protection_mode, protection_level from v$database;
OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL
-------------------- -------------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

Cancel Recovery:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Check SCN:
SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

Archives applied upto which sequence:
SQL> select THREAD#,max(SEQUENCE#),APPLIED from v$archived_log where APPLIED='YES' group by thread#,applied;
SQL> select sequence#,applied from v$archived_log;

If manually need to apply any gap log:
SQL> alter database register or replace logfile '/ora_arch/arch_2_4470_718297972.arc';

Manually Recovery/Applying the Logs in the Archive Gap to the Standby Database:
SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;

To check if you are using Real-Time Apply:
SQL> SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

Check Status of Processes:
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CLOSING
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
RFS       IDLE
MRP0      WAIT_FOR_LOG------- It means Redo apply is not real-time
RFS       IDLE

To identify standby redo logs:
SQL> select * from v$standby_log;

Check members of standby redo log file:
SQL> select * from  v$logfile where type=’STANDBY’;

To add Standby Redo Log File Group to a Specific Group Number:
SQL> alter database add standby logfile group 4 (
‘/<full_path_for_srl>/log04a.dbf’,
‘/<full_path_for_srl>/log04b.dbf’
) size 50m;

Note:Standby Redo Logs should be same size as of Online Redo Logs.

On Primary Node:

Enable/Disable Archive Dest:
alter system set log_archive_dest_state_2=defer sid='*' scope=both;
alter system set log_archive_dest_state_2=enable sid='*' scope=both;

Check Archive Gap:
SELECT applied.thread# ,last_applied ,newest_log ,newest_log - last_applied gap FROM ( SELECT thread# ,MAX( sequence# ) last_applied FROM v$archived_log WHERE applied='YES' GROUP BY thread# ) applied, ( SELECT thread# ,MAX( sequence# ) newest_log FROM v$archived_log WHERE applied='NO' GROUP BY thread# ) newest WHERE applied.thread# = newest.thread#;

To identify the logs in the archive gap
SQL> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT
FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES';

LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
15147         15147

To check the Current log sequence
select THREAD#,max(SEQUENCE#) from gv$log where THREAD# in (1,2) group by THREAD#;

Automatic Storage Management (ASM)

Automatic Storage Management (ASM)

Automatic Storage Management (ASM) is an option and a new feature of Oracle 10g and onwards that simplifies the storage management for all Oracle database file types. It renders the capabilities of a volume manager and filesystem together into
the Oracle database kernel. Although, it inherits the Stripe And Mirror Everything (SAME) functionality, it strips the data (extents) evenly across the ASM disks of a disk group by default and provides the mirroring functionality as an option. The
management and administration of ASM is made easy through a well-known set of SQL statements, such as, CREATE, ALTER, DROP, and through GUI tools.

While the ASM was initially intended for managing and maintaining only the Oracle database files and other related files, its functionality has been significantly improved in 11g R1 and R2 versions to manage all types of data. The following are some of the key features and benefits of ASM:

-It simplifies the storage configuration management for Oracle datafiles and other files.
-It eliminates the need for third-party software, (for example, volume manager) to manage the storage for the databases.
-When a datafile is created, it is divided into equally sized (1, 2, 4, 8, 16, 32,or 64 MB) extents that are scattered evenly across the disks of a disk group to provide balanced I/O to improve performance and prevent hot spot symptoms.
-It is built on the Stripe and Mirror Everything (SAME) functionality.
-It supports both non-RAC and RAC databases efficiently.
-It has the ability to add and remove ASM disks online without actually disturbing the ongoing operations.
-It can be managed and administrated using a set of known SQL statements.
-It performs automatic online redistribution for the data whenever a disk is being added or dropped.
-With ASM 11g R2, in addition to all database file types, it can also be used to store non-Oracle datafile types such as binaries, images, and so on. Beginning with ASM 11g R2, it provides the ability of a preferred read functionality, when ASM mirroring features are enabled.
-It supports multiversioning of databases.
-It supports a multipathing feature to prevent outages from disk path failures.

About ASM Disk Group

About ASM Disk Group

A disk group is a logical container for one or more ASM disks and is the highest level of data structure in ASM. When a database is configured to employ the disk group, the disk group then becomes the default location for its datafiles. The disk group
can be used to place various database file types, such as datafiles, online redo, archivelogs, RMAN backupsets, OCR and Voting disks (in 11g R2), and more. ASM also provides the flexibility of utilizing a single disk group by multiple ASM instances
and databases across a cluster.

After a disk group is successfully created and mounted for the first time in the ASM instance, the name of the disk group is automatically affiliated with the ASM_DISKGROUPS initialization parameter to be able to mount the disk group at ASM
instance restarts.

In general, when a datafile is created in a disk group, the datafile extents are striped/distributed evenly across the available disks of the disk group. Optionally, you can also set the following specified mirroring level at the disk group to protect the data
integrity by storing redundant copies of data (extents) in a separate failure group to cope with the disk outage symptom:

-External redundancy: Relies on the STORAGE (RAID)-level mirroring redundancy option to protect the data
-Normal redundancy: Provides a default two-way mirroring option
-High redundancy: Provides a three-way mirroring redundancy option of ASM files

As of 11g R2, the following limits have been imposed on the ASM instance.
-A maximum of 63 disk groups in a storage system
-1 million files per disk group

ASM Dynamic Views

ASM Dynamic Views

To manage and supervise the ASM instance and its primary components (disk and disk group) effectively, you really need to know the ASM specific dynamic views, and how to map them to extract useful information about disks, disk groups, and
so on. This section will help you to understand the use of the ASM specific dynamic views available in ASM.

There are about 19 dynamic views available, as of 11g R2, and each dynamic view provides different helpful information. All the ASM dynamic views are predefined with V$ASM_. In the following section, we are going to focus on a handful of ASM
dynamic views.

V$ASM_DISK
When an ASM instance completes the disk discovery operation by reading the disk header information, all disks (used and usable) will then list in the V$ASM_DISK view. Each individual disk has a row in the V$ASM_DISK dynamic view and contains very useful information. The PATH column specifies the disk's name and location. The HEADER_STATUS column, in most cases, contains the following three possible values:

• CANDIDATE: Indicates that the unused disks are ready for use.
• FORMER: Indicates that the disk was formerly part of a disk group and is now ready for use.
• MEMBER: Indicates that the disk is currently part of an active disk group.

Apart from the preceding values, the view also contains other useful information about the disks, such as total size, free size, physical reads, redundancy level, and so on. On the flipside, every time you run a query against this view, ASM initiates disk
discovery operations for the new disks, where it reads all disk header information. Querying against this view could be an expensive operation at times, and could impact performance.

The following list of SQL statements demonstrates some of the useful queries against the view. However, prior to running these commands, ensure you are connected to an ASM instance through sqlplus with SYSASM privilege:

SELECT path,header_status,total_mb FROM v$asm_disk WHERE header_status in ('CANDIDATE','FORMER','PROVISIONED');

The preceding command displays information about the disks that are eligible to use.

V$ASM_DISKGROUP
After a disk group is successfully created in the local ASM instance, the disk group summary is visible in the V$ASM_DISKGROUP view. Each disk group maintains a row in the view along with the important information, such as disk group number and name, total disk group size, used space, free space, redundancy type, compatibility, mount state, and so on. Every time a query is run against the view, it is likely to have a similar impact to querying the V$ASM_DISK views. The following SQL command extracts the mounted disk group's name, total disk group size, and the free space left in the group:

SELECT name,state,total_mb,usable_file_mb FROM v$asm_diskgroup;

V$ASM_OPERATION
V$ASM_OPERATION is one of the useful views that displays a row for each long running operation in the ASM instance. For example, when a disk is being dropped or attached to an existing disk group, an ASM should initiate and complete the
rebalancing operations just before releasing the subject disk. Therefore, the view will present useful details, such as the amount of work that has been completed, and show the estimated time (in minutes) required to complete the operations. This should help
you to understand how long the operation will take to complete.

V$ASM_DISK_STAT
Although the V$ASM_DISK and V$ASM_DISK_STAT views display nearly identical information, querying the V$ASM_DISK_STAT view results is a less expensive operation in comparison to the V$ASM_DISK view. On the flip side, this view doesn't display the details about new disks on the system that the ASM instance has yet to discover. As querying the view is less expensive, it is strongly recommended that you use this view in order to display the information and statistics (read/write)
about the existing disks.

V$ASM_DISKGROUP_STAT
The V$ASM_DISKGROUP_STAT view displays statistical information about the mounted disk groups in the ASM instance. Unlike the V$ASM_DISKGROUP view, a query against this view doesn't result in new disk discovery operations but is less expensive in terms of performance. Therefore, it is recommended to use this view to display existing disk group information and statistical information about the disk groups.

V$ASM_CLIENT
When the V$ASM_CLIENT view is queried in the ASM instance, it displays the information about the database instances that are using the disk groups mounted and managed by the ASM instance.

ASM Instance Startup/Shutdown

ASM Instance Startup/Shutdown

Managing an ASM instance is no different from managing the typical RDBMS database instances. The ASM instance could be managed by either using a set of SQLPLUS commands or the cluster aware SRVCTL utility. Nevertheless, it is strongly
recommended that you use the SRVCTL utility for managing the (start/stop) ASM instance in an RAC environment.

The ASM instance can be opened either in NOMOUNT, MOUNT, or RESTRICTED modes with the STARTUP command at the SQLPLUS prompt. When you have a planned maintenance on an ASM instance, you can open the ASM instance in a RESTRICT
mode to avoid any possible connections from the database instances. When the ASM instance is opened gracefully, it first discovers the disks and then mounts all the existing disk groups on the local instance.

To shut down the local ASM instance, you can use the various options available with the SHUTDOWN command in the SQLPLUS prompt. The supported options are NORMAL, IMMEDIATE, TRANSACTIONAL, and ABORT. As mentioned previously, you can use the SRVCTL utility to bring down the ASM instance as well. The following list of examples demonstrates how to start up/shut down an ASM instance using SQLPLUS and SRVCTL utilities:

srvctl stop asm –n raclinux1 –o normal:immediate:transactional:abort

export ORACLE_SID=+ASM1
export ORACLE_HOME=$ASM_HOME (in 11gR1)
export ORACLE_HOME=$ORA_GRID_HOME (in 11gR2)
sqlplus / as sysasm
SQL> SHUTDOWN NORMAL:IMMEDIATE:TRANSACTIONAL:ABORT

This example stops the ASM instance on raclinux1 node. Alternatively, you can also use either of the shutdown options.

srvctl start asm –n raclinux1 –o nomount:mount:restrict

export ORACLE_SID=+ASM1
export ORACLE_HOME=$ASM_HOME (in 11gR1)
export ORACLE_HOME=$ORA_GRID_HOME (in 11gR2)
sqlplus / as sysasm
SQL> STARTUP NOMOUNT:MOUNT:RESTRICT

This startup command starts up the ASM instance on raclinux1 node. When you start the instance in NOMOUNT mode, an ASM instance will be started without mounting the existing disk groups. When the instance is started in RESTRICT mode, no database
instance can attach to the ASM instance and all the disk groups are opened in restricted mode too.

Ensure that the database instances that are currently associated with the local ASM instance are stopped prior to shutting down the local ASM instance to avoid encountering an ASM instance shutdown error. In this case, the ASM instance will remain opened. Alternatively, you could use the ABORT option to forcefully shut down the currently connected database's instance before shutting down the ASM instance. It is also strongly recommended to dismount any existing Oracle Cluster File System (ACFS) to avoid any application I/O errors.

Note: If the voting disk and OCR files are placed in a disk group, you will not be able to stop the ASM instance.
In order to stop the ASM instance, you need to stop the cluster.

EBS: Workflow Mailer Status

EBS: Workflow Mailer Status

1. Check workflow mailer service current status

select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';

Number of running processes should be greater than 0

2. Find current mailer status

SELECT component_name as Component, component_status as Status FROM fnd_svc_components where component_name = 'Workflow Notification Mailer';
or
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');

Possible values:

RUNNING
STARTING
STOPPED_ERROR
DEACTIVATED_USER
DEACTIVATED_SYSTEM

ASM Background Processes

ASM Background Processes

An ASM instance comes with a few additional background processes that assist the ASM instance to perform its course of action. Therefore, understanding the individual role played by these ASM-specific background processes in order to know how they help the ASM instance to manage and carry out its functionality, will be helpful to DBAs. Here Iam going to cover the most useful ASM-specific background processes and the part played by the individual ASM-specific background processes:

RBAL - Rebalancer: It opens all the device files as part of disk discovery and coordinates the ARB processes for rebalance activity.

ARBx - Actual Rebalancer: They perform the actual rebalancing activities. The number of ARBx processes depends on the ASM_POWER_LIMIT init parameter.

ASMB - ASM Bridge: This process is used to provide information to and from the Cluster Synchronization Service (CSS) used by ASM to manage the disk resources. Responsible to communicate the database Instance to the ASM Instance, provides the heartbeat to the ASM instance.

ORBn - Rebalance ASM data extent movements. There can be many of this at the same time.

OSMB - (ASM) Any database instance that is using an ASM disk group will contain a background process called OSMB. The OSMB process is responsible for communicating with the ASM instance..Helps to manage the drive storage.

GMON - ASM Diskgroup monitor process, monitors all the mounted diskgroups and is responsible for maintaning consisten disk membership and status information

PZ9n - Parallel slave processes are used to fetch data from GV$ dynamic view in a cluster

Startup Oracle Database Instance

Startup Oracle Database Instance

Startup NOMOUNT:
During startup nomount Instance is started. It creates the SGA and starts up the background processes, and we need pfile/spfile for nomount the database and it doesn't provide access to the database.

Startup MOUNT:
During Startup mount Control file is created and opened for this instance.It mounts the database for certain DBA activities but does not provide user access to the database.

Alter database OPEN:
During startup database all the files get's opened as described by the control file for this instance. And it enables users to access the database.

Why should Apps & Applsys passwords always be the same

Why should Apps & Applsys passwords always be the same

The need to have the same password for Apps and Applsys is because when you sign on to apps, intially it connects to a public schema called APPLSYSPUB.
This validates AOL username and password that we enter (operations/welcome) using guest user account. Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to APPS schema.
Since it uses both applsys and apps during signon process this expects both the password to be identical.

Note:
-Apps is a universal schema has synonyms to all base product tables and sequences.This also has code objects for all products (triggers, views, packages, synonyms etc.).
-Applsys schema has applications technology layer products like FND and AD etc.

Manager Status Shows System Hold, Fix Manager before resetting counters

Manager Status Shows System Hold, Fix Manager before resetting counters

Issue started after bouncing all the servers.

Cause:

An orderly shutdown of the concurrent manager was not performed. The CP Diagnostic Request Analyzer" shows that a CP Shutdown was initiated at on 16-OCT-12 at 22:22:41 and then cancelled.

Yet, the database alert log file shows that the database was shutdown within four seconds of the concurrent managers being shutdown/cancelled, not providing enough time to perform an orderly shutdown of the concurrent processing server.

Solution:

1.Shutdown the Concurrent Processing (CP) server in an orderly manner and verify that the shutdown is complete before invoking a shutdown of the database and/or the database listener. Check the last CP process to exit/terminate, the Internal Manager, with the Unix command: ps -ef | grep FNDLIBR

If a manual shutdown is being performed, use the same sequence found in the adstpall.sh – "Stop All Oracle E-Business Suite Enabled Services" script: first the CP Server--once down--shutdown the App Listener, the Forms Server, the Web (Apache) Server. Lastly, if necessary, shut down the database listener and/or database.

Note 1:The start up is the same, but in reverse order. In this case, a complete bounce of all servers (apps/db tier) restored a normal manager status/state.

2.In the event that the CP server was not shutdown properly, please run the Concurrent Manager Recovery feature to address any Concurrent Manager / Concurrent Processing issues within the Oracle Application Manager.. In some cases, it may be necessary to bounce the database.


Note 2:Shutting down the database or the listeners without an orderly shutdown of the Concurrent Processing server is the equivalent of pulling the power cord on a Unix box. 999 times out of 1000, the Unix system will recover without any incident; however, at some point file/disk corruption or an odd state will occur; similarly, the same applies to the CP server, manager queue and process state information may not be current or correct.

Note 3:The Concurrent Manager Recovery was created by CP Development. It is a helpful for re-initializing the managers, particularly when a manager's normal operation may have been compromised by manual intervention by a DBA or System Administrator (by manual killing of OS / RDBMS processes) or due to unplanned system outages (an accidental closing of the database without doing an orderly shutdown of the concurrent managers, power / network outages, etc).

OR

1.Go to the Concurrent Manager Administer screen >.

2.Please click the "Fixed" button and restart the concurrent manger.

NOTE:The button will be enabled only when the manager is stuck in: "System Hold, Fix Manager". Then bring the mouse control to the specific manager to get the button enabled.

Reference metalink Doc ID 1507580.1

Concurrent Processing - Output Post Processor is Down with Actual Process is 0 And Target Process is 1

Concurrent Processing - Output Post Processor is Down with Actual Process is 0 And Target Process is 1

After recent cloned instance find that actual process of concurrent manager Output Post Processor is 0 and target process is 1.

Cause:

Executable FNDSVC is not generated / relinked properly in the new target instance after cloning.

Due to this OPP concurrent manager was not coming up. This can be verified from backend command line , which returns nothing.

ps -ef | grep applmgr | grep FNDSVC

Solution:

To implement the solution, please execute the following steps:

1. Telnet to application tier server via OS user applmgr, and source the env file
$APPL_TOP/APPS<SID>_hostname.env.

2. Shutdown concurrent server via command adcmctl.sh under
$COMMON_TOP/admin/scripts/<context_name>

3. To ensure concurrent manager down; check there is no FNDLIBR process running.
ps -ef | grep applmgr | grep FNDLIBR

4. Run adadmin to relink FNDSVC executable.

1. Invoke adadmin from command prompt
2. Choose option 2 (2. Maintain Applications Files menu)
3. Choose option 1 (1. Relink Applications programs )
4. Then type "FND" When prompted; ( Enter list of products to link ('all' for all products) [all] : FND )
5. Ensure adrelink is exiting with status 0

5. Start Concurrent Managers using adcmctl.sh

6. Retest the issue.

7. Migrate the solution as appropriate to other environments.

Reference metalink Doc ID 858813.1

Concurrent Managers Fail to Start, No Service Manager Process Spawned OR Fails with: ORA-01427, ORA-06512

Concurrent Managers Fail to Start, No Service Manager Process Spawned OR Fails with: ORA-01427, ORA-06512 

cleanup_node failed due to ORA-01427: single-row subquery returns more than one row
ORA-06512: at "APPS.FND_CP_FNDSM", line 29
ORA-06512: at line 1.

The SQL statement being executed at the time o
Routine AFPEIM encountered an error while starting concurrent manager STANDARD with library /mnt/oraarch1/arch1appl/fnd/11.5.0/bin/FNDLIBR.

Check that your system has enough resources to start a concurrent manager process. Contact your system administrator : 28-MAR-2011 05:36:25
Starting STANDARD Concurrent Manager : 28-MAR-2011 05:36:25
ORACLE error 1422 in get_sm_info

Cause:

The issue was caused by incorrect node names in the FND_NODES table, the table had not been updated to remove non existent nodes.

Solution:

1.Stop the services or atleast concurrent manager

2.Execute FND_CONC_CLONE.SETUP_CLEAN

3.Run AutoConfig on the database tier.

4.Run AutoConfig on the apps tier.

5.Check if AutoConfig completed successfully.

6.Start the services/concurrent managers and re-test.

Reference metalink Doc ID 470100.1

Workflow Notification Mailer Not Starting After Running AutoConfig With Errors: Unable to make a network connection OR java.lang.RuntimeException: Connection refused

Workflow Notification Mailer Not Starting After Running AutoConfig With Errors: Unable to make a network connection OR java.lang.RuntimeException: Connection refused

The Workflow Notification Mailer fails to start after having run AutoConfig, Patching, etc

Sample errors that can be encountered:

oracle.apps.fnd.cp.gsc.SvcComponentContainerException: Could not start component; performing rollback -> oracle.apps.fnd.cp.gsc.SvcComponentException: Validation failed for the following parameters -> {OUTBOUND_SERVER=Unable to make a network connection.
or
April 14, 2014 1:05:28 PM EST]:1327428328284:-1:-1:oracle.us.com:XXX.XXX.XX.XX:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:XXX.XXX.XX.XX:75957:1327428318189:104:ERROR:[SVC-GSM-WFMLRSVC-60934 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(BusinessEvent)]:java.lang.RuntimeException: Connection refused
at oracle.apps.fnd.wf.mailer.MailerUtils.isSocketConnectableNew(MailerUtils.java:300)
at oracle.apps.fnd.wf.mailer.SMTPUtils.isValidOutbound(SMTPUtils.java:522)
at oracle.apps.fnd.wf.mailer.Mailer.validateParameterValues(Mailer.java:1238)
at oracle.apps.fnd.cp.gsc.SvcComponent.performValidateParameterValues(SvcComponent.java:233)
at oracle.apps.fnd.cp.gsc.SvcComponent.start(SvcComponent.java:314)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.handleComponentEvent(SvcComponentContainer.java:2212)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(SvcComponentContainer.java:301)
at oracle.apps.fnd.wf.bes.DispatchThread.run(DispatchThread.java:57)

Cause:

Recent AutoConfig execution, without reviewing the application context variables of the Workflow Mailer.

When there is a maintenance activity like autoconfig,patching,upgrade etc which run's the autoconfig, workflow parameter SMTP (Outbound Server) is overwritten by context file parameter s_smtphost,s_smtpdomain.  If the context file parameter s_smtphost,s_smtpdomain is not pointing to correct SMTP server name(by default s_smtphost contains the application node name as value),it will be overwrite with node name which is not the correct SMTP server name.

Solution:

1. AutoConfig can modify Workflow Mailer related settings through the following context variables:

s_javamailer_imaphost: Oracle Workflow Java Mailer IMAP Host.
s_javamailer_imapdomainname: Oracle Workflow Java Mailer domain.
s_wf_admin_role: Role name of the Oracle Workflow Administrator.
s_javamailer_reply_to: Oracle Workflow Java Mailer IMAP ReplyTo Address.
s_javamailer_imap_user: Oracle Workflow Java Mailer IMAP User Name.
s_javamailer_outbound_user: Oracle Workflow Java Mailer User Name to Authenticate Oracle Workflow Mailer against SMTP Server
s_smtphost: SMTP Server Host.
s_smtpdomainname: Fully Qualified Domain Name for the Email Server.
Please review the current settings of the above variables in the application context file, and set them properly, then re-run AutoConfig, and retest the issue.  To Avoid this issue happening again in future ie whenever maintenance activity like autoconfig,patching,upgrade etc,please update below context parameter with correct SMTP server name on all the application node context file.In 11i the context file will be $APPL_TOP/admin/<SID>_hostname.xml and in R12 $INST_TOP/appl/admin/<SID>_hostname.xml.

2. If the SMTP (Outbound) Server name in the mailer configuration page is incorrect, please correct SMTP servername with the steps below and retest the issue:

1. Go into Oracle Application Manager as the SYSADMIN user.
2. In the upper right corner of the screen next to "Navigate to", please select "Workflow Manager" from the drop down and select "Go".
3. Then in the lower left corner of the screen under Related Links, select "Service Components".
4. Select "Workflow Notification Mailer" and click on "Edit".
5. Enter the correct Outbound(SMTP) Server name and Apply the changes.
6. Bounce the mailer and retest.

Reference metalink Doc ID 1565672.1

Short Names or Naming Convention in RAC

Short Names or Naming Convention in RAC

RAC (Real Application Clusters) Is Oracle Implementation of Clustered Database Instances.

CRS (Cluster Ready Services) Oracle's own clusterware tightly coupled with Oracle Real Application Clusters (RAC).

OCSF (Oracle Cluster File System) Oracle File system Implementation For Linux.
PCP (Parallel Concurrent Processing) PCP allows concurrent processing activities to be distributed across multiple nodes in an RAC environment, maximizing throughput and providing resilience to node failure.

OCR (Oracle Cluster Registry) The OCR contains cluster and database configuration information for RAC and Cluster Ready Services (CRS).

ASM (Automatic Storage Management) Simplifies database administration by enabling you to create disk groups and manage them instead of individual datafiles.

SSH (Secure Shell) SSH is a set of standards and an associated network protocol that allows establishing a secure channel between a local and a remote computer.

RSH (Remote Shell) rsh (remote shell) is a command line computer program which can execute shell commands as another user, and on another computer across a computer network.

DSA (Digital Signature Algorithm) The Digital Signature Algorithm (DSA) is a United States Federal Government standard or FIPS for digital signatures.

SCSI (Small Computer System Interface) standard interface and command set for transferring data between devices commonly used for interfaces for hard disks, and USB and FireWire connections for external devices.

NAS (Network-attached storage) Is a dedicated data storage technology that can be connected directly to a computer network to provide centralized data access and storage to heterogeneous network clients.

NFS (Network Filesystem) is a protocol allows a user on a client computer to access files over a network as easily as if attached to its local disks.

OSCP (Oracle Storage Compatibility Program) An Oracle Programe To assist third-parties with their Oracle compatibility with respect to storage devices.
DTP (Distributed Transaction Processing) DTP model is the synchronization of any commits and rollbacks that are required to complete a distributed transaction request.

VIP (virtual IP Address) is an IP address that is not connected to a specific computer or network interface card (NIC) on a computer.

TCP/IP ( Transmission Control Protocol (TCP) and the Internet Protocol (IP)
DNS (Domain Name Server) The domain name system is responsible for translating it translates domain names (computer hostnames) to IP addresses.

MAC (Media Access Control) Media Access Control address (MAC address) is a unique identifier attached to most network adapters.

JDBC (Java Database Connectivity) A Java API that allows independent connectivity between the Java programming language and a wide range of databases.

LB (Load Balancing) Is a technique to balance the load between configured servers.
FAN (Fast Application Notification) enables end-to-end, lights-out recovery of applications and load balancing when a cluster configuration changes.

FCF (Fast Connection Failover) Fast Connection Failover provides the ability to failover connections in the connection cache as quickly and efficiently as the database itself.

TAF (Transparent Application Failover) Using transparent application recovery (TAF), Oracle can automatically reconnect users to the database in the case of instance or node failure.

OCI (Oracle Call Interface) OCI allows you to develop applications that take advantage of the capabilites of SQL from within the application.

ODP (Oracle Data Provider) ODP.NET features optimized data access to the Oracle database from a .NET environment.

ONS (Oracle Names Server) Oracle Names makes network address and database link information available to all nodes throughout the network.
ORION (Oracle I/O) ORION is a test tool freely available tool which simulates Oracle I/O.

GSD (Global Services Daemon) The Global Services Daemon (GSD) background process allows usage of Oracle Enterprise Manager or the SRVCTL utility to perform system management tasks.

NTP (Network Time Protocol) The Network Time Protocol (NTP) is a protocol for synchronizing the clocks of computer.

CVU (Cluster Verification Utility) CVU is used to verify all the important components that need to be verified at different stages in a RAC environment.

VSD (Virtual Shared Disk) A Virtual Shared Disk (VSD) facility is a layer of software that allows a processing node to access disk devices physically attached to a remote node as if those devices were attached locally.

LPARs (Logical Partitions) a Logical Partition, commonly called an LPAR, is a virtualized computing environment abstracted from all physical devices.

DBCA (Oracle Database Configuration Assistant) An Oracle utility that facilitates the creation and configuration of a database.

ORACM (Oracle Cluster Manager) allows to add new nodes to an existing cluster without having to stop/start the whole cluster.

RDA (Remote Diagnostic Agent) An Oracle Diagnostic Tool used in troubleshooting.

GFS (Global Filesystem) GFS allows a cluster of Linux servers to share data in a common pool of storage.

NIC (Network Interface Controller) is a piece of computer hardware designed to allow computers to communicate over a computer network.

LVM (Logical Volume Manager)

GPFS (General Parrel File System) GPFS is IBM’s high-performance parallel, scalable file system for IBM UNIX clusters.

HA (High Avaliblity)

MAA (Maximum Availability Architecture)

RACDDT (RAC Data Collection Tool) is a data collection tool designed and configured specifically for gathering diagnostic data related to Oracle's Real Application Cluster (RAC) technology.

OSW (OSWatcher) is an operating system diagnostic utility tool that gathers archival performance data using various native Unix utilities, such as vmstat, iostat and top.

OLS (Oracle Label Security) Oracle Label Security is developed based on virtual private database

(VPD) technology and provides a flexible, fine-grained access control functionality that is achieved by comparing a sensitive label assigned to a piece of data with label authorizations assigned to an application user.

RSM (Remote Shared Memory) is a feature that bypasses the UDP/IP communication in Solaris.

UDP/IP (User Datagram Protocol) can send short messages sometimes known as datagrams to one another.

TNS (Transparent Network Substrate) Allows peer-to-peer connectivity where no machine-level connectivity can occur.

EMCA (Enterprise Manager Configuration Assistant) An Oracle tool to set up the Enterprise Manager.

OPS (Oracle Parallel Server) The OPS option allows multiple instances on different computer systems (nodes) can access the same database files simultaneously.

SRVCTL (Server Control) A utility to assist in administration and maintenance of RAC databases.

Current Workflow Mailer Logfile Location

Current Workflow Mailer Logfile Location

select fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;

Upgrade Oracle Application E Business Suite 12.0.X to 12.1.1

Upgrade Oracle Application E Business Suite 12.0.X to 12.1.1

Includes Upgrade of Database, Complete Middle Tier and Java Upgrade with Application TOP

This includes Challenges and issues and fixes during the process of upgrade.
This document refers to Redhat Enterprise Linux 4 update 5., Database 10.2.0.2, Forms server version 10.1.2.0.1 and 10g AS Web server 10.1.3

High Level Steps:

Step 1: Process of Database Upgrade from (10.2.0.2 to 10.2.0.4)
Step 2: (Upgrade 10gAS 10.1.3 to 10.1.3.4)
Step 3: Upgrading the Java JDK
Step 4: Forms server Upgrade from 10.1.2.0.1 to 10.1.2.3 is to be done.
Step 5:  upgrading to 12.1.1

Take a backup of the production before doing (Strong Recommendation)
Metalink Document to refer: 752619.1 (But many challenges ahead if you follow the document)
The as-is server is being 12.0.5 and database is of 10.2.0.2, we upgraded the Database to 10.2.0.4 using Patchset 6810189

Step 1: Process of Database Upgrade:

1. Unzip the p6810189_10204_Linux-x86.zip to a temporary directory which is obtained from metalink.
2. Shut down all the application services, DB, listener, isqlplus and emctl dbconsole.
Please do remember, Take a backup of the database and inventory.
3. Go to unzipped directory and run ./runInstaller
$./runInstaller
Once completed, startup the database using
$sqlplus / as sysdba
SQL> startup upgrade;
SQL> spool 'upgrade10204.log'
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
At the end the script will return the results of upgrade validity. Verify the validity of the database after upgrade.
SQL> shut immediate;
SQL> Startup;
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
This will compile all the invalid objects that are added and not compiled during upgrade.
After co-mpilation:
Shutdown the instance and apply the additional RDBMS Patches using Opatch for E Business Suite upgrade.
Please be sure to have OPatch in the PATH
To Include OPatch in the path use
$export PATH=$PATH:$ORACLE_HOME/OPatch
Using Opatch Apply the following Patches
4247037
6600051
6870937
6991626

Step 2: (Upgrade 10gAS 10.1.3 to 10.1.3.4)

Important:
1.Take a backup of inventory before proceeding.
2. Source the environment file from $INST_TOP/ora/10.1.3/
$. PROD_appsmachine.env
3. Verify echo $ORACLE_HOME as $ORACLE_APPS_BASE/apps/tech_st/10.1.3
$ export ORACLE_HOME=/u01/oracle/PROD/apps/tech_st/10.1.3
4. Ensure that all the OC4J services are up and running.
1. Unzip and Apply Patch 7272722 obtained from metalink using ./runInstaller
If you are using local inventory please use
./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc
During the process, the OPMN services will be down and started up by the OUI.
When prompted please give the OC4J admin password as either 'secret' or 'opmn' (As I upgraded two instance, one (12.0.6) was successful with 'secret' and another (12.0.5) by 'opmn' or ' oafm' )
This will complete the 10gAS (10.1.3.0.1) to be upgraded with 10.1.3.4
Time taken for this procedure ~ 1 hour.
Shutdown the services, Run Autoconfig on the apps Tier.
-----------------------------------------------------------------------------------------------------------
As per the metalink document, dont upgrade Opatch version to 1.0.0.0.62, it failed two times for me during the other patches. I advise you to keep the current Opatch as opatch tool.
Be sure, the path of the Opatch is present in the PATH variable.
Apply the following patches:
(if you are using local inventory, then use
Patch_Directory$opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc)
1. 6702510 (The issue is that.. being a general platform Patch, Opatch will fail at the verification of platform)
use $export OPATCH_PLATFORM_ID=46,
then rerun the opatch apply.
2. Apply 6771776 (If you are getting error during opatch as Platform as 196 then use OPATCH_PLATFORM_ID = 46)
3. 7411481
4. 7139320
5. 7139339
Post Upgrade steps
Source Application environment file
Regenerate appsborg.zip and appsborg2.zip using adadmin
Navigation: adadmin -> Choose Option 1-> Regenerate Product JAR files -> Force Option: No
Run autoconfig in application tier system.
Verify the Upgrade using:
either:
use ./runInstaller and then select installed products, it will show you the components from WEB_OH that are all upgraded to 10.1.3.4
or:
use opatch lsinventory -detail
Look for Oracle Application Server Patchset as 10.1.3.4 as follows:
Installed Patch List:
===================== 1) Patch 7139339 applied on Fri Jul 17 20:03:46 IST 2009 [ Base Bug(s): 7139339 ]
2) Patch 7139320 applied on Fri Jul 17 20:02:23 IST 2009 [ Base Bug(s): 7139320 ]
3) Patch 7411481 applied on Fri Jul 17 20:00:42 IST 2009 [ Base Bug(s): 7411481 ]
4) Patch 6771776 applied on Fri Jul 17 19:58:55 IST 2009 [ Base Bug(s): 6771776 ]
5) Patch 6702510 applied on Fri Jul 17 19:51:41 IST 2009 [ Base Bug(s): 6702510 ]
6) Patch 5586892 applied on Sat Dec 30 08:18:37 IST 2006
[ Base Bug(s): 5586892 5126270 4689959 5238255 ]
Verification from Opatch lsinventory –detail
oracle@xyz.com 7139339]$ opatch lsinventory -detail
Oracle Interim Patch Installer version 1.0.0.0.56
Copyright (c) 2006 Oracle Corporation. All Rights Reserved..
We recommend you refer to the OPatch documentation underOPatch/docs for usage reference.We also recommend usingthe latest OPatch version. For the latest OPatch versionand other support related issues, please refer to document293369.1 which is viewable from metalink.oracle.com
Oracle Home:/oracle/ACE/apps/tech_st/10.1.3Oracle Home Inventory:/oracle/ACE/apps/tech_st/10.1.3/inventoryCentral Inventory:/oracle/ACE/inst/apps/ACE_samarth/admin/oraInventory from:/oracle/ACE/apps/tech_st/10.1.3/oraInst.locOUI location:/oracle/ACE/apps/tech_st/10.1.3/ouiOUI shared library:/oracle/ACE/apps/tech_st/10.1.3/oui/lib/linux/liboraInstaller.soJava location:/oracle/ACE/apps/tech_st/10.1.3/jre/1.4.2/bin/javaLog file location:/oracle/ACE/apps/tech_st/10.1.3/.patch_storage//*.log
Creating log file "/oracle/ACE/apps/tech_st/10.1.3/.patch_storage/LsInventory__07-17-2009_20-17-29.log"
Result:
PRODUCT NAME VERSION
============ =======
Agent Required Support Files Patch 10.1.0.5.0
Agent Required Support Files 10.1.0.2.0
Apache Module for Oracle Distributed Authoring and Versioning 10.1.2.1.0
Assistant Common Files Patch 10.1.0.5.0
Assistant Common Files 10.1.0.2.0
Bali Share 1.1.18.0.0
DataDirect Connect JDBC Drivers 10.1.2.0.1
DBJAVA Required Support Files Patch 10.1.0.5.0
DBJAVA Required Support Files 10.1.0.2.0
Documentation Required Support Files 10.1.0.3.0
Enterprise Manager Minimal Integration Patch 10.1.0.5.0
Enterprise Manager Minimal Integration 10.1.0.2.0
Enterprise Manager plugin Common Files 10.1.0.2.0
Enterprise Manager plugin Common Files 10.1.0.5.0
Extended Windowing Toolkit 3.3.18.0.0
HTTP Server Files 1.3.31.0.0
Oracle Application Server PatchSet 10.1.3.4.0
Oracle ASkernel Common 10.1.3.0.0
Oracle Business Rules 10.1.3.0.0
.
.
.
OPatch succeeded.
Now upgrade the Opatch using 6880880 (Unzip the patch directly into the 10.1.3 Oracle Home)
Do not Start the application services and try to login to the applications using the port. As it throws login page cannot be displayed as the variable in context are pointing to portal/AppsLogin.jsp.
This will be resolved once you complete applying the patch 7303030.

Step 3: Upgrading the Java JDK

Download the latest JDK from http://java.sun.com/javase/downloads/index.jsp
download jdk-6u14-linux-i586.bin
1. Be sure to shutdown all the middle tier services.
a) Perform JDK Upgrade in IAS_ORACLE_HOME:
Navigate to $IAS_ORACLE_HOME/appsutil/
$mv jdk/ jdk_old/
$mkdir jdk/
copy the jdk-6u14-linux-i586.bin to $IAS_ORACLE_HOME/appsutil/jdk
Install the bin file using
$./jdk-6u14-linux-i586.bin
This will install latest jdk in appsutil/jdk
Copy the *.ttf files from $FND_TOP/resource to $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts.
Important: You need to do the steps as a post upgrade step if you complete with 7303030
b) Upgrade jre in $RDBMS_ORACLE_HOME
copy the jdk-6u14-linux-i586.bin to $RDBMS_ORACLE_HOME/appsutil
Source the database environment file
1. Navigate to $ORACLE_HOME/appsutil/
2. mv jre/ jre_old/
3. mkdir jre/
Install the jdk_version_linus-i586.bin to appsutil/jre
Verification of JDK installation
$ADJVAPRG -version
[oracle@prod appl]$ $ADJVAPRG -version
"1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)
echo $CLASSPATH
[oracle@prod appl]$ echo $CLASSPATH
/oracle/PRODN/apps/tech_st/10.1.3/appsutil/jdk/lib/dt.jar:
/oracle/PRODN/apps/tech_st/10.1.3/appsutil/jdk/lib/tools.jar:
/oracle/PRODN/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/rt.jar:
/oracle/PRODN/apps/apps_st/comn/java/lib/appsborg.zip:
/oracle/PRODN/apps/tech_st/10.1.2/forms/java:
/oracle/PRODN/apps/tech_st/10.1.2/forms/java/frmall.jar:/oracle/PRODN/apps/tech_st/10.1.2/jlib/ewt3.jar:/
oracle/PRODN/apps/tech_st/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/lib/frmsrv.jar:/oracle/PRODN/apps/apps_st/comn/java/classes
$AFJVAPRG -version
[oracle@prod appl]$ $AFJVAPRG -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)
echo $AF_CLASSPATH
Each should reflect the paths belonging to latest JDK like this
[IAS_ORACLE_HOME]/appsutil/jdk/lib/dt.jar, [IAS_ORACLE_HOME]/appsutil/jdk/lib/tools.jar, [IAS_ORACLE_HOME]/appsutil/jdk/jre/lib/rt.jar, and [IAS_ORACLE_HOME]/appsutil/jdk/jre/lib/charsets.jar
Method:1
Create an jsp file under $OA_HTML like jdktest.jsp
The JDK version is: <%= System.getProperty("java.version") %>
compile the jdktest.jsp using ojspcompile, syntax is:
perl -x $FND_TOP/patch/115/bin/ojspCompile.pl --compile -s jdktest.jsp'
Dont check it now, as your middle tier services are down.  
Check with Database Tier:
$cd $ORACLE_HOME/appsutil/jre/bin
./java -version
[oracle@prod jre]$ cd bin
[oracle@prod bin]$
./java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)

Run Autoconfig on both tiers

Step 4: Forms server Upgrade

In this Step upgradation of Forms server from 10.1.2.0.1 to 10.1.2.3 is to be done.
Backup the inventory file.
Source the application environment file
1. Unzip 5983622 and run ./runInstall
2. Supply oc4jadmin as 'secret' when prompted.
(Note here configuration assistant will fail at oc4j configuration Assistant, Simply skip and click next. This is normal behavior for E Business Upgrade)
Once completed:
Apply the Following Patches using OPatch tool from TOOLS_ORACLE_HOME

Error was: OPatch failed with error code 73

OPatch version    : 10.2.0.5.1
OUI version       : 10.1.0.6.0
=====================
p3559326_10105_LINUX.zip
p6880880_102000_LINUX.zip      ---
p4407272_10123_LINUX.zip
p7016961_101210_LINUX.zip
p4526825_10123_LINUX.zip
p7016961_10123_LINUX.zip
p5394728_10105_LINUX.zip
p5917053_10123_LINUX.zip
p7121788_10123_LINUX.zip
p5983622_10123_LINUX.zip
p7140392_10123_GENERIC.zip
p6371228_10123_LINUX.zip
p7329300_10123_LINUX.zip
p6857221_10123_LINUX.zip
p8300196_10123_GENERIC.zip
p8374931_10123_GENERIC.zip
Note: if you face the error below during any of the Opatch or ./runInstaller: rwconverter error: like this
/ACE/apps/tech_st/10.1.2/lib//librw.a -lnsl/oracle/ACE/apps/tech_st/10.1.2//lib/
/librw.so: undefined reference to `rolgxinoci_cid'
collect2: ld returned 1 exit status
make: *** [rwbuilder] Error 1
DO the following:
Restore the file
librw.so, and librw.a
from old backup to $TOOLS_ORACLE_HOME/lib and run
$TOOLS_ORACLE_HOME/reports/lib/make -f ins_reports.mk install
The result will be
/oracle/ACE/apps/tech_st/10.1.2/lib,-rpath,/oracle/ACE/apps/tech_st/10.1.2/jdk/jre/lib/i386:/lib:/usr/lib -lm `cat /oracle/ACE/apps/tech_st/10.1.2/lib/sysliblist` -ldl -lpthread -lm -L/oracle/ACE/apps/tech_st/10.1.2/lib -L/oracle/ACE/apps/tech_st/10.1.2/lib/stubs/ -lsnls10 -lpthread -ljvm -lhpi -Wl,-rpath,/usr/X11R6/lib -L/usr/X11R6/lib -lXm -lXt -lX11 -lm -lXp -lXext /oracle/ACE/apps/tech_st/10.1.2/lib//librw.a -lnslmv rwbuilder /oracle/ACE/apps/tech_st/10.1.2/bin/rwbuilderchmod 700 /oracle/ACE/apps/tech_st/10.1.2/bin/rwbuilder
then proceed with the patch, it should complete normally.
Create formsapp.ear
$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp
Enter Application name for re-deployment ? forms
Enter Oc4j Instance password for re-deployment ? welcome (or current password) error message 1
? No ">Run Autoconfig ? No
If you are prompted with oc4jadmin password is incorrect Do the following:
$INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml
$cp system-jazn-data.xml system-jazn-data-ori.xml
$vi system-jazn-data.xml
locate oc4j-admin section
[user]
[name]oc4jadmin[/name]
[display-name]OC4J Administrator[/display-name]
[guid]88836370D11611DC9F30F9C1CD6F1A73[/guid]
[description]OC4J Administrator[/description]
[credentials]{903}F+iG1A46edXG9RdfY0pD2O4Ge/qyEjsg[/credentials]
[/user]
replace
{903}F+iG1A46edXG9RdfY0pD2O4Ge/qyEjsg
with !Welcome [Please note '!' is important]
then issue oc4jadmin password as 'Welcome'. it will pass the step.
Run Autoconfig on Apps Tier
Apply Interoperability Patch 7120543
Apply OPatch 6880880 as in the previous section to upgrade Opatch tool to 1.0.0.0.62
Rebuild Forms and Reports executables
cd $ORACLE_HOME/forms/lib32
Note: if this directory does not exist: cd $ORACLE_HOME/forms/lib
$ make -f ins_forms.mk install
cd $ORACLE_HOME/reports/lib32
Note: if this directory does not exist: cd $ORACLE_HOME/reports/lib
$ make -f ins_reports.mk install
Regenerate appsborg.zip and appsborg2.zip using adadmin

Step 5:  upgrading to 12.1.1

Apply AD.B.1 Patch (7461070)
Copy adgrants.sql from patch/admin directory to $RDBMS_ORACLE_HOME/appsutil/admin directory
If it is not present create a directory /admin inside $RDBMS_ORACLE_HOME/appsutil
run adgrants.sql as sys user with syntax
sqlplus "/as sysdba" @adgrants.sql APPS
once finished,
Apply the patch AD.B.1
run d below patch as applmgr:-
cd /software/qadar_patches/7461070
adpatch
Recreate grants and synonyms using adadmin
Run adadmin -> Maintain Applications Database Entities menu ->
Re-create grants and synonyms for APPS schema.

Apply the Main patch 7303030 (Contains 8 parts)
During the patch run, please expect
XDOLoader.class will fail on user xdo
Follow the procedure from http://balajiabhi.blogspot.com/2009/07/rebuilding-xdo-objects-failed-worker-on.html
(Completed within 15 hours)
Apply online Help patch 7303031 using adpatch. (It takes 28 minutes)
Apply the product specific patches as per the metalink document stated above.
Start the application services using adstrtal.sh from $ADMIN_SCRIPT_HOME
Verify the forms version using 'About Applications' from Forms session.
It should show the version as follows

To get other versions, enable about this page link in LOGINPAGE of the server
To do this please do the following:
set the profile option FND: Diagnostics = Yes
Then bounce the middle tier services, you will see a link 'About This Page' at the bottom of the web page

The output of the About this page will give all the particulars about the version upgrade.

Oracle -RMAN Block Change Tracking

Oracle -RMAN Block Change Tracking

With Oracle 10gr1 Enterprise Edition Oracle introduce Block change tracking feature which use to FAST / Speed up RMAN Incremental Backup.

What is BLOCK CHANGE TRACKING in RMAN:

RMAN's change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.

After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

Source: Oracle documentation

1. Enable Block Change tracking and set location

Before enabling we must to set/specify block change tracking location.There is two way to specify block change tracking location1. using DB_CREATE_FILE_DEST parameter which use OMF file name.

SQL> alter system set db_create_file_dest='location' SCOPE=BOTH;

SQL> alter database enable block change tracking;

2. Manually specify location for the block change tracking

SQL>alter database enable block change tracking using file 'location';

2. Disable block change tracking

SQL> alter database disable block change tracking;

NOTE: BCT files deleted automatically by oracle when BCT is disabled.

3. Reset the location of Block change tracking file

-There are two options for this

1. shutdown the database and set the new location

SQL> shutdown database

SQL> move BCT file to new location

SQL> startup mount

SQL> alter database rename file 'old_location' TO 'new_location';

SQL> alter database open;
or
2. disable the block change tracking / set the new location / enable BCT

SQL> alter database DISABLE block change tracking;

SQL> alter database enable block change tracking using FILE 'NEW_LOCATION';

After that RMAN use new location for the BCT.

4. Checking the information about block change tracking enable or disable

Check v$BLOCK_CHANGE_TRACKING view

5. BCT file is important for restore and recovery of database.

Answer: No, it is doesn't require for Database Recovery of database

-What happen if BCT file is lost and corrupted.

That is very interesting case here, suppose oracle found if BCT file is corrupted or missing then oracle will automatically recreate new BCT file.

Let see some practical...

1. Enable BCT

SQL> alter database enable block change tracking using file 'e:\bct.dbf';
Database altered.

2. Check the status and default size.

SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES
--------------------------------- ----------
ENABLED E:\BCT.DBF 11599872

NOTE: Filesize created is 11 mb by default.

3. Now check alertsid log file what entries we found.

alter database enable block change tracking using file 'e:\bct.dbf'

Block change tracking file is current.

Starting background process CTWR

Mon Jul 28 20:06:54 2008

CTWR started with pid=20, OS id=464

Block change tracking service is active.

Completed: alter database enable block change tracking using file 'e:\bct.dbf'

4. Disable BCT

SQL> alter database disable block change tracking;
Database altered.

SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES
---------- ---------------------------------------- ----------
DISABLED

5. Check alertlog file

Mon Jul 28 20:12:41 2008

alter database disable block change tracking

Mon Jul 28 20:12:41 2008

Block change tracking service stopping.

Stopping background process CTWR

Deleted file E:\BCT.DBF

Completed: alter database disable block change tracking

6. Suppose BCT file lost or missing then what oracle will do...

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host del c:\bct.dbf

SQL> startup
ORACLE instance started.
Database mounted.
Database opened.

Now check the alertlog file for more information about how oracle create new BCT file when missing or lost.

Mon Jul 28 20:16:54 2008

ALTER DATABASE OPEN

CHANGE TRACKING is enabled for this database, but the change tracking file can not be found. Recreating the file.

Change tracking file recreated.

Block change tracking file is current.

adoafmctl.sh: exiting with status 204

adoafmctl.sh: exiting with status 204

While changing SERVER IP address in Oracle application r12 on linux 5.
changed IP address on both server ( we have two server 1. db server 2. application server)

after changing the server IP when we are trying to start application service, application service ended with below error message:
adoafmctl.sh: exiting with status 204

Cause: Server IP address change

Solution:

-logon to application server with applmgr user
-go to $INST_TOP
-stop application services ($ cd $INST_TOP/admin/scripts )
-delete following files
rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*

Execute autoconfig on application tier
$cd $INST_TOP/admin/scripts
$. ./autoconfig {hit enter}

-start all application services
-re-test the issue

adapcctl.sh: exiting with status 150

adapcctl.sh: exiting with status 150

After resolve adoafmctl.sh: exiting with status 204 when i try to start application tier services then getting below error message:
adapcctl.sh: exiting with status 150

Cause:
There is a states file called .opmndat at /oracle/apps/VIS/inst/apps//ora/10.1.3/opmn/logs/states which gets created when services are started.
Whenever services are started this file gets created or updated.
Unfortunately for some reason this file is not updated when services are started.

Solution:
1. shutdown all application services on application tier (applmgr) user
2. nevigate to $INST_TOP/ora/10.1.3/opmn/logs/states directory
3. delete this file .opmndat ( this is hidden file)
#rm .opmndat
4. restart all application services
5. re-test issue

Audit Database

Audit Database

Types of Auditing

1.Standard Auditing
2.FGA (Fine Grained Auditing)
3.SYS ( sysdba or sysoper)

1.Standard Auditing:

DBA_AUDIT_EXISTS
lists audit trail entries produced by AUDIT NOT EXISTS.

DBA_AUDIT_OBJECT
contains audit trail records for all objects in the system.

DBA_AUDIT_SESSION
lists all audit trail records concerning CONNECT and DISCONNECT.

DBA_AUDIT_STATEMENT
lists audit trail records concerning GRANT, REVOKE,AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database.

DBA_AUDIT_TRAIL or sys.aud$
lists all audit trail entries.

DBA_OBJ_AUDIT_OPTS
describes auditing options on all objects.

DBA_PRIV_AUDIT_OPTS
describes current system privileges being audited across the system and by user.

DBA_STMT_AUDIT_OPTS
describes current system auditing options across the system and by user.


Fine-Grained Auditing:

DBA_FGA_AUDIT_TRAIL or sys.fga_log$

Audit Example.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> --add AUDIT_TRIAL=TRUE parameter in INIT.ora file
SQL> create spfile from pfile;

File created.

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string TRUE


SYS ( sysdba or sysoper) Auditing:

-Set AUDIT_SYS_OPERATIONS = TRUE for every successful statement from SYS ( sysdba or sysoper) audited.
-All audit records for SYS are written to the operating system file that contains the audit trail.
-On Windows
audit records are written as events to the Event Viewer log file

-On Solaris
AUDIT_FILE_DEST parameter is not specified, the default location is $ORACLE_HOME/rdbms/audit.
AUDIT_FILE_DEST is not supported in Windows
audit file name (unix) is named after the "session", like ora-20043.aud

1.Statement Auditing
DDL statements. As an example, AUDIT TABLE audits all CREATE and DROP TABLE statements. SQL> truncate table aud$;

Table truncated.

1.audit table by access;
BOTH successful or not successful statement

2.audit table by access whenever successful;
SUCCESSFUL statement only

3.audit table by access whenever not successful;
NOT SUCCESSFUL statement only

4.audit table by SESSION;
Oracle to write a single record for all SQL statements of the same type issued in the same session.

5.audit table by ACCESS;
Oracle to write one record for each access.

6.audit select any table by access;
all statements issued by users with the SELECT ANY TABLE privilege are audited.

7.audit [select any table / table ] by USER/SCHEMA;
specific user/schema audit.

8.audit session;
To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, BY SESSION

9.audit delete any table;

10.audit update any table;

11.audit select table, insert table, delete table, execute prodedure
by access whenever not successful;

12.audit session;

13.audit connect;
Note : there is no DIFFERENCE between CONNECT or SESSION auditing.

Disable Auditing

1.noaudit table;
2.noaudit alter table;
3.noaudit select any table;
4.noaudit session;

Note : don't use BY SESSION / BY ACCESS with noaudit command.

5.NOAUDIT ALL PRIVILEGES;
turns off all privilege audit options

6.NOAUDIT ALL;
turns off all statement audit options
Disable Standard Audit
set AUDIT_TRAIL=false

SQL> audit table by access;
Audit successed.

SQL> conn scott/tiger
Connected.
SQL> create table nolog ( no number);

Table created.

SQL> drop table nolog purge;

Table dropped.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username, action_name from dba_audit_trail;

USERNAME ACTION_NAME
------------------------------ ----------------------------
SCOTT CREATE TABLE
SCOTT DROP TABLE

SQL> audit alter table by access;

SQL> alter table titi1 add ( name1 varchar2(20));

Table altered.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username, action_name from dba_audit_trail;

USERNAME ACTION_NAME
------------------------------ ----------------------------
SCOTT CREATE TABLE
SCOTT DROP TABLE
SCOTT ALTER TABLE
SCOTT ALTER TABLE
DML statements. As an example, AUDIT SELECT TABLE audits all SELECT ... FROM TABLE/VIEW statements, regardless of the table or view.
SQL> audit select table by access;

Audit succeeded.

SQL> conn scott/tiger;
Connected.
SQL> select count(*) from emp;

COUNT(*)
----------
14

1 row selected.

SQL> select count(*) titi1;
select count(*) titi1
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> column obj_name format a20
SQL> select username, action_name,obj_name from dba_audit_trail;
USERNAME ACTION_NAME OBJ_NAME
------------------------------ ---------------------------- --------------------
SCOTT SELECT EMP

Purging Audit Records from the Audit Trail

conn sys as sysdba
password :

Delete from SYS.AUD$;
delete all audit records

Deleting the Audit Trail Views
If auditing is DISABLE and no longer need then audit trail views.
1.conn SYS user
2.run CATNOAUD.SQL scripts

Trace Error Number.

SQL> select returncode from dba_audit_trail where rownum <= 1 and returncode <>
0;

RETURNCODE
----------
2004

1 row selected.

SQL> exec dbms_output.put_line ( sqlerrm(-2004));
ORA-02004: security violation

PL/SQL procedure successfully completed.

what are all the audit options enabled?
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS

Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.

SQL> select count(*) from STMT_AUDIT_OPTION_MAP;

COUNT(*)
----------
183

Administrative Privileges

Administrative Privileges

Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER

Note:
The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself.
The SYSDBA and SYSOPER privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion. For example, you if you have the SYSDBA privilege, you can connect to the database by specifying CONNECT AS SYSDBA.

SYSOPER Privileges

1.Perform STARTUP and SHUTDOWN operations

2.CREATE SPFILE

3.ALTER DATABASE OPEN/MOUNT/BACKUP

4.ALTER DATABASE ARCHIVELOG

5.ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)

6.Includes the RESTRICTED SESSION privilege

This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.

SYSDBA Privileges

1.Perform STARTUP and SHUTDOWN operations

2.ALTER DATABASE: open, mount, back up, or change character set

3.CREATE DATABASE

4.DROP DATABASE

5.CREATE SPFILE

6.ALTER DATABASE ARCHIVELOG

7.ALTER DATABASE RECOVER

8.Includes the RESTRICTED SESSION privilege

Effectively, this system privilege allows a user to connect as user SYS.

When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.

SQL> conn scott/tiger
Connected.
SQL> drop table admin_test purge;

Table dropped.

SQL> create table admin_test ( no number);

Table created.

SQL> conn scott/tiger as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select * from admin_test;
select * from admin_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant SYSOPER to scott;

Grant succeeded.

SQL> conn scott/tiger as sysoper
Connected.
SQL> select * from admin_test;
select * from admin_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> show user
USER is "PUBLIC"

For connecting SYSDBA privs to database any user need

1. SQLNET.AUTHENTICATION_SERVICES= (NTS) in sqlnet.ora
2. OS user is member of ORA_DBA group
3. Explicit granted SYSDBA role to user

For connection SYSOPER privs to database any user need

1. Explicit granted SYSOPER role to user

REMOTE_LOGIN_ PASSWORDFILE

REMOTE_LOGIN_ PASSWORDFILE

remote_login_passwordfile parameter is use for Password Authentication Method.

There are three value for remote_login_passwordfile

1. None

With this setting "NONE" oracle behave passwordfile doesn't exists.

2. Exclusize ===>Default value.

An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

3. Shared

A SHARED password file can be used by multiple databases running on the same server, or multiple instances of a Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

remote_login_passwordfile ====> is non modifiable parameters

this parameter is non modifiable. mean you have to restart oracle server to make any changes.

1. alter system set remote_login_passwordfile = [value]
2. shutdown
3. startup
or
1. shutdown
2. edit pfile and make changes in remote_login_passwordfile
3. create spfile from pfile
4. startup

OS Authentication

OS Authentication

-Once authenticated by the operating system, users can connect to Oracle more conveniently, without specifying a user name or password. For example, an operating-system-authenticated user can invoke SQL*Plus and skip the user name and password prompts by entering the following:
SQLPLUS /

-With control over user authentication centralized in the operating system, Oracle need not store or manage user passwords, though it still maintains user names in the database.

-Audit trails in the database and operating system use the same user names.

Note: When an operating system is used to authenticate database users, managing distributed database environments and database links requires special care.

OS Authentication Configuration Steps

1. Create OS user
2. Set below parameters.
alter system set os_authent_prefix = ‘OPS$’ scope=spfile;
Note: Default parameter value is “OPS$”

If OS user is member of domain group then also set below parameter.

alter system set remote_os_authent=TRUE scope=spfile;
Note: Default parameter value is FALSE,

3. Edit sqlnet.ora file and set below line to NTS
Sqlnet.authentication_services=(NTS)

4. Bounce database
shutdown immediate
startup

5. Create Oracle User for OS authentication

First check OS username
select sys_context(‘USERENV’,’OS_USER’) from dual;

create user “OPS$USERNAME” identified by EXTERNALLY;

Note: Oracle username is must be same as OS user name and must be started with “OPS$”.

6. Test to connect through OS authentication

sqlplus /