Database Server Upgrade/Downgrade Compatibility Matrix

Upgrade Compatibility Matrix for Upgrading to 11.2.x

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

Source Database	Destination Database
9.2.0.8   (or higher)	11.2.x
10.1.0.5 (or higher)	11.2.x
10.2.0.2 (or higher)	11.2.x
11.1.0.6 (or higher)	11.2.x

The following database version will require an indirect upgrade path.

Source Database	Upgrade Path	Destination Database
7.3.3 (or lower)	=> 7.3.4.0 => 9.2.0.8 =>	11.2.x
8.0.5 (or lower)	=> 8.0.6.x => 9.2.0.8 =>	11.2.x
8.1.7 (or lower)	=> 8.1.7.4 => 10.2.0.4 =>	11.2.x
9.0.1.3 (or lower)	=> 9.0.1.4 => 10.2.0.4 =>	11.2.x
9.2.0.7 (or lower)	=> 9.2.0.8 =>	11.2.x

 

Upgrade Compatibility Matrix for Upgrading to 11.1.x

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

Source Database	Destination Database
9.2.0.4   (or higher)	11.1.x
10.1.0.2 (or higher)	11.1.x
10.2.0.1 (or higher)	11.1.x

The following database version will require an indirect upgrade path.

Source Database	Upgrade Path	Destination Database
7.3.3 (or lower)	=> 7.3.4.0 => 9.2.0.8 =>	11.1.x
8.0.5 (or lower)	=> 8.0.6.x => 9.2.0.8 =>	11.1.x
8.1.7 (or lower)	=> 8.1.7.4 => 9.2.0.8 =>	11.1.x
9.0.1.3 (or lower)	=> 9.0.1.4 => 9.2.0.8 =>	11.1.x
9.2.0.3 (or lower)	=> 9.2.0.4.0 =>	11.1.x

Upgrade Compatibility Matrix for Upgrading to 10.2.x

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

Source Database	Destination Database
8.1.7.4   (or higher)	10.2.x
9.0.1.4   (or higher)	10.2.x
9.2.0.4   (or higher)	10.2.x
10.1.0.2 (or higher)	10.2.x

 

The following database version will require an indirect upgrade path.

Source Database	Upgrade Path	Destination Database
7.3.3 (or lower)	=> 7.3.4 => 8.1.7 =>8.1.7.4 =>	10.2.x
7.3.4 (or lower)	=>8.1.7 => 8.1.7.4 =>	10.2.x
8.0.n (or lower)	=>8.1.7 => 8.1.7.4 =>	10.2.x
8.1.n (or lower)	=>8.1.7 => 8.1.7.4 =>	10.2.x

For more info refer Oracle Note ID: 551141.1

Gather Schema Statistics" program error with Locks and Duplicate columns

Error

*******

**Starts**14-APR-2012 23:58:43

**Ends**15-APR-2012 05:34:48

ORA-0000: normal, successful completion

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 24 internal_flag= NOBACKUP

stats on table AQ$_WF_CONTROL_P is locked

stats on table FND_CP_GSM_IPC_AQTBL is locked

stats on table FND_SOA_JMS_IN is locked

stats on table FND_SOA_JMS_OUT is locked

Error #1: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #2: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #3: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

+---------------------------------------------------------------------------+

End of log messages from FND_FILE

+---------------------------------------------------------------------------+

Successfully resubmitted concurrent program FNDGSCST with request ID 35759846 to start at 22-APR-2012 00:00:00 (ROUTINE=AFPSRS)

Solution

***********

1. Please follow Doc ID 375351.1 to unlock the tables:

                AQ$_WF_CONTROL_P

                FND_CP_GSM_IPC_AQTBL

                FND_SOA_JMS_IN

                FND_SOA_JMS_OUT

               

From DB node run below to unlock tables

                exec dbms_stats.unlock_table_stats('APPLSYS','AQ$_WF_CONTROL_P');

                exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_IPC_AQTBL');

                exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_IN');

                exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_OUT');

2. Please perform the following action plan from Doc ID 781813.1 :

   Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.

   Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

   SQL> create table FND_HISTOGRAM_COLS_BKP as select * from FND_HISTOGRAM_COLS;

 -- identify duplicate rows

   select table_name, column_name, count(*)

   from FND_HISTOGRAM_COLS

   group by table_name, column_name

   having count(*) > 1;

 -- Use above results on the following SQL to delete duplicates

   delete from FND_HISTOGRAM_COLS

   where table_name = '&TABLE_NAME'

   and column_name = '&COLUMN_NAME'

   and rownum=1;

 -- Use following SQL to delete obsoleted rows

   delete from FND_HISTOGRAM_COLS

   where (table_name, column_name) in

   (

   select hc.table_name, hc.column_name

   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

   where hc.table_name ='&TABLE_NAME'

   and hc.table_name= tc.table_name (+)

   and hc.column_name = tc.column_name (+)

   and tc.column_name is null

   );

3. Run "Gather Schema Statistics" program again.

Data Guard Status Check Queries

1. Issue the following query to show information about the protection mode, the protection level, the role of the database, and switchover status:

                SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

2. On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log.

                SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Or

                SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

3. On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

                SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

4. Query the physical standby database to monitor Redo Apply and redo transport services activity at the standby site.

                SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

5. To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view.

                SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

6. The V$DATAGUARD_STATUS fixed view displays events that would typically be triggered by any message to the alert log or server process trace files.

                SELECT MESSAGE FROM V$DATAGUARD_STATUS;

7. Determining Which Log Files Were Not Received by the Standby Site.

                SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

8.If a delayed apply has been specified or an archive log is missing then switchover may take longer than expected.

Check v$managed_standby

                select process, status, sequence# from v$managed_standby;

OR alternatively:

                select name, applied from v$archived_log;