Recompiling Invalid Objects in 11gR2

Operations such as upgrades, patches and DDL changes can invalidate schema objects.
 Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation,
 but this can take an unacceptable time to complete, especially where complex dependencies are present.
For this reason it makes sense to recompile invalid objects in advance of user calls.
It also allows you to identify if any changes have broken your code base.

This article presents several methods for recompiling invalid schema objects.

Identifying Invalid Objects
The Manual Approach
Custom Script
DBMS_UTILITY.compile_schema
UTL_RECOMP
utlrp.sql and utlprp.sql

Identifying Invalid Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query.

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.

The Manual Approach

For small numbers of objects you may decide that a manual recompilation is sufficient.
The following example shows the compile syntax for several object types.

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification,
with the addition of the word "BODY" at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations.

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
This method is limited to PL/SQL objects, so it is not applicable for views.

Custom Script

In some situations you may have to compile many invalid objects in one go.
One approach is to write a custom script to identify and compile the invalid objects.
The following example identifies and recompiles invalid packages and package bodies.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
            '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/
This approach is fine if you have a specific task in mind,
but be aware that you may end up compiling some objects
multiple times depending on the order they are compiled in.
It is probably a better idea to use one of the methods provided by Oracle
since they take the code dependencies into account.

DBMS_UTILITY.compile_schema

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures,
functions, packages, and triggers in the specified schema.

The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects.
As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time,
while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads.

Their definitions are listed below.

 PROCEDURE RECOMP_SERIAL(
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
   threads  IN   PLS_INTEGER DEFAULT NULL,
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);
The usage notes for the parameters are listed below.

schema - The schema whose invalid objects are to be recompiled.
If NULL all invalid objects in the database are recompiled.
threads - The number of threads used in a parallel operation.
If NULL the value of the "job_queue_processes" parameter is used.
Matching the number of available CPUs is generally a good starting point for this value.

flags - Used for internal diagnostics and testing only.

The following examples show how these procedures are used.

 Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');

There are a number of restrictions associated with the use of this package including:

Parallel execution is performed using the job queue. All existing jobs are marked as disabled until the operation is complete.

The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.

Runnig DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database.
They are typically run after major database changes such as upgrades or patches.
They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package.

The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0".
The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

How To reset Password in 11GR2

Oracle 11gR2 introduces Case-sensitive passwords for database authentication.
Along with this if you wish to change the password (temporarily) and reset it back to old ,
you will find that password field in dba_users is empty.
Prior to 11g we could use following technique to change/restore password

SQL> create user sujeet identified by patna123;

User created.

SQL> grant create session to sujeet;

Grant succeeded.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username,password from dba_users where username='sujeet';

USERNAME                       PASSWORD
------------------------------ ------------------------------
sujeet                           8DEC0D889E8E9A6B

SQL> alter user sujeet identified by patna123;

User altered.

SQL> conn amit/patna123
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user sujeet identified by values '8DEC0D889E8E9A6B';

User altered.

SQL> conn sujeet/patna123
Connected.
In 11g if you query password field, it will return NULL.

SQL> select username,password from dba_users where username='AMIT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
sujeet

Let's first see Case-sensitive password feature in 11g and then steps to change/restore passwords

SQL> create user sujeet identified by patna123;

User created.

SQL> grant connect,resource to sujeet;

Grant succeeded.

SQL> conn sujeet/Patna123
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn sujeet/patna
Connected.
This behavior is controlled by "sec_case_sensitive_logon" initialization paramter.
If the value is true then it will enforce case sensitive passwords

SQL> select NAME,VALUE from V$SPPARAMETER where NAME='sec_case_sensitive_logon';

NAME                                     VALUE
---------------------------------------- --------------------
sec_case_sensitive_logon                 TRUE

SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn sujeet/PATNA123
Connected.
SQL> conn / as sysdba
Connected.

SQL> alter system set sec_case_sensitive_logon=true;

System altered.

SQL> conn sujeet/PATNA123
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn sujeet/patna123
Connected.

Now to reset the password in 11g, we need to query spare4 column in user$ table

SQL> select spare4 from user$ where name='SUJEET';

SPARE4
--------------------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL> alter user sujeet identified by patna321;

User altered.

SQL> conn sujeet/patna321
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user amit identified by values 'S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB';

User altered.

SQL> conn sujeet/patna32
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.


SQL> conn sujeet/patna321
Connected.

As per Metalink Note 429465.1 , view DBA_USERS has new column PASSWORD_VERSIONS rendered as follows:

decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL)

For example:

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where rownum <5;

USERNAME                       PASSWORD
------------------------------ --------
SYS                            10G 11G
SYSTEM                         10G 11G
OUTLN                          10G 11G
DIP                            10G 11G
In this case it means both old and new-style hash values are available for the users,
 the new hash value is stored in the USER$.SPARE4 column,
as long as this remains NULL it means the password has not been
changed since the migration and the user will have the old case insensitive password.

SQL> create user test identified by test;

User created.

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where username in ('sujeet','TEST');

USERNAME                       PASSWORD
------------------------------ --------
sujeet                           11G
TEST                           10G 11G

As I had reset password using only spare4 string,
password will be case -sensitive irrespective of setting for sec_case_sensitive_logon
 parameter value. i.e why we see value of "11G"  for user sujeet

Update

When resetting the password, we need to also query password column from user$ column
if we wish to use case-insensitive feature in future. i.e
In my above example I used only spare4 column value to reset the password.
Now if I set sec_case_sensitive_logon=false , I will not be able to connect.

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn sujeet/patna321
ERROR:
ORA-01017: invalid username/password; logon denied
In case we wish to use both, we need to set identified by values
 ‘S:spare4;password’. As I didnot use password field while resetting,
I find that password field in user$ is empty. To correct it,
 I had to change the password again.

SQL> select password,spare4 from user$ where name='SUJEET';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
                               S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL>  alter system set sec_case_sensitive_logon=true;

System altered.

SQL> alter user amit identified by SUJEET;

User altered.

SQL> select password,spare4 from user$ where name='SUJEET';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

So to reset the password, following needs to be used.

SQL> select password,spare4 from user$ where name='SUJEET';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

SQL> alter user sujeet identified by values 'S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE;9DEC0D889E8E9A6B';

User altered.

ProcessorException: Unable to determine SMTP server to use: set FND_SMTP_HOST

Outpost Processot has encoutered the below error.

This error can be found in OPP logs:
----------------------------------------
[GC 14000K->8905K(20284K), 0.0074280 secs]
ProcessorException: Unable to determine SMTP server to use: set FND_SMTP_HOST
        at oracle.apps.fnd.cp.opp.EmailDeliveryProcessor.deliver(EmailDeliveryProcessor.java:66)
        at oracle.apps.fnd.cp.opp.DeliveryProcessor.process(DeliveryProcessor.java:91)
        at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:176)
----------------------------------------

Solution:

1. Log into System Administrator responsibility.

2. Navigate to Profile - System.

3. Query up the %SMTP% profiles.

4. Set the following profile values to the defined host and port:

FND:smtp Host > localhost
FND:smtp Port > 25

SMTP Authentication Feature in R12.1.3

What is my SMTP?

When you need to set an email client, one of the first questions is: What is my SMTP server?

Most server names are written in the form "smtp.domain.com" or "mail.domain.com": for instance, a Gmail account will refer to smtp.gmail.com. But that's not a unified rule, so you should pay attention and get the right SMTP parameters.

How to configure SMPT?

• Go to Workflow Manager Screen and navigate to Workflow Notification Mailer page.
• Edit the Workflow Mailer configuration, update "SMTP user" and "SMTP Password" parameters, SAVE and bounce the Workflow Service Container.

Authentication Mechanisms

Workflow Mailer supports PLAIN, LOGIN and CRAM-MD5 mechanisms with JavaMail version 1.4.

How to check

On a non-SSL enabled SMTP server, it can be checked easily:  

$ - telnet smtp.host.com 25
Trying 10.11.12.13...
Connected to smtp.host.com (10.11.12.13).
Escape character is '^]'.
220 smtp.host.com ESMTP Sendmail 8.13.8/8.13.8; Tue, 17 Apr 2012 10:11:36 -0400
EHLO smtp.host.com
250-smtp.host.com Hello my.desktop.com [12.13.14.15], pleased to meet you
250-ENHANCEDSTATUSCODES
250-PIPELINING
250-8BITMIME
250-SIZE
250-DSN
250-ETRN
250-AUTH LOGIN PLAIN
250-STARTTLS
250-DELIVERBY
250 HELP

************************************************************************************************

Verify the SMTP and IMAP Server details are correct and these components are working.

Make sure customer has set up a Mail Client that connects to the same IMAP account that the Java Mailer is using, and it is configured to send outbound emails using the same SMTP Server. Verify that you can send an email from this account and have it be received by the receiver. Verify that you can send an email to this IMAP account and that the email is received by this IMAP account.

· Following are the manual steps to verify SMTP and IMAP server configuration. The lines marked with ‘%%’ are the commands to enter. Please exclude ‘%%’ when entering the commands, ‘%%’ is there for clarification purposes. The following examples were run on a Sun Solaris platform, on other UNIX platforms, the return result may look slightly different, in any case, you will get the general idea.

Important: These tests must be run from the concurrent processing tier where the Java Mailer is running.

a) Verify SMTP Server: In this example, crmops02.us.oracle.com is the SMTP server running on the default port 25, andap102ses.us.oracle.com is the server where the Java Mailer(concurrent processing tier) runs. Wfuser is the mail account used by the Java Mailer. myemail@oracle.com is a valid email address to receive email from the Java Mailer.

%% telnet crmops02.us.oracle.com 25

Trying 144.25.76.117...

Connected to crmops02.us.oracle.com.

Escape character is '^]'.

220 crmops02.us.oracle.com ESMTP Sendmail 8.9.3 (PHNE_18546)/8.7.1; Fri, 31 Jan 2003 05:56:18 -0800 (PST)

%% EHLO ap102ses.us.oracle.com

250-crmops02.us.oracle.com Hello ap102ses.us.oracle.com [144.25.76.44] (may be forged), pleased to meet you

250-EXPN

250-VERB

250-8BITMIME

250-SIZE

250-DSN

250-ONEX

250-ETRN

250-XUSR

250 HELP

%% MAIL FROM: wfuser@crmops02.us.oracle.com

250 wfuser@crmops02.us.oracle.com... Sender ok

%% RCPT TO: myemail@oracle.com

250 myemail@oracle.com... Recipient ok

%% DATA

354 Enter mail, end with "." on a line by itself

%% Subject: Test message via CRMOPS2

%%

%% Test message body

%% .

250 FAA17833 Message accepted for delivery

%% quit

221 crmops02.us.oracle.com closing connection

Connection closed by foreign host.

Verify that myemail@oracle.com receives an email from wfuser01@crmops02.us.oracle.com. If not, then the SMTP server is not configured properly. Please contact the SMTP System Administrator to correct the problem.

b) Verify IMAP server: In this example, ap700lts.us.oracle.com is the IMAP server running on the default port 143. Each IMAP4 command must start with a number/character, and it does not matter if you use the same number/character for all the commands. Spaces are important and multiple spaces/tabs are not allowed. For each successful command completion the server ends the response by "n OK ....". If a command fails then the failure reason is printed.

%% telnet ap700lts.us.oracle.com 143

Trying 130.35.81.214...

Connected to ap700lts.us.oracle.com.

Escape character is '^]'.

* OK Domino IMAP4 Server Release 5.0.11 ready Sun, 23 Nov 2003 10:26:49 -0800

%% 1 login wfuser welcome

1 OK LOGIN completed

%% 1 select "INBOX"

* 23 EXISTS

* 0 RECENT

* OK [UIDVALIDITY 0] UIDs valid

* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)

* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags

1 OK [READ-WRITE] SELECT completed

%% 1 select "DISCARD"

* 54 EXISTS

* 0 RECENT

* OK [UIDVALIDITY 6] UIDs valid

* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)

* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags

1 OK [READ-WRITE] SELECT completed

%% 1 select "PROCESSED"

* 58 EXISTS

* 0 RECENT

* OK [UIDVALIDITY 4] UIDs valid

* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)

* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags

1 OK [READ-WRITE] SELECT completed

%% 1 logout

* BYE ap700lts.us.oracle.com IMAP4rev1 server terminating connection

1 OK LOGOUT completed

Connection closed by foreign host.

c) Patch 3265133 (included in Rollup 5.1 - patch 3409889) provides a simple commandline interface that uses native JavaMail APIs. This interface can be used to validate IMAP / SMTP connectivity from the concurrent-tier where mailer runs. Syntax can be found by running the following on the concurrent-tier after initializing the APPS environment:

Values in <...> are the values defined (or to be defined) for mailer configuration using OAM.

IMAP Server:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Dserver= -Dport=143

-Daccount= -Dpassword= -Dconnect_timeout=120

-Dfolder=INBOX oracle.apps.fnd.wf.mailer.Mailer

Example output:

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

bash-2.05$ $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Dserver=ap700lts.us.oracle.com -Dport=143

-Daccount=notesuser2@ap700lts.us.oracle.com -Dpassword=XXXXX -Dconnect_timeout=120 -Dfolder=INBOX oracle.apps.fnd.wf.mailer.Mailer

Server ap700lts.us.oracle.com at port 143 is reachable

Successfully connected to the IMAP account

Testing existense of folder:INBOX

Folder [INBOX] exists

Note: Special folders like Inbox/Trash may not get listed on some IMAP servers

Folders defined are:

INBOX

Drafts

Sent

PROCESS

DISCARD

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

Note: Email Processing (PROCESS and DISCARD Folder) should get listed like "PROCESS" and "DISCARD" in output above. If not, these folders are required to be created by connecting to the IMAP / Inbound Email account with any email client (e.g. Netscape Messenger or Microsoft Outlook). These folders should be created before they are specified in the mailer configuration within OAM. The notification mailer may not be able to access folders that were created using command line tools outside the e-mail client.

SMTP Server:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=

-Dport=25 -Daccount= -Dconnect_timeout=120

oracle.apps.fnd.wf.mailer.Mailer

Example Output:

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

bash-2.05$ $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=ap6120rt.us.oracle.com -Dport=25

-Daccount=notesuser6@ap700lts.us.oracle.com -Dconnect_timeout=120 oracle.apps.fnd.wf.mailer.Mailer

Server ap6120rt.us.oracle.com at port 25 is reachable

Successfully connected to the SMTP account

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

If above tests are successful from commandline, mailer should also be able to connect to IMAP / SMTP server. If these tests are not successful then mailer will also not be able to connect. Then it is IMAP / SMTP server issue that administrators need to take a look at.

How to find version in oracle EBSR12.1.3

How to check my jre version R12.1.3 EBS?

[applmgr@sujeet ~]$ cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version
sun_plugin_version=1.7.0_021

How to check my java version in R12.1.3 EBS?
[applmgr@sujeet ~]$ $ORACLE_HOME/jdk/bin/java -fullversion
java full version "1.4.2_14-b05"

How to check my forms version in R12.1.3 ebs?

[applmgr@sujeet ~]$ $ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version
Forms 10.1 (Form Compiler) Version 10.1.2.3.0 (Production)

How to check my Apache version in R12.1.3 EBS?

[applmgr@sujeet ~]$ $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
Server version: Oracle-Application-Server-10g/10.1.3.4.0 Oracle-HTTP-Server
Server built:   Jul  7 2008 14:58:00


How to check my pl/sql version in R12.1.3 EBS?

[applmgr@sujeet ~]$ $ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version
PL/SQL Version 10.1.0.5.0 (Production)

How to find workflow version in oracle apps R12.1.3?

SQL> select TEXT from WF_RESOURCES where
NAME='WF_VERSION';  2

TEXT
--------------------------------------------------------------------------------
2.6.0

INS-40909

 [INS-40909] Host names from multiple domains entered.


Cause: 
Node information was entered for two or more nodes that belonged to different domains.

Solution: 

Public hostname is on different domain than hostname, example: node1.us.oracle.com is hostname but node1.at.oracle.com is selected for public hostname.

OR.

Shouldn't the contents of your /etc/resolv.conf be the same?  ie they will contain the same IP for the DNS server.

If the contents of resolv.conf are the same, then it could be a firewall issue or maybe a permissions issue.  Is this configured the same on both servers?  Can you run a successful nslookup from both servers?

Given to full permission to   /etc/resolv.conf file.

I hope issue resolved..

Exadata Interview Questions

Oracle Exadata Interview Questions and Answers

What is Exadata?

Exadata is pre-configured combination of hardware and software which provides a platform to run the Oracle Database.

Exadata sizing configuation

Full Rack
Half Rack
Quater Rack
1/8th Rack

What is flash cache and how it works?

The flash cache is a hardware component configured in the exadata storage cell server which delivers high performance in read and write operations.

Primary task of smart flash cache is to hold frequently accessed data in flash cache so next time if same data required than physical read can be avoided by reading the data from flash cache.

What are the types of EHCC?

Query Low
Query High
Archive High
Archive Low

What is the purpose of spine switch?

Spine switch is used to connect or add more Exadata machine in the cluster

What is ASR?

ASR is the tool to manage the Oracle hardware. Full form of ASR is Auto Service Request.

Whenever any hardware fault occurs ASR automatically raise SR in Oracle Support and send notification to respective customer.

What are the Exadata Health check tools available?

Exacheck
sundiagtest
oswatcher
OEM 12c

What is the difference between cellcli and dcli?

Cellcli can be used on respective cell storage only.
DCLi (Distributed command Line Utility) – DCLI can be used to replicate command on multipla storage as well as DB servers.

What is the difference between wright-through and write-back flashcache mode?

writethrough –> Falshcache will be used only for reading purpose
writeback –> Flashcache will be used for both reading and writing

What are the steps to create DBFS ?

1. Create Directory
2. Create Tablespace on database which you are going to use for DBFS
3. Create user for DBFS
4. Grant required privileges to created user
5. Now connect to database with created user
6. Create dbfs filesystem by invoking dbfs_create_filesystem_advanced
7. Mount file system by starting dbfs_client

What is the difference between DBRM and IORM?

DBRM is the feature of database while IORM is the feature of storage server software.

What is smart flash cache?

Flash cache is the PCIe (Peripheral Components Interconnect Express) card which is plugged into the back end of the storage cell.

How smart scan works?

• If any query executes on database server than database server sends the extents and metadata to the storage cell.
• Smart scan will scan data blocks to identify relevant rows and columns.
• Once data identified by smart scan, it will return to database with only appropriate rows
  and columns.
• Once DB server gets the data, it will assemble returned data into result set.
• This operation will save the bandwidth as well CPUs and memory cost on database server
  as whole sql processing happens on storage server.

What are the pre-requisites to configure ASR?

Access to My Oracle Support
Internet connectivity using HTTPS
Network connectivity from ASR server to Exadata components

Which MOS ID I should refer for latest patch update?

MOS 888828.1

Which tool is used to generate initial configuration files based on customer’s data?

OEDA (Oracle Exadata Deployment Assistance)

What are the unique features of Exadata?

Smart Scan (Cell Offload)
Flash cache
EHCC (Exadata Hybrid Columnar Compression)
IORM (IO Resource Manager)
Storage Index

Which all networks available in Exadata?

Infiniband Network
ILOM and Management Network
Client/Public Network

What is client or public network in exadata?

Client or public network is used to established connectivity between database and application.

What are the steps involved for initial Exadata configuration?

• Initial network preparation
• Configure Exadata servers
• Configure Exadata software
• Configure database hosts to use Exadata
• Configure ASM and database instances
• Configure ASM disk group for Exadata

What is iDB protocol?

iDB stands for intelligent database protocol. It is a network based protocol which is responsible to
communicate between storage cell and database server.

Which processes are used by storage software?

Cellsrv – Cell Server
MS- management server
RS – Restart Server

What is LIBCELL?

Libcell stands for Library Cell which is linked with Oracle kernel. It allow oracle kernel to talk with the storage server via network based instead of operating system reads and writes.

What is checkip and what the use of it?

Checkip is the OS level script which contains IP address and hostname which will be used by Exadata in configuration phase. It checks network readiness like proper DNS configuration, it also checks there is no IP duplication in the network by pinging it which not supposed to ping initially.

Which script is used to reclaim the disk space of unused operating system?

For Linux: reclaimdisks.sh
For Solaris: reclaimdisks.pl

What should be ASM space allocation if backup performed internally?

40% storage space allocation for DATA disk group
60% storage space allocation for RECO disk group

How database server communicates to storage cell?

Database server communicates with storage cell through infiniband network.

Can I have multiple celldisk for one grid disk?

No. Celldisk can have multiple griddisk but griddisk cannot have multiple celldisk

How many FMods available on each flash card?

Four FMods (Flash Modules) are available on each flash card.

List the steps for replacing the damaged physical flash disk.

1. Identify damaged flash disk
2. Power off the cell
3. Replace flash card
4. Power on the cell
5. Verify and confirm new flash card

What is smart flash log?

Smart flash log is a temporary storage area on Exadata smart flash cache to store redoes log data.

What is smart scan offloading?

Offloading and Smart Scan are two terms that are used somewhat interchangeably. Exadata Smart
Scan offloads processing of queries from the database server to the storage server.
Processors on the Exadata Storage Server process the data on behalf of the database SQL query. Only the data requested in the query is returned to the database server.

Which parameter is used to enable and disable the smart scan?

cell_offload_processing

Can we use HCC on non-exadata environment?

No, HCC is only available data stored on Exadata storage server.

Which packaged is used by compression adviser utility?

DBMS_COMPRESSION package

What is the primary goal of storage index?

Storage indexes are a feature unique to the Exadata Database Machine whose primary goal is to reduce the amount of I/O required to service I/O requests for Exadata Smart Scan.

What is resource plan?

It is collection of plan directives that determines how database resources are to be allocated.

What is DBFS?

DBFS stands for Database File system which can be built on ASM disk group using database tablespace.

What is the purpose of infiniband spine switch?

Spine switch is used to connect multiple exadata database machines.

How to check infiniband topology?

We can verify infiniband switch topology by executing verify-topology script from one of our database server.

What is OEM?

OEM is Oracle Enterprise Manager which is centralized tool to monitor and administer systems as well software.

What is offload block filtering?

Exadata storage server filters out the blocks that are not required for the incremental backup in progress so only the blocks that are required for the backup are sent to the database.

Which command is used to monitor BCT?

SQL>select filename,status, bytes from v$block_change_tracking;

How to add memory into database server?

Power off database server
Add memory expansion into server
Power on the server

What are the major steps involved for cell server patching?

• Check and note down existing configuration of cell
• Clean up any previous patchmgr utility
• Verify that the cells meet prerequisite checks
• Patch cell server using patchmgr
• Validation updated cell

What environment is a good fit for Exadata?

Exadata was originally designed for the warehouse environment. Later it was enhanced for use in the OLTP databases as well.

What are the advantages of Exadata?

The Exadata cluster allows for consistent performance while allowing for increased throughput. As load increases on the cluster the performance remains consistent by utilizing inter-instance and intra-instance parallelism. It should not be expected that just moving to Exadata will improve performance. In most cases it will especially if the current database host is overloaded.

What is the secret behind Exadata’s higher throughput?

Exadata ships less data through the pipes between the storage and the database nodes and other nodes in the RAC cluster.Also it’s ability to do massive parallelism by running parallel processes across all the nodes in the cluster provides it much higher level of throughput.It also has much bigger pipes in the cluster using Infiniband interconnect for inter-instance data block transfers as high as 5X of fiberchannel networks.

What are the different Exadata configurations?

The Exadata Appliance configuration comes as a Full Rack, Half Rack, Quarter Rack or 1/8th rack.The Full Rack X2-2 has 6 CPU’s per node with Intel Xeon 5670 processors and a total of 8 Database Server nodes. These servers have 96GB of memory on each node. A total of 14 Storage server cells communicate with the storage and push the requested data from the storage to the compute nodes.The Half Rack has exactly half the capacity. It has 6 CPU’s per node with core Intel Xeon 5670 processors and a total of 4 Database Server nodes. It has 96GB of memory per database server node with a total of 7 Storage server cells.The Exadata is also available in the 1/8th Rack configuration.

What are the key Hardware components?

• DB Server
• Storage Server Cells
• High Speed Infiniband Switch
• Cisco Switch

What are the Key Software Features?

• Smart Scan,
• Smart Flash Cache
• Storage Index
• Exadata Hybrid Columnar Compression (EHCC)
• IORM (I/O Resource Manager)

What is a Cell and Grid Disk?

Cell and Grid Disk are a logical component of the physical Exadata storage. A cell or Exadata Storage server cell is a combination of Disk Drives put together to store user data. Each Cell Disk corresponds to a LUN (Logical Unit) which has been formatted by the Exadata Storage Server Software. Typically, each cell has 12 disk drives mapped to it.Grid Disks are created on top of Cell Disks and are presented to Oracle ASM as ASM disks. Space is allocated in chunks from the outer tracks of the Cell disk and moving inwards. One can have multiple Grid Disks per Cell disk.

What is IORM?

IORM stands for I/O Resource Manager.It manages the I/O demand based on the configuration, with the amount of resources available. It ensures that none of the I/O cells become oversubscribed with the I/O requests. This is achieved by managing the incoming requests at a consumer group level.Using IORM, you can divide the I/O bandwidth between multiple databases.To implement IORM resource groups, consumers and plans need to be created first.

What is hybrid columnar compression?

Hybrid Columnar compression, also called HCC, is a feature of Exadata which is used for compressing data at column level for a table.
It creates compression data units which consist of logical grouping of columns values typically having several data blocks in it. Each data block has data from columns for multiple rows.
This logarithm has the potential to reduce the storage used by the data and reduce disk I/O enhancing performance for the queries.
The different types of HCC compression include:

Query Low

Query High

Archive High

Archive Low

What is Flash cache?

Four 96G PCIe flash memory cards are present on each Exadata Storage Server cell which provide very fast access to the data stored on it.

This is further achieved by also provides mechanism to reduces data access latency by retrieving data from memory rather than having to access data from disk. A total flash storage of 384GB per cell is available on the Exadata appliance.

What is Smart Scan?

It is a feature of the Exadata Software which enhances the database performance many times over. It processes queries in an intelligent way, retrieving specific rows rather than the complete blocks.It applies filtering criteria at the storage level based on the selection criteria specified in the query.It also performs column projection which is a process of sending only required columns for the query back to the database host/instance.

What are the Parallelism instance parameter used in Exadata?

The parameter PARALLEL_FORCE_LOCAL can be specified at the session level for a particular job.

How do you Test performance of Exadata?

You can use the “calibrate” commands at the cellcli command line.

What are the ways to migrate onto Exadata?

Depending on the downtime allowed there are several options:

• Oracle DataGuard
• Traditional Export/Import
• Tablespace transportation
• Goldengate Replication after a data restore onto Exadata.

What types of operations does Exadata “offload”?

Some of the operations that are offloaded from the database host to the cell servers are:

• Predicate filtering
• Column project filtering
• Join processing
• Backups

What is cellcli?

This is the command line utility used to managed the cell storage.

How do you create obtain info on the Celldisks?

At the cellcli command line you can issue the “list celldisk” command.

How would you create a grid disk?

At the cellcli command you would need to issue the “create grididsk all ..” command.

What are the cellinit.ora and the cellip.ora files used for?

These files have the hostnames and the ip address of all the nodes in the cluster. They are used to run commands on remote database and cellserver nodes from a local host.
Example: cat /etc/oracle/cell/network-config/cellinit.oraipaddress1=192.168.47.21/24
$ cat /etc/oracle/cell/network-config/cellip.ora

cell=”192.168.47.21:5042″
cell=”192.168.47.22:5042″
cell=”192.168.47.23:5042″

What operating systems does Exadata support?

Exadata has traditionally run Oracle Linux OS. Recently, Solaris has also been made available on this engineered system.