Check table aud$ before 10g upgrade

Check table aud$ before 10g upgrade

Usually, it will take around 2 hour to finish the 10g upgrade from 9i, but this time, it took me more than 4 hours, that was amazing!

During the upgrade, I found the the EVENT: ‘db file scattered read’ from v$session_wait, and Oracle was updating table ‘aud$’:

UPDATE aud$
SET ntimestamp# = SYS_EXTRACT_UTC (CAST(timestamp# AS TIMESTAMP WITH TIME ZONE))
WHERE ntimestamp# IS NULL

I enabled auditing on this 9i database, which generated 2GB size of table aud$, and you may know that Oracle 10g adds many new features for auditing(you may check the table definition of aud$ between 9i and 10g), so it will also update table aud$ during the 10g upgrade.

So, you can backup table aud$ and then truncate it directly before 10g upgrade. this will speed up the upgrade.

Advertisements

Database contains stale optimizer statistics – preupgrade check – workaround

Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 10.2.0.2
This problem can occur on any platform.
Symptoms
When running the pre-upgrade script utlu102i.sql , the following warning occurs :

WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 10g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:

…. SYS
…. OLAPSYS

This warning persists even after you run :
.
For 10G: SQL>exec dbms_stats.gather_dictionary_stats
For 9.2 : SQL>exec dbms_stats.gather_schema_stats(‘SYS’,options=>’GATHER’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);
Cause
This is an issue which was reported internally.You can use the following workaround.

Solution

* Gather statistics in the original release prior to running the DBUA or running the utlu102i.sql script
* You can do the same by using the following command :

SQL> exec dbms_stats.gather_schema_stats(”,options=>’GATHER STALE’,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>’FOR ALL COLUMNS SIZE AUTO’,cascade=>TRUE);

Replace the ” with the name of the schema where the stale statistics are reported.

For Eg :
For ‘SYS’ schema :

SQL> exec dbms_stats.gather_schema_stats(‘SYS’,options=>’GATHER STALE’,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>’FOR ALL COLUMNS SIZE AUTO’, cascade=>TRUE);

Order Of Data Retrieval Differs after upgrading 9i To 10g

Applies to:
Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.1
This problem can occur on any platform.

SQL output and the order of data

It is not unknown for SQL in 10g, using the same execution plan as previously in 9i, to return the data
in a different order for columns outside of the ORDER BY clause.
For instance , the following output is from SQL with an ORDER BY NAME clause :-

NAME SURNAME
—– ——-
George Bush
Tony Blair
Tony Book

It may well change its order to

NAME SURNAME
—– ——-
George Bush
Tony Book
Tony Blair

… after an upgrade. This can cause issues with customers and applications which were
reliant on the fixed order of SURNAME although it was not explicitly ordered.
Changes
Upgrade from 9.x to 10.x
Cause
This happens because Oracle conforms to the SQL STANDARD which states that only columns which have an explicit ORDER BY instruction are guaranteed to be correctly and consistently ordered.

This is also documented in the SQL Reference Manual:-

order_by_clause

Use the ORDER BY clause to order rows returned by the statement. Without an
order_by_clause, no guarantee exists that the same query executed more than
once will retrieve rows in the same order.

SOLUTIONS

It is important to realize that the behavior is not a BUG.
The solutions to the issue are:-

* Explicitly sort on columns using an ORDER BY to guarantee consistency across versions
* However, as a temporary workaround while the application is recoded correctly it may be possible to use the following parameter to revert to 9.x sorting behavior:

alter session set “_newsort_enabled”=false;

Calculate the approximate size of the SGA (System Global Area)

How to calculate the approximate size of the SGA (System Global Area)

It is very difficult and time-consuming process to find the exactly SGA size based on values of init.ora parameters. It is difficult because of different port specific sizes of data structures that are allocated in the SGA.

It is time consuming because there are so many parameters that influence the SGA size.

For example, any parameter that configures a number of resources, such as PROCESSES and SESSIONS, will have an impact on the SGA size. SGA size information displayed upon startup of the database
. You can display it by using svrmgrl or sqlplus.

Here are few examples to find the SGA in various oracle versions.

In Oracle 8.0.X – svrmgrl connect internal show sga In Oracle 8.1.X – svrmgrl or sqlplus /nolog connect internal show sga In Oracle 9.X – sqlplus connect sys as sysdba show sga What are different sub-divisions of the SGA?

Let’s take an sample output from svrmgrl (SHOW SGA)

Total System Global Area 23460696 bytes

Fixed Size 72536 bytes

Variable Size 22900736 bytes

Database Buffers 409600 bytes

Redo Buffers 77824 bytes

Total System Global Area : – Total in bytes of all the sub-divisions that makes up the SGA.

Fixed Size: Fixed size contains general information about the state of the database and the instance, which the background processes need to access. This does not store user data. Usually this area is less than 100k in size.

Variable Size: This part is influenced by the following init.ora parameters. shared_pool_size large_pool_size java_pool_size Database Buffers:

This holds data blocks copies that are read from datafiles and can be calculated by using following formula. size = db_block_buffers * block size Redo Buffers: This is another buffer in the SGA that holds information about changes made to the database.

Approximating size calculation of the SGA

In 8.0.X :Use the following formula. ((db_block_buffers * block size) + (shared_pool_size + large_pool_size + log_buffers) + 1MB

In 8.1.X : Use the following formula. ((db_block_buffers * block size) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB

In 9.X : To approximate size of the SGA (Shared Global Area), use following formula: db_cache_size + db_keep_cache_size + db_recycle_cache_size + db_nk_cache_size + shared_pool_size + large_pool_size + java_pool_size + log_buffers + 1mb

Fix: “Oracle Database Server” component in an invalid status while upgrading to 10.2.0.4

Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.4 to 10.2.0.4
This problem can occur on any platform.
Oracle Server Enterprise Edition – Version: 10.2.0.4 to 10.2.0.4
Symptoms

— Problem Statement:

When upgrading to 10.2.0.4, following errors for package body DBMS_SQLPA are found in upgrade log file (catupgrd.sql spool file or DBUA Oracle_server.log )

” Warning: Package Body created with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY DBMS_SQLPA:

LINE/COL ERROR
——– —————————————————————–
113/5 PL/SQL: SQL Statement ignored
118/44 PL/SQL: ORA-00904: “OTHER_XML”: invalid identifier ”

This error leaves the “Oracle Database Server” component in an invalid status.

Component Status Version HH:MM:SS
Oracle Database Server INVALID 10.2.0.4.0 00:12:35
Cause
Bug 6893073 – DBMS_SQLPA IS INVALID ORA-904: “OTHER_XML”: INVALID IDENTIFIER

The pre 10G utlxplan creates a table called PLAN_TABLE.
The 10.2.0.4 catplan drops plan_table$ and then creates a public SYNONYM PLAN_TABLE for PLAN_TABLE$. The table takes precedence over the public synonym.

Customers upgrading to 10.2.0.2, 10.2.0.3, or 11.1.0.6 won’t encounter this bug during upgrade because for those releases SQL Performance Analyzer exists in the format of a one-off, which is not run as part of upgrade.
Customers upgrading to 10.2.0.4 or 11.1.0.7 will encounter the bug only and only if they are upgrading a pre-10g (e.g., 9i or 8) db where admin/utlxplan.sql was explicitly run by/as SYS to create a (user) plan table in the SYS schema. I believe this is very rare as DBA won’t use the SYS schema to do explain plan of user/application queries.

The dbms_sqlpa is a package owned by SYS that has a static SQL statement that queries the plan table which is expected to contain the other_xml column.
This is why we get an error if SYS.plan_table does not have this column. The other_xml column was introduced in 10.2.0.1 and added to the temporary global plan table plan_table$. plan_table$ was introduced in 10.1 as a replacement to table plan_table which is used to store the result of explain plan
command. plan_table has become a public synonym to plan_table$.
Solution
— To implement the solution, please execute the following steps:

1. There is an BUG (6893073) logged for the issue and only have a patch to fix this issue on Sun Solaris SPARC (64-bit). So on Sun Solaris SPARC (64-bit) platforms please apply patch 6893073 to fix this issue.

2. For the other platforms in the mean time can you please follow below Workaround:

SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

Query the dba_registry to confirm that the “Oracle Database Server” component is now in a VALID status:

SQL> select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name, substr(version,1,10) version, status from dba_registry order by modified;

Note:
It is possible that when you tried to apply the above Workaround you aren’t able to drop plan_table:

SQL> drop table plan_table;
drop table plan_table
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01008: not all variables bound
ORA-06512: at line 21

You obtain this issue when cursor_sharing is set to force. So setting cursor_sharing to exact you were able to drop the table plan_table and apply the Workaround.