Error: Component upgrade failed PORTAL component version is: 10.1.2.0.2 INVALID

Connect to Portal schema and run the below PL/SQL

SQL>    set trimspool on
SQL>         set heading off
SQL>         set feedback off
SQL>         set serveroutput on size 1000000
set linesize 10000
SQL> SQL>         begin
2              — Check Version
3              dbms_output.put_line(wwc_version.get_portal_version);
4          exception
5          when OTHERS then
6              dbms_output.put_line(‘ERROR’);
7          end;
8
9  /
10.1.2.0.2 <== before upgrade

Backup Portal, portal_public, portal_demo and portal_app schemas before the upgrade. Stop all the middle tiers.

mpoojari:oracle # cat /oracle/product/10ginfra/config/ias.properties | grep OID                                      

OID.LaunchSuccess=true
Components=Apache,J2EE,JServ,SSO,OID,DAS,DIP,OCA
OIDhost=mpoojari 
OIDport=389
OIDsslport=636

MRUA script needs the following inputs

  1.   Need -oracle_home value
  2.   Need -oid_host value
  3.   Need -oid_ssl_port value

The upgrade is perfomed through OID using SSL port for security reasons. Make sure there are no invalids for Portal schema. Compile all invalids using $ORACLE_HOME/rdbms/admin/utlrp.sql script.

Perform the Portal component upgrade to 10.1.4.0

mpoojari:oracle # ./mrua.sh -oracle_home /oracle/product/10ginfra -oid_host mpoojari -oid_ssl_port 636

Executing mrua.pl
Running on UNIX

OracleAS Metadata Repository Upgrade Assistant 10.1.4.0.0

Enter the password for SYS:
Enter the password for cn=orcladmin:
Upgrading the OracleAS Metadata Repository to release 10.1.4.0.0
Calling upgrade plugin for MRUA
Component upgraded successfully MRUA
Calling upgrade plugin for PORTAL
Error: Component upgrade failed PORTAL
Error: PORTAL component version is: 10.1.2.0.2 INVALID
Calling upgrade plugin for MRC
Component upgraded successfully MRC
FAILURE: Some OracleAS plug-ins report failure during upgrade.
Log file: /oracle/product/10ginfra/upgrade/logs/mrua2011-07-02_10-50-20AM.log

Finished mrua.pl

Version status in the metadata repository during the upgrade is as below:

SQL> select comp_name, status, version from dba_registry where comp_name=’Oracle Application Server Portal’;

COMP_NAME
——————————————————————————–
STATUS      VERSION
———– ——————————
Oracle Application Server Portal
UPGRADING   10.1.2.0.2

The error complaining about portal version 10.1.2.0.2 is due to the other portal sessions in the metadata repository holding locks on Portal schema while trying to upgrade using MRUA.

Make sure all middle tiers are down. Use opmnctl stopall to stop all middle tier process and ensure no oracle processes are running.

Stop EM console using emctl stop iasconsole.

bounce the database to clean up all portal sessions and start only the OID.

mpoojari:oracle # opmnctl startproc process-type=OID
opmnctl: starting opmn managed processes…

mpoojari:oracle # ldapbind -h mpoojari -p 389

bind successful.

Then start the upgrade once again.
mpoojari:oracle # ./mrua.sh -oracle_home /oracle/product/10ginfra -oid_host mpoojari -oid_ssl_port 636

Executing mrua.pl
Running on UNIX

OracleAS Metadata Repository Upgrade Assistant 10.1.4.0.0

Enter the password for SYS:
Enter the password for cn=orcladmin:
Upgrading the OracleAS Metadata Repository to release 10.1.4.0.0
Calling upgrade plugin for MRUA
Component has already been upgraded MRUA
Calling upgrade plugin for PORTAL <===== This will take a while (5-10mins)
Component upgraded successfully PORTAL
Calling upgrade plugin for MRC
Component has already been upgraded MRC
SUCCESS: All OracleAS plug-ins report successful upgrade

Finished mrua.pl

Check the metadata to confirm if the version has been upated.
SQL> select comp_name, status, version from dba_registry where comp_name=’Oracle Application Server Portal’;

COMP_NAME
——————————————————————————–
STATUS      VERSION
———– ——————————
Oracle Application Server Portal
VALID       10.1.4.0.0

Finally startall the processes:

  • emctl start iasconsole
  • opmnctl startall

log file:

mpoojari:oracle # pwd
/oracle/product/10ginfra/upgrade/logsmpoojari:oracle # tail -20 mrua2011-07-02_11-09-42AM.out
### Upgrade completed successfully
>>> Running /oracle/product/10ginfra/upgrade/temp/portal/tmp/popinv.sql
Portal SQL script started at Sat Jul  2 11:21:42 2011
>>> Running sql/pofrpt.sql
Portal SQL script started at Sat Jul  2 11:21:43 2011
Upgrade Ended at Sat Jul  2 11:21:43 2011

### Upgrade ended with status: 0
### OracleAS Portal 10.1.4 upgrade completed successfully.
Status is: 0
 Component upgraded successfully PORTAL
Calling upgrade plugin for MRC
——— MrcVersionPlugin.doUpgrade called
Component has already been upgraded MRC
SUCCESS: All OracleAS plug-ins report successful upgrade
Connect to Portal schema and run the following SQL:
SQL>    set trimspool on
SQL>         set heading off
SQL>         set feedback off
SQL>         set serveroutput on size 1000000
set linesize 10000
SQL> SQL>         begin
2              — Check Version
3              dbms_output.put_line(wwc_version.get_portal_version);
4          exception
5          when OTHERS then
6              dbms_output.put_line(‘ERROR’);
7          end;
8
9  /
10.1.4.0.0 <=== upgrade done

Oracle Database Session queries

DBA Blockers displays HOLDING sessions

SELECT * FROM dba_blockers;

DBA Waiters displays WAITING_SESSION, HOLDING_SESSION and LOCK details

SELECT * FROM DBA_WAITERS;
select
    sid, serial#,USERNAME, STATUS,SCHEMANAME,TERMINAL,PROGRAM,BLOCKING_SESSION, sql_id
from v$session
where sid in (select distinct BLOCKING_SESSION
from v$session where BLOCKING_SESSION IS nOT NULL);
select count(*), HOLDING_SESSION
from dba_waiters
where HOLDING_SESSION in (SELECT HOLDING_SESSION from dba_blockers)
group by HOLDING_SESSION;

Identify the session details and SQL ID

set lines 200
col USERNAME for a30
col STATUS for a10
col SCHEMANAME for a15
col TERMINAL for a10
col PROGRAM for a25
col SQL_ID for a15
select
    sid, serial#,USERNAME, STATUS,SCHEMANAME,TERMINAL,PROGRAM,BLOCKING_SESSION, sql_id
from v$session where sid=&sid;

What’s the SQL statement

set long 3000
select SQL_TEXT from v$sql where sql_id='&sql_id';

To identify the OS PID and Session ID, Serial#, use the following

set long 2000;
select sql_text from v$sql where sql_id='&SQL_ID';

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select	s.sid || ',' || s.serial# "SID/SERIAL"
,	s.username
,	s.osuser
,	p.spid "OS PID"
,	s.program
from	v$session s
,	v$process p
Where	s.paddr = p.addr
and s.sid in (<from dba_blockers or HOLDING_SESSION from dba_waiters>)
order 	by to_number(p.spid)
/

To get necessary information straight from the view v$session_longops.

col OPNAME for a20
col TARGET for a30
col MESSAGE for a40
col UNITS for a20
select * from (
  select opname, target, sofar, totalwork,
         units, elapsed_seconds, message
  from v$session_longops order by start_time desc)
where rownum <=1;

On a busy server one would like to add filter on sid and serial# in the inner query to get the info only about the necessary session and the query is as follows:

col OPNAME for a20
col TARGET for a30
col MESSAGE for a40
col UNITS for a20
select * from (
  select opname, target, sofar, totalwork,
         units, elapsed_seconds, message
  from v$session_longops
  where sid =&SID and serial# =&SERIAL_NO
  order by start_time desc)
where rownum <=1;

Long running sessions

set lines 200
col username for a25;
col curr for a10
col logon for a10
SELECT username,sid, serial#,
           TO_CHAR(CURRENT_TIMESTAMP,'HH24:MI:SS') AS curr,
           TO_CHAR(start_time,'HH24:MI:SS') AS logon,
           (sysdate - start_time)*24*60 AS mins
    FROM V$SESSION_LONGOPS
    WHERE    username is not NULL
      AND (SYSDATE - start_time)*24*60 > 1 ;

Decrypt SSO username and password

select subscriber_id, sso_username, app_id, ORASSO.WWSSO_API_PRIVATE.DECRYPT_STRING(APP_USERNAME) as APP_USERNAME, ORASSO.WWSSO_API_PRIVATE.DECRYPT_STRING(APP_PWD)  as APP_PWD
, user_fields, user_prefs from  ORASSO.WWSSO_APPUSERINFO_T

11g Management Agent Deployment via Grid Control using the ‘Push’ Method

 

I had the below errors while installing 11.1 agents on Target hosts but got it resolved.

 

 

 

 

 

 

 

 

 

 

 

If you need the solution, write an email to me.

FLASHBACK_TRANSACTION_QUERY gives you null values in UNDO_SQL and OPERATION is set to ‘UNKNOWN

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_create_file_dest                  string      D:\romford\oradata
db_recovery_file_dest                string      D:\romford\flash_recovery_area
db_recovery_file_dest_size           big integer 3G
db_flashback_retention_target        integer     1440
recyclebin                           string      on
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> alter tablespace UNDOTBS1 retention guarantee;

Tablespace altered.

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION
—————————— ———–
SYSTEM                         NOT APPLY
SYSAUX                         NOT APPLY
UNDOTBS1                       GUARANTEE
TEMP                           NOT APPLY
USERS                          NOT APPLY
MPOOJARI                       NOT APPLY

To flash back a table to an earlier SCN or timestamp, you must have either the

1. FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege.
2. SELECT, INSERT, DELETE, and ALTER object privileges on the table.
3. Row movement must be enabled for all tables in the Flashback list

Oracle has provided a new view, FLASHBACK_TRANSACTION_QUERY, to provide more information about the data versions. This includes the SQL required to reverse each change. Queries against this view are documented as “flashback transaction queries” and require the SELECT ANY TRANSACTION system privilege.

Error: Selecting from FLASHBACK_TRANSACTION_QUERY gives you null values in UNDO_SQL and OPERATION is set to ‘UNKNOWN’.

Its not that there wasn’t sufficient undo but it was not logged because of supplemental logging not enabled. This is a change which is there from 11g onwards. To make sure that the completel information is logged in the FLASHBACK_TRANSACTION_QUERY view, you must enable it. You can do so by,

SQL> alter database add supplemental log data;

Database altered.