Farhan's Oracle Apps Blog

How to Export and Import Statistics of tables?

procedure with a scenerio

Case Definition

A critical application suddenly seems to hang, wait events show long table scans running on the OLTP environment. It comes out that the DBA in charge of this system did run statistics on the tables of the user that owns the application. The gather statistics got stuck and the process was killed. Since this moment the application started to perform extremely slowly.
The production database has several clones; we decide to export back statistics from one of these clones, to the production database.

Steps in Brief

1) Create a table to hold statistics on the source database
2) Generate a script that export table statistics on the clone database
3) Generate a script that import statistics on the clone database
4) Export statistics on clone database
5) Export table containing the exported statistics from clone database
6) Ftp export file with clone statistics table, and the script to import statistics from clone server to production server
7) Import table containing clone statistics into production database
Import statistics on production server using the script to import statisctics generated on the clone server

1. Create tables to hold statistics on the clone database

– On Cloned Database

SQL> execute BMS_STATS.create_stat_table(‘OM’,'OLD_STATS’);

PL/SQL procedure successfully completed.

SQL> grant SELECT,INSERT,UPDATE,DELETE on OM.OLD_STATS to public;

Grant succeeded.

2. Generate a script that export table statistics on the clone database

The purpose of this script is to generate one export statistics command per table, the export is directed into the table created on step 1.

Variables:

&tabname = the table created on the previous step to hold the statistics
&usrname = The name of the owner of &tabname

—- script to generate export table stats start here ———-

set linesize 130 pagesize 0
spool exportstats.sql
select ‘exec dbms_stats.export_table_stats(‘||chr(39)||owner||chr(39)||’,'||chr(39)||table_name||chr(39)||’,null,’||chr(39)||’&tabname’
||chr(39)||’,null,true,’||chr(39)||’INV’||chr(39)||’)’
from dba_tables where owner =’&usrname’
/
spool off

—- script to generate export table stats end here ———-

Note: you may also use instead of the script this command:
exec DBMS_STATS.export_schema_stats(‘&usrname’,'&tabname’)
This syntax will run in 10g. It may fail on 8i – 9i databases with some objects. That’s why I prefer the script on these versions.

3. Generate a script that import statistics on the clone database
The purpose of this script is to generate one import statistics command per table, the source is the table created on step 1.
&tabname = the table created on the previous step to hold the statistics
&usrname = The name of the owner of &tabname

—- script to generate import table stats start here ———-

set linesize 130 pagesize 0
spool importstats.sql
select ‘exec dbms_stats.import_table_stats(‘||chr(39)||owner||chr(39)||’,'||chr(39)||table_name||chr(39)||’,null,’||chr(
39)||’&tabname’||chr(39)||’,null,true,’||chr(39)||’&usrname’||chr(39)||’)’
from dba_tables where owner =’&usrname’
/
spool off

—- script to generate import table stats end here ———-

Execute this script to generate impstats.sql that will import the statistics on the production database.

4. Export statistics on clone database

Using the script expstat.sql; generated on step 2, export statistics into the statistics table created on step 1.

5. Export table containing the exported statistics from clone database

vmractest:/oradisk/av/expstats>exp avargas file=exp_stats_from_clone tables=avr.old_stats feedback=1000
Export: Release 9.2.0.5.0 – Production on Tue Feb 20 11:57:02 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 – Production
Export done in IW8ISO8859P8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
Current user changed to AVR
. . exporting table OLD_STATS
….
4115 rows exported
Export terminated successfully without warnings.

6. Ftp export file with clone statistics table from clone server to production server and script to import statistics from clone server to production server
Execute FTP session from target server, get both the table that contains the exported statistics and the script to import them, generated on step :
proddb > ftp vmractest
Connected to vmractest
220 vmractest FTP server (SunOS 5.8) ready.
Name (vmractest:oracle): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
ftp> cd /oradisk/av/expstats
250 CWD command successful.
ftp> get exp_stats_from_clone.dmp
200 PORT command successful.
150 ASCII data connection for exp_stats_from_clone.dmp (10.5.180.72,64082) (473088 bytes).
226 ASCII Transfer complete.
local: exp_stats_from_clone.dmp remote: exp_stats_from_clone.dmp
478390 bytes received in 0.17 seconds (2680.69 Kbytes/s)
ftp> get impstats.sql
200 PORT command successful.
150 ASCII data connection for impstats.sql (10.5.180.72,64776) (31461 bytes).
226 ASCII Transfer complete.
local: impstats.sql remote: impstats.sql
31704 bytes received in 0.033 seconds (947.63 Kbytes/s)
ftp> bye
221 Goodbye.

7. Import table containing clone statistics into production database

On the production database import the table that contains the exported statistics.
proddb >imp avargas file= exp_stats_from_clone.dmp full =y
Import: Release 9.2.0.5.0 – Production on Tue Feb 20 12:19:11 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 – Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and UTF8 NCHAR character set
export client uses IW8ISO8859P8 character set (possible charset conversion)
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing AVARGAS’s objects into AVARGAS
. importing AVR’s objects into AVR
. . importing table “OLD_STATS” 4115 rows imported
Import terminated successfully without warnings.

8. Import statistics on production server using the script to import statistics generated on the clone server

Using the script impstats.sql; generated on step 3, import statistics into the production database.

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. Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login. 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.

Hello,

In my today’s post we will  have discuss steps that i have followed to upgrade oracle database from 9.2.0.x to 10.2.0.4.0 for Oracle Application 11.5.10.2

Upgrade 9i Rel 9.2.0.x – 11.5.10.2 Oracle Applications database to 10gR2 10.2.0.4

Steps for upgrade

_ Check for pre requisites

_ Apply patches to current Oracle Applications instance

_ Install 10.2.0 base release in a new Oracle Home directory

_ Install Oracle Database 10g products from 10g Companion directory

_ Apply Oracle database patch 10.2.0.4

_ Create nls/data/9idata directory

_ Apply database patches to 10.2.0.4 home

_ Prepare for database upgrade

_ Upgrade database

_ Post Upgrade

_ Issues encountered during upgrade process

Check for pre- requisites

Edit /etc/sysctl.conf by adding below lines for kernel tuning

# Parameters as mentioned during 10gR2 Installation/Upgrade

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 262144

net.core.rmem_max = 262144

net.core.wmem_default = 262144

net.core.wmem_max = 262144

# sysctl -p

Apply patches to current Oracle Applications instance

Upgrade the developer version to Developer 6i Patch Set 17 or higher

Apply patch 4653225 – 11.5.10 Interoperability patch for 10g release 4 to current application

Install 10.2.0 base release in a new Oracle Home directory

Download oracle 10.2.0 base release from

http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201linuxsoft.html

Select Enterprise Edition & uncheck create database and hit next

Hit next

Click on yes and hit next

Click on install

.

login to another session and execute root.sh under mentioned location

Click on exit and continue

Hit yes and proceed

Install Oracle Database 10g products from 10g Companion directory

Click on next

Select Oracle database 10g Products 10.2.0.1.0 and hit next

Verify the location and hit next

Click on next

Click on Install

Click on exit

Hit on yes

Apply Oracle database patch 10.2.0.4 p6810189_10204_Linux-x86.zip

Click on next

Verify the path on OH and hit next

Click on Next

Fill in proper info and hit Next

Click on Install

login to another session and execute root.sh under mentioned location

Click on Exit

Hit on yes … and 10.2.0.4 software upgrade is completed

Create nls/data/9idata directory

From 10.2.0 $ORACLE_HOME/nls/data/old directory, run perl cr9idata.pl

Modify oracle environment file so that the environment variable ORA_NLS10 points to $ORACLE_HOME/nls/data/9idata

directory.

[oradev@testapps old]$ perl cr9idata.pl

Creating directory /oradev/devdb/10.2.0/nls/data/9idata

Copying files to /oradev/devdb/10.2.0/nls/data/9idata…

Copy finished.

Please reset environment variable ORA_NLS10 to /oradev/devdb/10.2.0/nls/data/9idata!

Apply database patches to 10.2.0.4 home

Patch 5386204

Patch 5868257

Please refer the metalink note 555579.1 for issues during upgrade. (10.2.0.4 Patch Set – Availability and known Issues)

Please check this note#565600.1 (Very Important).

Prepare for database upgrade

Shutdown all application services

Shutdown database listener

Verify that 9i database is defined in /etc/oratab file

Upgrade database

During this stage the 9.2.0 database should be up and running but database listener should be down and

$ORACLE_HOME should point to 10.2.0; make sure /etc/oratab has proper entry pointing to 9.2.0 ORACLE_HOME.

# cd $ORACLE_HOME/bin

# ./dbua

Hit Next

Select the correct database to upgrade and click on next

Hit yes and continue

Hit Next

Make the changes as seen in the screenshot and continue

Hit next

Choose NOT to backup your database and hit next

Hit Next

Tick the check box if flash recovery needs to be enabled and hit next

Enter system password and hit next

Confirm database upgrade summary and hit finish to start the actual migration

Monitor the migration and make sure there are no errors

Hit on OK and continue

Hit on Close …. And verify Enterprise Manager Console

Post Upgrade

1 .Install Oracle Data Mining and OLAP

Verify that Oracle Data Mining and OLAP are installed in your database by using SQL*Plus to connect to the database

as SYSDBA and running the following command:

SQL> select comp_id from dba_registry where comp_id=’ODM’ or comp_id=’AMD’;

If the query does not return ODM, then you do not have Oracle Data Mining installed. To install Data Mining, use

SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> @$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP

If the query does not return AMD, then you do not have OLAP installed. To install OLAP, use SQL*Plus to connect to

the database as SYSDBA and run the following command:

SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP

2. Add 10gR2 release specific parameters to the init.ora file and remove obsolete parameters follow the doc 216205.1

Convert to System Managed Undo if not already configured.

3. Run changeperm.sh from $ORACLE_HOME/install.

4. Select comp_name,version,status from dba_registry;

In the out put ,status of all components should be Valid. If there is any invalid check the note 565600.1

5. Fix Korean lexers

Sqlplus “/ as sysdba” @$ORACLE_HOME/ctx/sample/script/drkorean.sql

6. If AD.I has been applied, then copy adgrants.sql script for $APPL_TOP/admin directory to

$ORACLE_HOME/appsutil/admin directory and run in sqlplus connected as sysdba.

# sqlplus “/as sysdba” @adgrants.sql applsys

7. Copy $AD_TOP/patch/115/sql/adctxprv.sql to $ORACLE_HOME/appsutil/admin directory and run in sqlplus as APPS

user.

# sqlplus apps/<pwd> @adctxprv.sql <system password> CTXSYS

8. Implement and run autoconfig.

Update the RDBMS ORACLE HOME file system with the AutoConfig files by performing the following steps:

On the Application Tier (as the APPLMGR user):

Source the environment file.

Create the appsutil.zip file by executing:

$ADPERLPRG $AD_TOP/bin/admkappsutil.pl

This will create appsutil.zip in $APPL_TOP/admin/out

On the Database Tier (as the ORACLE user):Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>

cd <RDBMS ORACLE_HOME>

unzip -o appsutil.zip

Run AutoConfig by executing: <RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/

Note: If you are using an E-Business Suite release prior to 11.5.10 on a Unix platform and do not find adautocfg.sh at

the location specified above, then run adconfig.sh from <RDBMS ORACLE_HOME>/appsutil/bin to generate the

adautocfg.sh script.

9. Copy $APPL_TOP/admin/adstats.sql to $ORACLE_HOME/appsutil/admin directory. Shutdown database and startup in

restricted mode and run adstats.sql as sysdba. Then shutdown database again and startup in normal mode.(make sure

we have atleast 1.5GB)

$ sqlplus “/ as sysdba”

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> shutdown normal;

SQL> startup;

SQL> exit;

10. Start the new database listener.

11. Run adadmin and run program “recreate grants and synonyms”.

12. Apply Oracle Receivables Patch 5753621

13. Startup application services and test login.

Issues Encountered During Upgrade Process

A. Autoconfig failed with below error on Admin Node

[APPLY PHASE]

AutoConfig could not successfully execute the following scripts:

Directory: /appldev/devora/iAS/Apache/perl/bin/perl -I /appldev/devora/iAS/Apache/perl/lib/5.00503 -I

/appldev/devora/iAS/Apache/perl/lib/site_perl/5.005 -I /appldev/devappl/au/11.5.0/perl

/appldev/devcomn/admin/install/DEV_testapps

adadmat.pl INSTE8_APPLY 256

txkGenExtSecConf.pl INSTE8_APPLY 1

AutoConfig is exiting with status 35

Fix:(372714.1)

1. Manually run jtfiappr.sql

2. Run jtfiaibu.sql

3. Run jtffmdel.sql

The required parameters are as follows:

1. jtfiappr.sql – Required parameters in order are — apps

2. jtfiaibu.sql – Requred parameters parameters in order are — jtf, jtf, apps

3. jtffmdel.sql – Does not require any parameters

4. Re-run Autoconfig.