Error - Gather statistics for SYS schema as part of upgrade to 10.2.0.4

Today I have an issue while upgrading one of my 11i CU2 instance database to 10.2.0.4, as part of the upgradation process (Note:1135973.1) need to Gather statistics for SYS schema using adstats.sql in database restrict mode. But after sometimes getting errors like

--- adstats.sql started at 2010-10-30 11:52:47 ---

Checking for the DB version and collecting statistics ...

declare
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P002 
ORA-1652: unable to extend temp segment by 106496 in tablespace SYSTEM
ORA-06512: at "SYS.DBMS_STATS", line 13591 
ORA-06512: at "SYS.DBMS_STATS", line 13937 
ORA-06512: at "SYS.DBMS_STATS", line 14015 
ORA-06512: at "SYS.DBMS_STATS", line 13974 
ORA-06512: at line 2 
ORA-06512: at line 33

So checked the temporary tablespace for system, found that not TEMP.

select username,default_tablespace,temporary_tablespace from dba_users dba_users
where username in ('SYS','SYSTEM')

USERNAME    DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE
SYSTEM             SYSTEM              SYSTEM
SYS                    SYSTEM              SYSTEM

so I have changed the default temporary tablespace to temp using

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Now its showing correctly

USERNAME    DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE
SYSTEM             SYSTEM              TEMP
SYS                    SYSTEM              TEMP

Again connected database using sysdba privilages and run the adstats.sql again. But this time getting

ORA-12801: error signaled in parallel query server P002 
ORA-1652: unable to extend temp segment by 16 in tablespace TEMP
ORA-06512: at "SYS.DBMS_STATS", line 13591 
ORA-06512: at "SYS.DBMS_STATS", line 13937 
ORA-06512: at "SYS.DBMS_STATS", line 14015 
ORA-06512: at "SYS.DBMS_STATS", line 13974 
ORA-06512: at line 2 
ORA-06512: at line 33

while checking usage of temporary tablespace, usage nearing about 100% and suddenly getting this error.

Temporary tablespace usage in percentage ---
select 100*(u.tot/d.tot) "pct_temp_used" FROM
     (select sum(u.blocks) tot from v$tempseg_usage u) u,
     (select sum(d.blocks) tot from dba_temp_files d) d

Temporary tablespace usage----
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Finally decided add a tempfile of 1G

ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 1G;

Run the command again using

$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @?/rdbms/admin/adstats.sql 

--- adstats.sql started at 2010-10-30 13:49:15 ---

Checking for the DB version and collecting statistics ...

PL/SQL procedure successfully completed.

--- adstats.sql ended at 2010-10-30 14:29:36 ---

Commit complete.


Cheers!!!

Relinking TNSLSNR fails - undefined reference to `snprintf@GLIBC_2.4'

Lsnrctl File Zero Bytes After Upgrade To 10.2.0.4.0

Applying 10.2.0.4.0 PATCHSET gives the following error :-

During the relink phase for tnslsnr executable you are receiving the bellow error:

INFO: gcc -m64 -o tnslsnr -L/opt/oracle/product/10.2/db/network/lib/ -L/opt/oracle/product/10.2/db/lib/ -L/opt/oracle/product/10.2/db/lib//stubs /opt/oracle/product/10.2/db/network/lib/s0nsgl.o /opt/oracle/product/10.2/d..... /opt/oracle/product/10.2/db/lib/sysliblist`


INFO: /lib64/libcrypt.so.1: undefined reference to `snprintf@GLIBC_2.4'
collect2: ld returned 1 exit status


INFO: make: *** [tnslsnr] Error 1

Solution to this problem is that
1. Install the Oracle 10.2.0.4 patchset and ignore this relink error
2. Take a backup of the file $ORACLE_HOME/network/lib/env_network.mk

$ cp $ORACLE_HOME/network/lib/env_network.mk $ORACLE_HOME/network/lib/env_network.mk.orig3. open for edit the file $ORACLE_HOME/network/lib/env_network.mk and identify the following line:

TNSLSNR_LINKLINE+=$(SYSLIBS)
4. add above the line TNSLSNR_LINKLINE+=$(SYSLIBS) the following entry:

LLIBCRYPT = -lcrypt 5. modify the entry TNSLSNR_LINKLINE+=$(SYSLIBS) as bellow:

from
        TNSLSNR_LINKLINE+=$(SYSLIBS)
to
        TNSLSNR_LINKLINE+=$(SYSLIBS) $(LLIBCRYPT)

Regenerate the lsnrctl file using the following procedure

$cd $ORACLE_HOME/network/lib
$make -f ins_net_server.mk ilsnrctl

$cd $ORACLE_HOME/bin
$ls -la lsnrctl Make sure that lsnrclt file is regenerated.

Cheers!!!

Pfile vs SPfile

Until Oracle 8i DBAs have been using a text file called the pfile (parameter file) to store the database initialization parameters.

As of Oracle9i, new feature called the spfile (server parameter file). The spfile is a binary file that contains the same information as the old pfile.

The spfile is a server-side initialization parameter file; parameters stored in this file are persistent across database startups.

This makes all the changes made to the instance using the ALTER SYSTEM statement persistent. Oracle requires that you start an instance for the first time using the pfile and then create the spfile.

The server parameter file (also called SPFILE) is in a single location where all the necessary parameters are defined and stored. The defined parameter values are applicable for all the instances in the cluster.

The SPFILE permits dynamic changes without requiring you to bring down the instance.

By default, if you do not specify PFILE in your STARTUP command, Oracle will use a server parameter file.

SERVER PARAMETER FILE ( SPFILE )

A server parameter file is basically a repository for initialization parameters.

Initialization parameters stored in a SPFILE are persistent, meaning any parameter changes made while an instance is running can persist across instance shutdown and startup.

In this way, all the initialization parameters manually updated by ALTER SYSTEM SET commands become persistent.

It also provides a basis for the Oracle database server to self-tune.

Another advantage, particularly for multi-instance RAC systems, is that a single copy of the parameter file can be used by all instances. Even though a single file is used to specify parameters, it has different format styles to support both the common values for all instances, as well as the specific values for an individual instance.

A server parameter file is initially built from the traditional text initialization parameter file, using the create SPFILE statement. It is a binary file that cannot be browsed or edited with a text editor.

Oracle provides other interfaces for viewing and modifying parameter settings.  At system startup, the default behavior of the STARTUP command is to read a SPFILE to obtain initialization parameter settings. If the STARTUP command doesn't have a PFILE clause, it reads the SPFILE from a location
specified by the operating system.

If you choose to use the traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command.

Some initialization parameters are dynamic since they can be modified using the ALTER SESSION or ALTER SYSTEM statement while an instance is running. Use the following syntax to dynamically alter
initialization parameters:

ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value [DEFERRED]

Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. Additionally, the SCOPE clause specifies the scope of a change as described below:

SCOPE = SPFILE

(For both static and dynamic parameters, changes are recorded in the spfile, to be given effect in the next restart.)

SCOPE = MEMORY

(For dynamic parameters, changes are applied in memory only. No static parameter change is allowed.)

SCOPE = BOTH

For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameter change is allowed.)

For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

How to create server parameter file (SPFILE)
The server parameter file is initially created from a text initialization parameter file (init.ora).

It must be created prior to its use in the STARTUP command.
The create SPFILE statement is used to create a server parameter file.

The following example creates a server parameter file from an initialization parameter file.

CREATE SPFILE FROM PFILE='/u01/oracle/product/10.2.0/dbs/initTEST.ora';

Below is another example that illustrates creating a server parameter file and supplying a name.

CREATE SPFILE='/u01/oracle/product/10.2.0/dbs/TEST_spfile.ora'
FROM PFILE='/u01/oracle/product/10.2.0/dbs/initTEST.ora';

The following example creates a text initialization parameter file from the server parameter file:

CREATE PFILE FROM SPFILE;

The example below creates a text initialization parameter file from a server parameter file, where the names of the files are specified:

CREATE PFILE='/u01/oracle/product/10.2.0/dbs/initTEST.ora'
FROM SPFILE='/u01/oracle/product/dbs/10.2.0/TEST_spfile.ora';

How to find-out that whether database using spfile or pfile ?

The following query will let you know..

1) SQL> SELECT name,value FROM v$parameter WHERE name = 'spfile';
2) SQL> show parameter spfile;

What should EBS customers running 10gR2 do?

From Mr. Steven Chang blog, Premier Support is running out on both EBS 11i and the 10gR2 database this year, he have recommended that e-business suite 11i customers should start planning upgrades to Oracle E-Business Suite Release 12 immediately with 11gR2 database.


Read more from his blog about this...

Reminder: Premier Support for 10gR2 10.2.0.4 Database ends July 2010

Cheers!!!

How to Enable Enterprise Manager on the Oracle E-Business Suite Release 12

For the process to complete successfully, the database and database listener must be started. It is not neccessary that any other E-Business Suite services are running for this process to complete.

Source the environment for the 10g ORACLE_HOME. With a Release 12 environment this would mean running the [sid]_[node].env script as generated by AutoConfig in the 10gR2 ORACLE_HOME.

Implement a password file and to overcome any ORA-01031 errors on the database. (follow metalink note: 358201.1)

It is also worth considering converting your Applications database to work with an SPFile instead of an init.ora. An SPFile is approximately analogous to being a binary equivalent of the init.ora and allows for dynamic changes to many database parameters that previously would have required a database restart. Refer to Note 166601.1 for details on how to convert to using an SPFile.

Next, as the owner of the 10gR2 ORACLE_HOME, run the following command to create the Enterprise Manager repository:-

$ emca -config dbcontrol db -repos create

When the above create command is run you will be prompted for the following:

STARTED EMCA at Oct 15, 2007 12:26:22 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the values appropriate to your environment:-

Database SID: VIS12
Listener port number: 1533
Password for SYS user: change_on_install
Password for DBSNMP user: dbsnmp
Password for SYSMAN user: sysman
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings

Database ORACLE_HOME ................ /oracle/VIS12/db/tech_st/10.2.0

Database hostname ................ xxxxxxx.xx.oracle.com
Listener port number ................ 1533
Database SID ................ VIS12
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y

The repository should then start to be created. Typically this can take anywhere between 10 and 60 minutes depending on hardware and database configuration and size.

INFO: Creating the EM repository (this may take a while) ...

When the repository is created the following message should appear....

INFO: >>>>>>>>>>> The Database Control URL is http://xxxxxxxxxx.xx.oracle.com:1158/em <<<<<<<<<<< Enterprise Manager configuration completed successfully FINISHED EMCA at Sep 13, 2007 2:13:48 PM Use the above Database Control URL to access the Enterprise Manager console through a browser interface. Us the following command to drop an EM repository that has already been created: $ emca -deconfig dbcontrol db -repos drop Refer to Metalink Note 278100.1 for more detailed instructions on how to drop the DB Control repository in a 10g database. To enable or disable browser access to dbconsole use the following command after sourcing the environment for the 10g ORACLE_HOME. With a Release 12 environment this would mean running the [sid]_[node].env script as generated by AutoConfig in the 10gR2 ORACLE_HOME.

$ emctl start dbconsole

To disable browser access use the following command:

$ emctl stop dbconsole

Known Issues
Pay particular attention to TZ (timezone) issues as these may become significant during EM setup. If the repository fails to create, try setting the TZ value in the [node].[domain]_[oracle_sid]/sysman/config/emd.properties file to match the TZ environment variable.

The following error may occur when dropping the repository

Oct 29, 2007 1:05:25 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error dropping the repository

For the above error, follow the workaround in Note 358201.1 to enable a password file. The password specified on the orapwd command line must be the same as the SYS password used when running emca.


The following error may occur when creating the repository

Oct 29, 2007 2:31:31 PM oracle.sysman.emcp.EMReposConfig createRepository
CONFIG: ORA-01920: user name 'MGMT_VIEW' conflicts with another user or role name
ORA-06512: at line 40

For the above error, follow the workaround in Note 358627.1 and drop the SYSMAN and MGMT_VIEW users.

SQL> drop user SYSMAN cascade;

SQL> drop user MGMT_VIEW cascade;

If the above errors are encountered, repeat the following command to create the repository:-

$ emca -config dbcontrol db -repos create

When creating a fresh repository on a cloned database, there may be a problem where the password for the DBSNMP user is rejected. Refer to Note 337260.1 which describes how to include several parameters on the initial command line to overcome the problem. The emca log file will probably contain ORA-01034 and/or ORA-27101 errors. A typical command line to create a fresh repository is as follows

$ emca -config dbcontrol db -repos recreate -SID PROD -PORT 1533 -ORACLE_HOME /oracle/PROD/db/tech_st/10.2.0 -DBCONTROL_HTTP_PORT 1158

The emca utility will continue to prompt for the dbsnmp password however with the above command line, the password will not be rejected as invalid. This issue seems limited specifically to PROD but not VISION databases.

The following error may appear when trying to build the repository if using a fresh 12.0.4 install:-

$ORACLE_HOME/bin/emca: line 44: /nfs/bld/d29/VRIV204/db/tech_st/10.2.0/jdk/bin/java: No such file or directory

To resolve the above error edit the $ORACLE_HOME/bin/emca script and correct the $ORACLE_HOME location