Difference between 9i and 10g

Reference :
*********

http://www.dataqapps.com/difference-between-oracle-database-9i10g-and-oracle-database-10g11g/

Summary Of Differences Between oracle Database 9i and 10g.

The below Points are completely New in oracle database 10g (Not there in oracle 9i)

Automatic Storage Management(ASM).

Automatic Shared Memory Management(ASMM).

Automatic Database Diagnostic Monitor(ADDM).

Automatic Workload Repository(AWR).

Flashback Technologies.

Data Pump replaces Traditional EXP/IMP.

Automatic Checkpoint Tunning(FAST_START_MTTR_TARGET).

Automatic Undo Retention Tunning.

Introduced Default Permanent Tablespace (USERS).

Introduced SYSAUX tablespace.

Streams Technology(STREAMS POOL).

Introduced Big file Tablespace Option and Rename Tablespace Command.

Automatic SQL Tunning.

Temporary Tablespace Group and Default Temporary Tablespace.

Recovery Manager Enhancements(RMAN).

DBMS Scheduler Packages and DBMS File Transfer Packages.

Detailed Explanation of the above summary of differences  between oracle database 10g and 9i.

Automatic Storage Management(ASM).

ASM  means Automatic Storage Management to simplify the Storage of datafiles , controlfiles and  Redolog files.ASM  is the extension of OMF (oracle managed files).ASM functionality is controlled by ASM instance.It is not full database instance its just  a memory structures.

The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files.

ASM provides the following the functionalities

Manages groups of disks, called disk groups.

Manages disk redundancy within a disk group

Supports large files.

Initialization Parameters and ASM instance Creation

The initialization parameters that are of specific interest for an ASM instance are

INSTANCE_TYPE – Set to ASM or RDBMS depending on the instance type. The default is RDBMS.

DB_UNIQUE_NAME – Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.

ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.

ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.

ASM_DISKSTRING – Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.

Automatic Shared Memory Management(ASMM).

Oracle database 10g’s New features.By default sga_target  is enabled.But  always sga_max_size is greater than or equal to sga_target.So once sga_target is enabled means, no need to set the auto tuned parameters like

db_cache_size

shared_pool_size

large_pool_size

java_pool_size

So ASMM automatically readjusts the sizes of the main pools.

Automatic Database Diagnostic Monitor(ADDM).

ADDM means automatic database diagnostic monitor and is a oracle database 10g’s new features. ADDM does analysis of the database, identifies problems and their potential causes, and comes up with recommendations for fixing the problems. It can call all other advisors also.

ADDM stores the snapshot in SYSAUX tablespace.

The main features of the ADDM are as follows

ADDM runs automatically in the background process MMON whenever a snapshot of in-memory statistics is taken. ADDM does analysis of the statistics collected between two snapshots.

ADDM analysis results are written back to the workload repository for further use.

ADDM uses the new wait and time statistics model, where activities with high time consumption are analyzed on a priority basis. This is where the big impact lies.

ADDM can also be invoked manually

The Automatic Database Diagnostic Monitor (ADDM) is a new diagnosis tool that runs automatically every hour, after the AWR takes a new snapshot. The ADDM uses the AWR performance snapshots to locate the root causes for poor performance and saves recommendations for improving performance in SYSAUX.

ADDM also gives the recommendations for

Sql tuning advisor

Sql access advisor

Segment advisor

Undo advisor

Redo log file size advisor

Automatic Workload Repository (AWR)

AWR is the oracle database 10g’s new features.Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR). In your database some repository tables are created automatically when database is created.Each and every seconds AWR collects issues and activities in the database.

The repository is a source of information for several other Oracle 10g features.

Automatic Database Diagnostic Monitor

SQL Tuning Advisor

Undo Advisor

Segment Advisor

To check the AWR status, Show parameter statistics_level. If you want to enable the AWR report set statistics_level=Typical.If you want to disable the AWR report statistics_level=normal.

When you enable the AWR report the MMON (Manageability Monitor) background process will active.It will write issues in the repository tables. By default Every one hour AWR will generate a snapshot in SYSAUX tablespace and the default retention period is 7 days.If you want to take snapshot every 15 minutes and retention period 10 days means

begin

dbms_workload_repository.modify_snapshot_settings(

retention=>14400,       —–Minutes (=30 days). Current Value retained if null

interval=>15);              — Minutes .Current value retained if null.

End;

/

Flashback Technologies

Flashback technologies is the oracle database 10g’s new features.

Types of Flashback Technologies

There are six basic types of Flashback recovery, discussed below in detail:

Flashback Query

Flashback Version Query

Flashback Transaction Query

Flashback Table

Flashback Drop (Recycle Bin)

Flashback Database

How to Configure the flashback

In mount stage only we can enable the flashback technologies and  disable

SQL>alter database flashback on;         —–Enable

SQL>alter database flashback off;        ——–Disable

Flashback Query

You can perform a Flashback Query using a SELECT statement with an AS OF clause. You can use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time.

his example uses a Flashback Query to examine the state of a table at a specified time in the past. Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JON has been deleted from the employee table, and the DBA knows that at 9:30AM the data for JON was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30, to find out what data has been lost. If appropriate, the DBA can then re-insert the lost data in the database.

The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2006:

sql>select * from  employee as of timestamp to_timestamp(‘

2006-04-04 09:30:00′ , ‘YYYY-MM-DD HH:MI:SS ‘) where name=’JHON’;

this updates restores Jhon’s information to the employee table

sql> insert into employee (select * from employee as of timestamp to_timestamp(‘ 2006-04-04 09:30:00′ , ‘YYYY-MM-DD HH:MI:SS ‘) where name=’JHON’);

Flashback Version Query

Not only can the DBA run a manual analysis, but this is a powerful tool for the application’s developer as well. You can build customized applications for auditing purposes. Now everyone really is accountable for his or her actions. Various elements for this are shown below

Versions_xid-The transaction id that created this version of the row

Versions_operations-The action that created this version of the row (such as delete, insert, and update).

Versions_startscn-The SCN in which this row version first occurred

Versions_Endscn-The SCN in which this row version was changed

For example: we use the Dept table in Scott schema and update dept 10 to 11, then 12, and then 13 (with a commit after every update). Thus we have done three updates <!–[if gte mso 9]–>For extended help options we can use the help extended command from within the ADR command tool and shown below

We can get detailed explanations of an option such as the BEGIN BACKUP command which will present a series of screens much like that found in the old UNIX man pages.

What you should finally realize at this point is that Oracle 11g has taken monitoring to a whole new level over 10g release. In fact, think of it as monitoring on steroids. We will next view an example of how to look at the log files for database monitoring with Oracle 11g on Red Hat Linux

Lets scroll down and see more options for the show alert option with ADRCI

As you can see, there are now options to review the old style alert.log files as well as the ability to view the log files from the Oracle listener and host. We can also parse the log file to see just a recent activity which is useful for quick monitoring tasks. Lets take a peek at one of the recent log files in the next example.

We choose option 1 and the alert log file is displayed below:

By default the log files are stored under the Linux /tmp directory structure.

To exit and return to the main ADR command screen we use the :q command within the Linux vi editor.

The ADR command tool also lets us view the new health monitor reports and functions.

For instance, if I wish to look at the most recent health monitor execution tasks then I can simply issue the show hm_run command within the ADR command interface

Also of note with Oracle 11g are several new V$ tables that correspond to the ADR and new health monitoring features:

V$HM_CHECK

V$HM_CHECK_PARAM

V$HM_FINDING

V$HM_INFO

V$HM_RECOMMENDATION

V$HM_RUN

Online Patching

Online patching introduced in oracle database 11g. This simplifies administration, because no downtime is needed, and also results in a much quicker turnaround time for installing or de-installing Online Patches.

A regular RDBMS patch can require many minutes to install, since it requires instance shutdown, a relink, and instance startup. On the other hand, you can install an online patch in just a few seconds

Online patches are only applicable for Oracle RDBMS and not any other products. Online patches are currently not supported in Windows, and only supported on the following UNIX platforms for version 11.1.0.7.0 and later:

Linux x86

Linux x86_64

HP-UX Itanium (HP-UX 11.31 and later)

Solaris SPARC 64-bit (Solaris 10 and later)

Temporary Tablespace shrink

Oracle database 11g introduced the temporary tablesapce shrink feature.

SQL>alter tablespace temp1 shrink space;

This deallocates all the unused segments from the tablespace and shrinks it. After the above operation, you can check the view DBA_TEMP_FREE_SPACE to check how much the allocated space and free space currently is.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP            179306496       179306496      178257920

Basic Node Information

The below two queries will help us to identify about the environment.

SQL> set pagesize 50 

SQL> col node_name format a15 

SQL> col server_id format a8 

SQL> col server_address format a15 

SQL> col platform_code format a4 

SQL> col webhost format a12 

SQL> col domain format a20 

SQL> col virtual_ip format a12 

SQL> set linesize 132 

select 

SQL> 2 node_id, 

3 platform_code, 

4 support_db D, 

support_cp C, 

5 6 support_admin A, 

7 support_forms F, 

8 support_web W, 

9 node_name, 

10 server_id, 

11 server_address, 

12 domain, 

13 webhost, 

14 virtual_ip 

15 from 

fnd_nodes 

16 17 order by node_id; 

NODE_ID PLAT D C A F W NODE_NAME SERVER_I SERVER_ADDRESS DOMAIN WEBHOST VIRTUAL_IP 

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

8142 212 Y Y Y Y Y TRAINING ACC0C9B7 192.168.101.31 iasri.res.in TRAINING.ias training 

5E4E0096 ri.res.in 

E043C0A8 

651F9697 

19452395 

78983212 

25176481 

97992738 

8143 212 N N N N N AUTHENTICATION SECURE * 

SQL> set linesize 132 

SQL> set pagesize 132 

SQL> col NAME format A20 

SQL> col HOST format A20 

SQL> col RELEASE format A12 

SQL> col VERSION format A12 

SQL> select i.instance_name NAME, i.host_name HOST, 

2 f.release_name RELEASE, i.version VERSION 

3 from v$instance i,fnd_product_groups f 

4 where upper(substr(i.instance_name,1,4)) = 

5 upper(substr(f.applications_system_name,1,4)); 

NAME HOST RELEASE VERSION 

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

TRN TRAINING 12.1.3 11.2.0.3.

Fixing false ‘COMMERCIAL USE SUSPECTED’ In Teamviewer

Steps to be followed :
*****************

1. Uninstall Teamviewer
2. Click Start then type ‘%appdata%’ – now delete the Teamviewer folder
3. Click Start the type %temp% and delete the contents of this folder as well as the X:\Windows\temp folder
4. Click Start then type ‘regedit’, delete the registry folder found at HKEY_CURRENT_USER\Software\TeamViewer & the folder HKEY_LOCAL_MACHINE\SOFTWARE\TeamViewer
5. Then goto X:\Users\(Your username)\AppData\Roaming  > delete the Teamviewer folder [you might have to make hidden folders visible to see it or just type the path into the address bar]
6. Restart your PC
7. Now change your MAC address, either manually or with a tool like TMAC [I suggest changing the MAC address on both network connections, i.e. wireless and LAN]
8. Install TeamViewer [You'll see your PC has a new TeamViewer ID]

Manually changing your MAC Computers Address
***************************************

1. Right-click ‘My Computer’ on your desktop and select Manage
2. Go to Device Manager
3. Select Network Adapters
4. Select and double click on the adapter for which you want to change MAC address
5. You will find a properties window with multiple tab. Select Advance tab
6. From Property, find Network Address
7. Now select value and put your 12 digit hexadecimal number
8. Press OK and exit.


Now issue won't come ever.....

Look And Feel Of Self Service Pages Are Messed After R12 Upgrade or HRMS Patch

Look And Feel Of Self Service Pages Are Messed After R12 Upgrade (Doc ID 1556590.1)  

APPLIES TO:

Oracle Applications Framework - Version 11.5.10.1 to 12.1.3 [Release 11.5.10 to 12.1]

Information in this document applies to any platform.

SYMPTOMS

On : 12.1.3 version, IAS for Applications Technology

ACTUAL BEHAVIOR 

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

After upgrading E business suite framework pages fonts and formatting is messed up.

EXPECTED BEHAVIOR

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

Framework page formats should not change with patches .

STEPS

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

The issue can be reproduced at will with the following steps:

1. Login to application and access Application Dashboard

BUSINESS IMPACT

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

The issue has the following business impact:

Due to this issue, users having difficulty read some of the text .

CHANGES

 Apply Patch or Upgrade application

CAUSE

In self service pages  some images and fonts are change . This indicates images , style sheets in cabo cache is corrupt or not updated correctly .

eg.

SOLUTION

1)Shutdown all application services

2)Take backup of all files in following directories from application tier .

$OA_HTML/cabo/images/cache

$OA_HTML/cabo/styles/cache

3)Remove all files in above 2 directories

4) Restart services. 

5) Clear the client side browser cache ,restart the browser and test.

Ref :

Look And Feel Of Self Service Pages Are Messed After R12 Upgrade [ID 1556590.1]

How To Clear Caches (Apache/iAS, Cabo, Modplsql, Browser, Jinitiator, Java, Portal, WebADI) for E-Business Suite? [ID 742107.1]

Unable to paste data from excel,word,notepad to oracle forms

Today its great day for me...

Few days ago, one functional consultant come with this issue that he is not able to copy data from ms excel,word or notepad to oracle forms. The root cause for the issue with signature on the jar files in the client system.

Solution
**********

Run Adadmin and generate Jar files.

Select 'Yes' to force generate the jar files.

The regeneration of jar files will work for R12.0.4 and above application versions.

You can try the below steps if generation of jar files was not helped.

1. Clear browser cache completely and close it.
2. Clear java cache under Control Panel -> Java -> General -> Settings -> Delete Files
3. Retest the issue.


Ref : Cannot Perform a Copy and Paste from release 12.0.4 from or to Excel (Doc ID 735670.1)

How to compile all Oracle schema objects using dbms_utility.complie_schema

compile_schema is used to compile a whole schema which means you can use the procedure to re-compile procedures, functions, packages and triggers. This procedure in Oracle built-in called DBMS_UTILITY package.

The package should be run with caution as this will take a long time to execute – depending on the size and number of objects in your schema.

Precedure signature (Oracle 8x):

procedure compile_schema(schema varchar2);

Paramemters:

schema – The name of schema that you want to compile.

Oracle 11g Syntax:

procedure compile_schema(schema varchar2,

compile_all boolean default TRUE,

reuse_settings boolean default FALSE);

Paramemters:

schema – The name of schema that you want to compile.

compile_all – a boolean flag to indicate if all schema objects should be compiled even if they are INVALID

reuse_settings – a boolean flag to indicate if the sessions settings should be reused

Example:

To compile objects in SCOTT schema

exec dbms_utility.compile_schema(‘SCOTT’);

exec dbms_utility.compile_schema(‘SCOTT’,TRUE, TRUE);

Restrict users with read only privilege for all objects in database level

Method which i adhered

You can simply achieve this goal by giving him three permission.

1)Create Session: With this privilege he will be able to log on into the database.

2)Select any Table: With this privilege he will be able to select any table schema rather than SYS objects. Though if O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.

3)Select any dictionary: If O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then this privilege is not required to select data dictionary objects or in other word SYS objects. If O7_DICTIONARY_ACCESSIBILITY parameter is set to false then this privilege is required to give select permission on the data dictionary objects.

Following is an example. User arju will be globally read only user.

SQL> create user arju identified by a;

User created.

SQL> grant create session,select any dictionary,select any table to arju;

Grant succeeded.

References :
https://community.oracle.com/thread/1093477?start=0&tstart=0
https://community.oracle.com/thread/1042230
http://arjudba.blogspot.in/2008/09/create-read-only-user-for-schema.html

Create Read only user for a Schema

One thing you need to remember before read this post is there is no easy or shortcut way to make a read only user of another schema. Like grant select on username to another_username- there is no such single command like this. However you may have several alternatives to make read only user for a schema.

I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema.

Let's start by creating PROD user.

SQL> CREATE USER PROD IDENTIFIED BY P;

User created.

SQL> GRANT DBA TO PROD;

Grant succeeded.

SQL> CONN PROD/P;

Connected.

SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);

Table created.

SQL> INSERT INTO PROD_TAB1 VALUES(1,2);

1 row created.

SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE);

Table created.

SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1 

BEGIN

INSERT INTO PROD_TAB2 VALUES(SYSDATE);

END;

/

Trigger created.

SQL>CREATE VIEW A AS SELECT * FROM PROD_TAB2;

View created.

Method 1: Granting Privilege Manually

Step 1: Create devels user

SQL> CREATE USER DEVELS IDENTIFIED BY D;

User created.

Step 2: Grant only select session and create synonym privilege to devels user.

SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO DEVELS;

Grant succeeded.

Step 3:Make script to grant select privilege.

$vi /oradata2/script.sql

SET PAGESIZE 0

SET LINESIZE 200

SET HEADING OFF

SET FEEDBACK OFF

SET ECHO OFF

SPOOL /oradata2/select_only_to_prod.sql

@@/oradata2/select_only_script.sql

SPOOL OFF

This script will run the /oradata2/select_only_script.sql and generate a output script /oradata2/select_only_to_prod.sql which need to be run in fact.

Step 4:

Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.

$vi /oradata2/select_only_script.sql

SELECT 'GRANT SELECT ON PROD.' ||TABLE_NAME || ' TO DEVELS;' FROM DBA_TABLES WHERE OWNER='PROD';

SELECT 'GRANT SELECT ON PROD.' ||VIEW_NAME || ' TO DEVELS;' FROM DBA_VIEWS WHERE OWNER='PROD';

Step 5:

Now execute the /oradata2/script.sql which will in fact generate scipt /oradata2/select_only_to_prod.sql.

SQL> @/oradata2/script.sql

GRANT SELECT ON PROD.PROD_TAB1 TO DEVELS;

GRANT SELECT ON PROD.PROD_TAB2 TO DEVELS;

Step 6:

Execute the output script select_only_to_prod.sql which will be used to grant read only permission of devels user to prod schema.

SQL> @/oradata2/select_only_to_prod.sql

Step 7:

Log on devels user and create synonym so that the devels user can access prod's table without any dot(.). Like to access prod_tab2 of prod schema he need to write prod.prod_tab2. But after creating synonym he simply can use prod_tab2 to access devels table and views.

To create synonym do the following,

SQL>CONN DEVELS/D;

SQL>host vi /oradata2/script_synonym.sql

SET PAGESIZE 0

SET LINESIZE 200

SET HEADING OFF

SET FEEDBACK OFF

SET ECHO OFF

SPOOL /oradata2/synonym_to_prod.sql

@@/oradata2/synonym_script.sql

SPOOL OFF

SQL>host vi /oradata2/synonym_script.sql

SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR PROD.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='PROD';

SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR PROD.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='PROD';

SQL>@/oradata2/script_synonym.sql

SQL>@/oradata2/synonym_to_prod.sql

Step 8: At this stage you have completed your job. Log on as devels schema and see,

SQL> select * from prod_tab1;

1 2

SQL> show user

USER is "DEVELS"

Only select privilege is there. So DML will throw error. Like,

SQL> insert into prod_tab1 values(4,3);

insert into prod_tab1 values(4,3)

*

ERROR at line 1:

ORA-01031: insufficient privileges

Method 2: Writing PL/SQL Code

This is script for table :

set serveroutput on

DECLARE

sql_txt VARCHAR2(300);

CURSOR tables_cur IS

SELECT table_name FROM dba_tables where owner='PROD';

BEGIN 

dbms_output.enable(10000000);

FOR tables IN tables_cur LOOP

sql_txt:='GRANT SELECT ON PROD.'||tables.table_name||' TO devels';

execute immediate sql_txt;

END LOOP;

END;

/

This is the script for grant select permission for views :

DECLARE

sql_txt VARCHAR2(300);

CURSOR tables_cur IS

SELECT view_name FROM dba_views where owner='PROD';

BEGIN dbms_output.enable(10000000);

FOR tables IN tables_cur LOOP

sql_txt:='GRANT SELECT ON PROD.'||tables.view_name||' TO devels';

--dbms_output.put_line(sql_txt);

execute immediate sql_txt;

END LOOP;

END;

/

To create synonym on prod schema,

Log on as devels and execute the following procedure.

SQL>CONN DEVELS/D

SQL>

DECLARE

sql_txt VARCHAR2(300);

CURSOR syn_cur IS

SELECT table_name name FROM all_tables where owner='PROD'

UNION SELECT VIEW_NAME name from all_views where owner='PROD' ;

BEGIN dbms_output.enable(10000000);

FOR syn IN syn_cur LOOP

sql_txt:='CREATE SYNONYM '||syn.name|| ' FOR PROD.'||syn.name ;

dbms_output.put_line(sql_txt);

execute immediate sql_txt;

END LOOP;

END;

/

Method 3: Writing a Trigger

After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.

Create a log on trigger that eventually set current_schema to prod just after log in DEVELS user.

create or replace trigger log_on_after_devels

after logon ON DEVELS.SCHEMA

BEGIN

EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = prod';

END;

/