Optimizer Statistical Information

Statistics plays a key role in providing the correct information to the Optimizer while the queries are executed.

For example if we run a query after enabling the optimizer trace

select id from test where id=5

In the trace info we can find

Below is the optimizer statistics about the query

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FIRST  Alias: FIRST
    #Rows: 1429  #Blks:  5  AvgRowLen:  5.00  ChainCnt:  0.00
Index Stats::
  Index: IDX  Col#: 1
    LVLS: 1  #LB: 4  #DK: 10  LB/K: 1.00  DB/K: 1.00  CLUF: 7.00
Access path analysis for FIRST

After Inserting 50000 records into the table and without running the statistics we can see still see the same info and the explain plan is proportional to the above statistics.

After we run the stats on the table below is the change happened in the statistics information.

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FIRST  Alias: FIRST
    #Rows: 51429  #Blks:  88  AvgRowLen:  7.00  ChainCnt:  0.00
Index Stats::
  Index: IDX  Col#: 1
    LVLS: 1  #LB: 104  #DK: 50536  LB/K: 1.00  DB/K: 1.00  CLUF: 99.00

Now it has considered all the info.

Redo Logs Recovery

Recover Steps when Redo logs are Corrupted or Missed

1. One of the Redo Logs which is in Inactive State is corrupted or Removed
2. Active Redo Log is corrupted
3. All the Redo Logs are corrupted or Removed

In case if the Inactive Redo Logs were deleted just create the redo logs and clear the unarchived logfile.

Example

1. Removed the redo log at OS Level.
2. Switch the Log and we can see the errors in the alert log.
3. ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP groupnumber;
4. Switch the logs.
5. Take Backup again.

Incase if ONLINE Redo Log is removed or corrupted or All the redo logs were removed

We need to restore the database and need to perform incomplete Recovery.

RMAN> restore database;

RMAN> recover database;

or

SQL> recover database until cancel;

SQL> alter database open resetlogs;

FRM-92101 Failure in the Forms Server

After Installation of the R12 in Linux, When the forms are opening we could get the below error.

The error is due to the missing of the rpm openmotif21-2.1.30-11.EL5.i386.rpm.

Install the above rpm and reopen the forms.

ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 5

while creating sample database if found below error

Please check the init.ora for undo_tablespace and check the names are matching in create database stmt and init.ora file.

ORA-01092: ORACLE instance terminated. Disconnection forced
-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 5
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 4014
Session ID: 125 Serial number: 3

Oracle 11.1.0.7 to 11gR2 Upgrade Steps

1. Download and install the new 11gR2 Oracle in the target machine.

2. After installation copy the ORACLE_HOME/rdbms/admin/utlu112i.sql to some /tmp location as the 

script needs to be executed in the 11.1.0.7 box as pre-requisite step.

Pre-requisite Upgrade Steps in the source(11.1.0.7) box

1. Run the utlu112i.sql script which was copied to /tmp location in the earlier steps.

2. If the script was not run before the upgrade then the upgrade will fail while running the catupgrd.sql.

    Common error which we will face when the above script was not run before the upgrade will be

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 1:
ORA-01722: invalid number

3.Login as / as sysdba and run the below commands

      sql> spool upgrade_pre.log

      sql>@utlu112i,sql

      sql>spool off

4. The above spool file will validate whether the upgrade requirements are met or not like it will display the tablespace info,update parameters,deprecated parameters...etc.

5. Run the /rdbms/admin/utlrp.sql to validate invalidate objects.

6. For 11.1.0.7 no need to apply any DST related patches and we can take after the db upgrade.

7. Run the DBMS_STATS.GATHER_DICTIONARY_STATS.

  

     EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

8. No files should be in backup mode.

    sql>select * from v$recover_file;

    sql>select * from v$backup where status != 'NOT ACTIVE';

9. The users SYS and SYSTEM have 'SYSTEM' as their default tablespace.

10. Shutdown the db and take the cold backup or backup using rman.

11. Currently taken the cold backup like copied all the redo logs,datafiles,temp files,control files...to the target db.

Upgrade Steps in Target

1. Set the ENV in the target box(11gR2).

2. Currently copied the init.ora and modified according to the target db.

3. login as / as sysdba

    1. Startup nomount

    2. Recreate the Control file (If the target box is new)

    3. alter database open resetlogs upgrade;

    4. spool upgrade.info

     5.@/rdbms/admin/catupgrd.sql

     6. spool off

12. Will discuss the DST settings in the next blog.

R12 Architecture

HRMS Legislative Data Installation using Data Installer

The installation of legislative data is a two step process.
The first step is to run the DataInstall program and select the localizations for each HRMS product that you require or that is already installed.
The command line for the Data Install program is as follows:
java oracle.apps.per.DataInstall apps (password) thin (server):(db port):(SID)
where: (password) refers to the APPS schema password
(server) is the database server name or ip address
(db port) is the sql net or database port number default is 1521
(SID) is the database system identifier like PROD
for example:
java oracle.apps.per.DataInstall apps apps thin dbsvr1:1521:proddb
In version of apps prior to 11.5.10 the command uses jre instead of java.
for example:
jre oracle.apps.per.DataInstall apps apps thin dbsvr1:1521:proddb
DataInstall version 115.36

DataInstall Main Menu

1. Select legislative data to install/upgrade
2. Select college data to install/upgrade
3. Select JIT/Geocode or OTL to install/upgrade
4. Exit to confirmation menu

Enter your choice : 1


--------------------------------------------------------------------------------
After you enter option 1 you will see a similiar list of Localizations to select from:
# Localization Product(s) Leg. Data? Action
1 Global Human Resources Installed 
2 Australia Human Resources  
3 Australia Payroll   
4 Belgium Human Resources  
5 Canada Human Resources  
6 Canada Payroll   
7 China Human Resources  
8 China Payroll   
9 Denmark Human Resources  
10 Denmark Payroll   
11 Finland Human Resources  
12 Finland Payroll   
13 France Human Resources  
14 France Payroll   
15 Germany Human Resources  
16 Hong Kong Human Resources  
17 Hong Kong Payroll   
18 Hungary Human Resources  
19 Hungary Payroll   
20 India Human Resources  
21 India Payroll   
22 Ireland Human Resources  
23 Ireland Payroll   
24 Italy Human Resources  
25 Japan Human Resources  
26 Japan Payroll   
27 Korea, Republic of  Human Resources  
28 Korea, Republic of  Payroll  
29 Kuwait Human Resources  
30 Kuwait Payroll  
31 Mexico Human Resources  
32 Mexico Payroll  
33 Netherlands Human Resources  
34 Netherlands Payroll   
35 New Zealand Human Resources  
36 New Zealand Payroll   
37 Norway Human Resources  
38 Norway Payroll   
39 Poland Human Resources  
40 Poland Payroll   
41 Saudi Arabia Human Resources  
42 Saudi Arabia Payroll   
43 Singapore Human Resources  
44 Singapore Payroll   
45 South Africa Human Resources  
46 South Africa Payroll   
47 Spain Human Resources  
48 Spain Payroll   
49 United Kingdom Human Resources Installed 
50 United Kingdom Payroll  Installed 
51 United States Human Resources Installed 
52 United States Payroll  Installed 
53 United States US Federal Human Resources  
- Change Action
where is [I : Install, C : Clear]
[Return] - To return to main menu.
Enter your choice (for example 2I) : 1I

--------------------------------------------------------------------------------
In some cases, when selecting to install United States Payroll, JIT/GeoCode may also be marked as set to install.
It is highly recommended that JIT/Geocode be selected for installation even though it may already be marked for installation.
In cases where JIT/Geocode is not automatically selected for install, if you are installing Legislation please ensure that JIT/GeoCode is also selected.
Be sure to select Global and all other Legislation and Products you have Installed when running a later hrglobal update.
In this example be sure to enter: 1I, 49I, 50I, 51I, 52I
After selecting to install a legislations/products the list will add Install to the action column as follows:
# Localisation Product(s) Leg. Data? Action
1 Global Human Resources Installed Install
2 Australia Human Resources  
3 Australia Payroll   
4 Belgium Human Resources  
5 Canada Human Resources  
6 Canada Payroll   
7 China Human Resources  
8 China Payroll   
9 Denmark Human Resources  
10 Denmark Payroll   
11 Finland Human Resources  
12 Finland Payroll   
13 France Human Resources  
14 France Payroll   
15 Germany Human Resources  
16 Hong Kong Human Resources  
17 Hong Kong Payroll   
18 Hungary Human Resources  
19 Hungary Payroll   
20 India Human Resources  
21 India Payroll   
22 Ireland Human Resources  
23 Ireland Payroll   
24 Italy Human Resources  
25 Japan Human Resources  
26 Japan Payroll   
27 Korea, Republic of  Human Resources  
28 Korea, Republic of  Payroll  
29 Kuwait Human Resources  
30 Kuwait Payroll  
31 Mexico Human Resources  
32 Mexico Payroll  
33 Netherlands Human Resources  
34 Netherlands Payroll   
35 New Zealand Human Resources  
36 New Zealand Payroll   
37 Norway Human Resources  
38 Norway Payroll   
39 Poland Human Resources  
40 Poland Payroll   
41 Saudi Arabia Human Resources  
42 Saudi Arabia Payroll   
43 Singapore Human Resources  
44 Singapore Payroll   
45 South Africa Human Resources  
46 South Africa Payroll   
47 Spain Human Resources  
48 Spain Payroll   
49 United Kingdom Human Resources Installed Install
50 United Kingdom Payroll  Installed Install
51 United States Human Resources Installed Install
52 United States Payroll  Installed Install
53 United States US Federal Human Resources  
- Change Action
where is [I : Install, C : Clear]
[Return] - To return to main menu.
Enter your choice (for example 2I) :

--------------------------------------------------------------------------------
To return to the main menu just press the enter/return key
If you are using College data use Menu 2 to chose To install it

DataInstall Main Menu

1. Select legislative data to install/upgrade
2. Select college data to install/upgrade
3. Select JIT/Geocode or OTL to install/upgrade
4. Exit to confirmation menu

Enter your choice : 2

# Localization College Data? Action
1 United Kingdom Installed 
2 United States Installed 
- Change Action
where is [I : Install, C : Clear]
[Return] - To return to main menu.
Enter your choice (for example 2I) :

--------------------------------------------------------------------------------
To view currently installed JIT/Geocode data and to install JIT/Geocode and OTL data use menu 3
DataInstall Main Menu
1. Select legislative data to install/upgrade
2. Select college data to install/upgrade
3. Select JIT/Geocode or OTL to install/upgrade
4. Exit to confirmation menu

Enter your choice : 3
Currently installed JIT/Geocode data

Patch  applied Date
JIT_Q4_2004.1  22-MAR-2005
GEOCODE_ANNUAL_2004  22-MAR-2005
GEOCODE_ANNUAL_2004.2 22-MAR-2005
JIT_Q2.1_2004  28-OCT-2004
JIT_Q3_2003  11-DEC-2003
JIT_Q2_2003  05-AUG-2003
GEOCODE_ANNUAL_2003  05-AUG-2003
JIT_Q1_2003  04-JUN-2003
JIT_Q4.1_2002  20-JAN-2003
GEOCODE_ANNUAL_2002.1 20-JAN-2003
GEOCODE_ANNUAL_2002  20-SEP-2002
JIT_Q2_2002  20-SEP-2002
JIT_Q1_2002  16-JUL-2002
JIT_Q4_2001  17-JAN-2002
GEOCODE_ANNUAL_2001  19-DEC-2001
JIT_Q3_2001  19-DEC-2001
JIT_Q1_2001  25-JUN-2001
GEOCODE_2000_Q3  13-SEP-2000
JIT_Q3_2000  13-SEP-2000
GEOCODE_1999_Q4  21-AUG-2000
JIT_Q1_2000  21-AUG-2000
JIT_Q4_1999  12-JAN-2000
GEOCODE_1999_Q3  12-JAN-2000
# Option Data? Action
1 JIT/Geocode  Installed 
2 Oracle Time and Labor (OTL)   
- Change Action
where is [I : Install, C : Clear]
[Return] - To return to main menu.
Enter your choice (for example 1I) :

--------------------------------------------------------------------------------
To exit DataInstall program use menu 4, and confirm your choices
DataInstall Main Menu
1. Select legislative data to install/upgrade
2. Select college data to install/upgrade
3. Select JIT/Geocode or OTL to install/upgrade
4. Exit to confirmation menu

Enter your choice : 4
DataInstall - Actions confirmation
Do you really wish to exit and save your changes?
[Y] - Yes, save then exit
[N] - No, don't save but exit
[Return] - To return to the DataInstall Main Menu
Enter your choice (for example Y) : y

DataInstall - Actions summary

The following actions will be performed:


Localisation Product(s) Leg. Data? Action
Global  Human Resources Installed Install
United Kingdom Human Resources Installed Install
United Kingdom Payroll  Installed Install
United States  Human Resources Installed Install
United States  Payroll  Installed Install

Localisation College Data? Action
United Kingdom Installed 
United States  Installed 
Option Data? Action
JIT/Geocode  Installed Install
Oracle Time and Labor (OTL)  

--------------------------------------------------------------------------------
The second stage of the legislative update is to run ADPATCH to apply the hrglobal.drv driver.
Make sure you run the driver that is in the $PER_TOP/patch/115/driver directory.
If you run one from a different directory it may run without error, but no legislative data will have been updated.
Be sure to check the generated log files after hrglobal driver has completed to ensure no issues were encountered.
Please note that older version of Data Install required one to use Force Install to update legislation that where already installed.
If you see Force Install in your the list of actions in DataInstall, please apply the latest hrglobal patch to upgarde your DataInstall program.
The latest hrlgobal patch can be found in Metalink Note:145837.1.