dbv for ASM disk

Oracle disk verification Utility dbv On ASM file system

set feedback off  head off echo off linesize 200 pagesize 1000
spool /tmp/dbvchk.txt
select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=system/SYS logfile=' ||substr(name, instr(name, '/', -1, 1) +1) ||'.' || file# || '.log' from v$datafile
/ 

Output

dbv file=+DATA/rac/datafile/system.259.716288417 blocksize=8192 USERID=system/SYS logfile=system.259.716288417.1.log
dbv file=+DATA/rac/datafile/undotbs1.260.716288467 blocksize=8192 USERID=system/SYS logfile=undotbs1.260.716288467.2.log
dbv file=+DATA/rac/datafile/sysaux.261.716288483 blocksize=8192 USERID=system/SYS logfile=sysaux.261.716288483.3.log
dbv file=+DATA/rac/datafile/undotbs2.263.716288533 blocksize=8192 USERID=system/SYS logfile=undotbs2.263.716288533.4.log
dbv file=+DATA/rac/datafile/users.264.716288559 blocksize=8192 USERID=system/SYS logfile=users.264.716288559.5.log

without password

set feedback off  head off echo off linesize 200 pagesize 1000
spool /tmp/dbvchk.txt
select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=\''/ as sysdba\'' logfile=' ||substr(name, instr(name, '/', -1, 1) +1) ||'.' || file# || '.log' from v$datafile
/ 

=======

corrupt the datafile 

dd if=/dev/zero of=/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 bs=8k conv=notrunc seek=10 count=1

dbv file=/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 blocksize=8192 USERID=\'/ as sysdba\' logfile=data_D-ORCL_TS-USERS_FNO-6.6.log

 cat data_D-ORCL_TS-USERS_FNO-6.6.log

DBVERIFY: Release 12.2.0.1.0 - Production on Fri Dec 30 06:30:04 2022

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6

Page 10 is marked corrupt

Corrupt block relative dba: 0x0180000a (file 6, block 10)

Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined         : 640

Total Pages Processed (Data) : 30

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 5

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 576

Total Pages Processed (Seg)  : 11

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 17

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 1839277 (0.1839277)

[oracle@wcp12cr2 Datafile]$

select * from v$database_block_corruption ;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID

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

         6         10          1                  0 ALL ZERO           0

repair failure preview;

repair failure noprompt;

RMAN> list failure;

Database Role: PRIMARY

List of Database Failures

=========================

Failure ID Priority Status    Time Detected       Summary

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

1761       HIGH     OPEN      30-12-2022 06:42:46 Datafile 6: '/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6' contains one or more corrupt blocks

==========

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures

=========================

Failure ID Priority Status    Time Detected       Summary

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

1761       HIGH     OPEN      30-12-2022 06:42:46 Datafile 6: '/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

no manual actions available

Automated Repair Options

========================

Option Repair Description

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

1      Perform block media recovery of block 10 in file 6

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1375281163.hm  <<<< check this file 

RMAN>

[oracle@wcp12cr2 Datafile]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1375281163.hm

   # block media recovery

   recover datafile 6 block 10;

=====

define file_no=6

define block_value=8

set linesize 300

col SEGMENT_NAME for a20

col TABLESPACE_NAME for a20

SELECT segment_name, TABLESPACE_NAME,segment_type,file_id, block_id, blocks

        FROM   dba_extents

        WHERE file_id = &file_no 

--AND ( &block_value BETWEEN block_id   AND ( block_id + blocks -1 ) )

;

define file_id=6

define block_id=144

col OWNER for a15

col SEGMENT_NAME for a20

col TABLESPACE_NAME for a20

SELECT relative_fno, owner, segment_name, segment_type ,file_id, block_id, blocks

 FROM dba_extents 

 WHERE file_id = &file_id

 and block_id=&block_id

;

RELATIVE_FNO OWNER           SEGMENT_NAME         SEGMENT_TYPE                FILE_ID   BLOCK_ID     BLOCKS

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

           6 OJVMSYS         OJDS$BINDINGS$       TABLE                             6        144          8

define file_number=6

define BLOCK_NUMBER=144

SELECT relative_fno, owner, segment_name, segment_type ,file_id, block_id, blocks FROM DBA_EXTENTS WHERE FILE_ID= &file_number 

 AND &BLOCK_NUMBER BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;

RELATIVE_FNO OWNER           SEGMENT_NAME         SEGMENT_TYPE                FILE_ID   BLOCK_ID     BLOCKS

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

           6 OJVMSYS         OJDS$BINDINGS$       TABLE                             6        144          8

Oracle 11g alert log change to old alert log

sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 28 09:13:18 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> alter system set background_dump_dest='/opt/app/oracle/admin/vihaan/bdump' scope=spfile;

System altered.

SQL> alter system set "_diag_adr_enabled"=false scope=spfile;

System altered.

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup force ;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.




SQL> show parameter back

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /opt/app/oracle/admin/vihaan/b
dump
backup_tape_io_slaves boolean FALSE
db_flashback_retention_target integer 1440
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5




set pagesize 200
col KSPPINM format a50
col KSPPSTVL format a20
1* select i.ksppinm, v.ksppstvl from x$ksppi i, x$ksppcv v where i.ksppinm like '_diag_adr_enabled'
SQL> select i.ksppinm, v.ksppstvl from x$ksppi i, x$ksppcv v
where i.ksppinm like '_diag_adr_enabled%'
-- where i.ksppinm like '_%'
and i.indx=v.indx
and v.ksppstvl!='TRUE';

KSPPINM KSPPSTVL
-------------------------------------------------- --------------------
_diag_adr_enabled FALSE

oracle 10g and 11g database Enabling ARCHIVELOG Mode

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
SQL> alter system set log_archive_dest = '/opt/app/oracle/admin/vihaan/archive' scope=spfile ;

System altered.

SQL> alter system set log_archive_dest = '/opt/app/oracle/admin/vihaan/archive' scope=both;

System altered.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/admin/vihaan/archive
Oldest online log sequence 63
Next log sequence to archive 65
Current log sequence 65


set linesize 200
col DEST_NAME format a50
col DESTINATION format a30
set pagesize 100
select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST

DEST_NAME STATUS DESTINATION
------------------------------ --------- --------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID /opt/app/oracle/admin/vihaan/archive
LOG_ARCHIVE_DEST_2 INACTIVE
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE


select log_mode from v$database;


LOG_MODE
------------
ARCHIVELOG



log file status


COL GROUP# FORMAT 999999 HEAD 'Groupe'
COL THREAD# FORMAT 999999 HEAD 'Thread'
COL SEQUENCE# FORMAT 99999999 HEAD 'Sequence'
COL TAI FORMAT A6 HEAD 'Taille|Mo'
COL STATUS FORMAT A10 HEAD 'Statut'
COL MEMBER FORMAT A40 HEAD 'Nom fichier'
COL HR FORMAT A20 HEAD 'Date ouverture'
COL ARCHIVED FORMAT A7 HEAD 'Archive'
BREAK ON THREAD# NODUP ON GROUP# NODUP SKIP 1 ON TAI NODUP ON HR NODUP
COMPUTE NUMBER LABEL 'Nombre:' OF SEQUENCE# ON GROUP#
SELECT L.GROUP#, L.THREAD#, L.SEQUENCE#, LPAD(TRUNC(BYTES/1024/1024),5) TAI, L.STATUS,
MEMBER, TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS') HR,
DECODE(ARCHIVED,'YES','Oui','Non') ARCHIVED
FROM V$LOG L, V$LOGFILE F
WHERE L.GROUP# = F.GROUP#
ORDER BY 1,3,6;


in Oracle 11gr2


Oracle 11R2 archive log file

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.


SQL> alter system set log_archive_dest_1='LOCATION=/opt/app/oracle/admin/vihaan/archive';







For RAC Perform:
ALTER SYSTEM set db_recovery_file_dest_size=60G scope=both sid='*' ;
ALTER SYSTEM SET db_recovery_file_dest='+FLASH' sid='*';
In a RAC database, all instances must have the same values for these parameters. Even though there are multiple nodes they all share the same controlfiles.

To disable FRA you can use:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '' scope=both;
Note: even after you disable the flash recovery area, the RMAN will continue to access the files located in the flash recovery area for backup and recovery purposes.

Upgrade Oracle Database 10g to 11g Manual Upgrade

We are going to use Manual Upgrade

Install 11g database software in different ORACLE_HOME from source Database



oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> pwd
/opt/app/oracle/product/11.1/rdbms/admin
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> ls -lt utlu111i.sql
-rw-r--r-- 1 oracle oinstall 138636 2007-07-11 09:01 utlu111i.sql
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> pwd
/opt/app/oracle/product/11.1/rdbms/admin
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 27 09:28:04 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> spool anujupgrade.lst

SQL> @utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool 04-27-2010 09:29:25
.
**********************************************************************
Database:
**********************************************************************
--> name: VIHAAN
--> version: 10.2.0.4.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 727 MB
.... AUTOEXTEND additional space required: 247 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 473 MB
.... AUTOEXTEND additional space required: 443 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 425 MB
.... AUTOEXTEND additional space required: 175 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 672 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 11g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... OLAPSYS
.... SYSMAN
.... CTXSYS
.... XDB
.... MDSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER PUBLIC has 1 INVALID objects.
.... USER SYS has 2 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.

PL/SQL procedure successfully completed.

SQL> select * from v$timezone_file;
If time zone file version is less than 4 then apply time zone patch 5632264 manually

select object_name, owner, object_type from all_objects where status like 'INVALID';


SQL> select object_name, owner, object_type from all_objects where status like 'INVALID';

OBJECT_NAME OWNER
------------------------------ ------------------------------
OBJECT_TYPE
-------------------
DBMS_REGISTRY SYS
PACKAGE BODY

DBA_REGISTRY_DATABASE SYS
VIEW

DBA_REGISTRY_DATABASE PUBLIC
SYNONYM

col COMP_NAME format a50
set linesize 200
set pagesize 200
SQL> r
1* select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS
-------------------------------------------------- ------------------------------ --------------------------------------------
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rules Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID

17 rows selected.



1.7 If you are using spfile, create pfile
SQL> create pfile from spfile ;

This will create pfile in 10g $ORACLE_HOME/dbs/init[SID].ora





a) Remove *.background_dump_dest, *.core_dump_dest, *.user_dump_dest and add
*.diagnostic_dest=’/11g_base’ (11g Base Directory)
b) Change
*.compatible='10.2.0.1.0'
to
*.compatible=’11.1.0′




vihaan.__db_cache_size=377487360
vihaan.__java_pool_size=4194304
vihaan.__large_pool_size=4194304
vihaan.__shared_pool_size=142606336
vihaan.__streams_pool_size=0
*.control_files='/opt/app/oracle/datafile/vihaan/control01.ctl','/opt/app/oracle/datafile/vihaan/control02.ctl','/opt/app/oracle/datafile/vihaan/control03.ctl'
*.db_block_size=8192
*.db_domain='apt-amd-02'
*.db_file_multiblock_read_count=16
*.db_name='vihaan'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=vihaanXDB)'
*.job_queue_processes=10
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.compatible='11.1.0'
*.diagnostic_dest='/opt/app/oracle/admin/vihaan/diagnostic'



oracle@apt-amd-02:/opt/app/oracle/product/10.2/dbs> mkdir -p /opt/app/oracle/admin/vihaan/diagnostic
oracle@apt-amd-02:/opt/app/oracle/product/10.2/dbs> cd /opt/app/oracle/admin/vihaan/
oracle@apt-amd-02:/opt/app/oracle/admin/vihaan> ls -lt
total 8
drwxr-xr-x 2 oracle oinstall 6 2010-04-27 09:43 diagnostic
drwxr-x--- 2 oracle oinstall 4096 2010-04-27 09:29 adump
drwxr-x--- 2 oracle oinstall 4096 2010-04-27 09:20 udump
drwxr-x--- 2 oracle oinstall 141 2010-04-27 09:20 bdump
drwxr-x--- 2 oracle oinstall 35 2010-04-26 13:26 pfile
drwxr-x--- 2 oracle oinstall 6 2010-04-26 13:20 cdump
drwxr-x--- 2 oracle oinstall 6 2010-04-26 13:20 dpdump


2. Upgrade Database

Shut down source database (10g) - Your downtime starts here

SQL> connect sys/sys as sysdba
Connected.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.



2.2 Set your environment variables to Oracle Database 11g Release 1 (11.1) :

export ORACLE_HOME=/u01/oracle/11gbase/11.1.0
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/10.2
#export ORACLE_HOME=/opt/app/oracle/product/11.1
export ORACLE_SID=vihaan
export PATH=$ORACLE_HOME/bin:$PATH:.
export LIBXCB_ALLOW_SLOPPY_LOCK=1
export TZ=GMT

comment the oracle 10g home and uncomment 11g




2.3 Start Upgrade
oracle@apt-amd-02:~> pwd
/home/oracle
oracle@apt-amd-02:~> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 27 09:50:25 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade pfile='/tmp/initvihaan.ora' ;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.


Check shared_pool & java_pool size, to set new values



SQL> spool anujupgrade.log
SQL> @?/rdbms/admin/catupgrd.sql

after catupgrd.sql database will be shutdown .


SQL> /*****************************************************************************/
SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!!
SQL> */
SQL> /*****************************************************************************/
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above sql script is the final step of the upgrade. Please
DOC> review any errors in the spool log file. If there are any errors in
DOC> the spool file, consult the Oracle Database Upgrade Guide for
DOC> troubleshooting recommendations.
DOC>
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> Rem *********************************************************************
SQL> Rem END catupgrd.sql
SQL> Rem *********************************************************************
SQL>

=====


SQL> startup pfile='/tmp/initvihaan.ora' ;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.

SQL> @?/rdbms/admin/utlu111s.sql




Oracle Database 11.1 Post-Upgrade Status Tool 04-27-2010 12:11:48
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:42:49
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:20:34
Oracle Workspace Manager
. VALID 10.2.0.4.3 00:00:01
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:55
OLAP Catalog
. VALID 11.1.0.6.0 00:01:40
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:26
Oracle Enterprise Manager
. ORA-06550: line 5, column 35:
. PL/SQL: ORA-00942: table or view does not exist
. ORA-06550: line 5, column 1:
. PL/SQL: SQL Statement ignored
. ORA-00001: unique constraint (SYSMAN.PARAMETERS_PRIMARY_KEY) violated
. ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 108
. ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 166
. ORA-06512: at line 2
. VALID 11.1.0.6.0 00:20:30
Oracle XDK
. VALID 11.1.0.6.0 00:01:37
Oracle Text
. VALID 11.1.0.6.0 00:01:41
Oracle XML Database
. VALID 11.1.0.6.0 00:13:43
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:42
Oracle Multimedia
. VALID 11.1.0.6.0 00:09:04
Spatial
. VALID 11.1.0.6.0 00:06:41
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:15
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:13
Gathering Statistics
. 00:05:25
Total Upgrade Time: 02:06:28

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON



Workaround:

These errors do not result in any data loss. Therefore, you can ignore these errors.


===============================
or apply Patch 7449757

oracle@apt-amd-02:~> unzip p7449757_111070_Generic.zip
Archive: p7449757_111070_Generic.zip
creating: 7449757/
creating: 7449757/etc/
creating: 7449757/etc/xml/
inflating: 7449757/etc/xml/ShiphomeDirectoryStructure.xml
inflating: 7449757/etc/xml/GenericActions.xml
creating: 7449757/etc/config/
inflating: 7449757/etc/config/actions.xml
inflating: 7449757/etc/config/inventory.xml
inflating: 7449757/README.txt
creating: 7449757/files/
creating: 7449757/files/rdbms/
creating: 7449757/files/rdbms/admin/
inflating: 7449757/files/rdbms/admin/i1002000.sql

oracle@apt-amd-02:~> cd 7449757/
oracle@apt-amd-02:~/7449757> pwd
/home/oracle/7449757
oracle@apt-amd-02:~/7449757> /opt/app/oracle/product/11.1/OPatch/opatch apply
Invoking OPatch 11.1.0.6.0


====


SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql


SQL> create spfile from pfile='/tmp/initvihaan.ora' ;

File created.


Check invalid objects
SQL> select count(*) from dba_objects where status like 'INVALID';

COUNT(*)
----------
0


Post Upgrade steps

Check status of database components
SQL>select comp_name,version, status from dba_registry;

SQL> col COMP_NAME format a40
SQL> set pagesize 200
SQL> set linesize 200
SQL> col STATUS format a15
SQL> select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ ---------------
Oracle Enterprise Manager 11.1.0.6.0 VALID
OLAP Catalog 11.1.0.6.0 VALID
Spatial 11.1.0.6.0 VALID
Oracle Multimedia 11.1.0.6.0 VALID
Oracle XML Database 11.1.0.6.0 VALID
Oracle Text 11.1.0.6.0 VALID
Oracle Data Mining 11.1.0.6.0 VALID
Oracle Expression Filter 11.1.0.6.0 VALID
Oracle Rules Manager 11.1.0.6.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 11.1.0.6.0 VALID
Oracle Database Packages and Types 11.1.0.6.0 VALID
JServer JAVA Virtual Machine 11.1.0.6.0 VALID
Oracle XDK 11.1.0.6.0 VALID
Oracle Database Java Packages 11.1.0.6.0 VALID
OLAP Analytic Workspace 11.1.0.6.0 VALID
Oracle OLAP API 11.1.0.6.0 VALID

17 rows selected.




Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source (10g)
oracle_home to target (11g) oracle_home


===
useful link

# 429825.1 Complete Checklist for Manual Upgrades to 11gR1
# 744693.1 ORA-00001 For SYS.DIANA_VERSION$ During RDBMS Upgrade From 11.1.0.6 To 11.1.0.7
# 413671.1 Applying version 4 Time Zone Files on an Oracle Database
# 396387.1 Workarounds when Database time zone patches are not available for your patchset
# 396671.1 Usage of utltzuv2.sql before updating time zone files in Oracle 10
# 730057.1 Upgrading to 11g Fails with ORA-01722: invalid number

Check Metalink note: Complete Checklist for Manual Upgrades to 11gR1 - 429825.1

Oracle re-create or recover  parameter file
..

set linesize 300 pagesize 300

column num noprint

col value for a100

col name for a35

select num, '*' "thread#", name, value from v$parameter where num in (select num from v$parameter 

WHERE (isdefault = 'FALSE' or ismodified <> 'FALSE') and name NOT LIKE 'nls%'

MINUS

select num from gv$parameter gvp, gv$instance gvi 

where 1=1

and num in   ( select distinct gvpa.num from gv$parameter gvpa, gv$parameter gvpb where gvpa.num = gvpb.num 

and  gvpa.value <> gvpb.value 

and (gvpa.isdefault = 'FALSE' or gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%'

              ) 

and gvi.inst_id = gvp.inst_id  

and (gvp.isdefault = 'FALSE' or gvp.ismodified <> 'FALSE') 

and gvp.name not like 'nls%')

union

select num, to_char(thread#) "thread#", name, value from gv$parameter gvp, gv$instance gvi 

where 1=1

and num in (select distinct gvpa.num from gv$parameter gvpa, gv$parameter gvpb 

where 1=1

and gvpa.num = gvpb.num 

and gvpa.value <> gvpb.value 

and (gvpa.isdefault = 'FALSE' or gvpa.ismodified <> 'FALSE') 

and gvp.name not like 'nls%'

           ) 

and gvi.inst_id = gvp.inst_id  

and (gvp.isdefault = 'FALSE' or gvp.ismodified <> 'FALSE') 

and gvp.name NOT LIKE 'nls%' 

order by 1, 2;

=============

Oracle re-create or recover parameter file

SELECT

initcap(p2.value) ||' startup with '|| DECODE(p1.value, NULL,'pfile', 'spfile') || ' on '|| to_char(startup_time,'dd/mon/yyyy hh24:mi:ss') "Instance startup info"

FROM v$parameter p1, v$parameter p2, v$instance

WHERE p1.name = 'spfile'

and p2.name='db_name';

-- NUMB

col name format a25

col value format a20

-- type is_defaul is_se is_system is_modified is_ad

col description format a20

set linesize 200  pagesize 200

select

nam.indx+1 numb,

nam.ksppinm name,

val.ksppstvl value,

nam.ksppity type,

val.ksppstdf is_default,

decode(bitand(nam.ksppiflg/256,1),1,'True','False') is_session_modifiable,

decode(bitand(nam.ksppiflg/65536,3),1,'Immediate',2,'Deferred' ,3,'Immediate','False') is_system_modifiable,

decode(bitand(val.ksppstvf,7),1,'Modified',4,'System Modified','False') is_modified,

decode(bitand(val.ksppstvf,2),2,'True','False') is_adjusted,

nam.ksppdesc description

from x$ksppi nam,

x$ksppsv val

where nam.indx = val.indx

/

select

nam.indx+1 numb,

nam.ksppinm name,

val.ksppstvl value,

nam.ksppity type,

val.ksppstdf is_default,

decode(bitand(nam.ksppiflg/256,1),1,'True','False') is_session_modifiable,

decode(bitand(nam.ksppiflg/65536,3),1,'Immediate',2,'Deferred' ,3,'Immediate','False') is_system_modifiable,

decode(bitand(val.ksppstvf,7),1,'Modified',4,'System Modified','False') is_modified,

decode(bitand(val.ksppstvf,2),2,'True','False') is_adjusted,nam.ksppdesc description

from x$ksppi nam,x$ksppcv val

where nam.indx = val.indx

;

/

===

set head off

set feed off

set pages 0

--spool $ORACLE_ADMIN/pfile/initPROD920.ora.sav

SELECT

'######################################' ||CHR(10)||

'# file : init'||upper(value)||'.ora #' ||CHR(10) ||

'# Date : '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')|| ' #' ||CHR(10)||

'######################################' ||CHR(10) ||CHR(10)

FROM v$parameter

WHERE name = 'db_name';

SELECT '# '|| DESCRIPTION ||CHR(10) ,NAME||'='|| DECODE(VALUE,'TRUE',VALUE,'FALSE',VALUE,

DECODE(SIGN(INSTR(VALUE,' ')),1,'( "'||REPLACE(VALUE,', ','",'||CHR(10)||' "')||'" )', '"'||VALUE||'"'))||DECODE(upper(ISDEFAULT),'TRUE',' # (Default value) - ',NULL)|| CHR(10)

FROM v$parameter

WHERE ISDEFAULT = 'FALSE'

ORDER BY num;

SELECT '############### END ################' FROM DUAL;

--spool off;

======

col param_name format a40 heading "Parameter Name"

col param_value format a38 heading "Parameter Value" word_wrap

col isdefault format a7 heading "Default|Value"

col isses_modifiable format a10 heading "Session|Modifiable"

col issys_modifiable format a10 heading "System|Modifiable"

col ismod                          heading "Is|Modified"

-- Get database name and store in variable

column name new_value s_dbname

SELECT rtrim(name) name from v$database;

-- Get today's date

column today new_value s_curDate

SELECT to_char(sysdate, 'Month DD, YYYY') today from dual;

-- Get host name and store in variable

column host_name new_value s_machine

SELECT host_name from v$instance;

set termout on

set feedback off

set head on

select name param_name,value param_value,isdefault,ismodified ismod,isses_modifiable,issys_modifiable from v$parameter

order by param_name;

spool off

ttitle off

btitle off

clear columns

clear breaks

set feedback on

set termout on

set verify on

===

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;

exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

exec select DBID into :DID from v$database;

exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;

set linesize 300 pagesiz 300

col NAME for a45

col BVAL for a20

col EVAL for a20

select e.parameter_name             name

         , b.value                      bval

         , decode(b.value, e.value, NULL, e.value) eval

      from dba_hist_parameter b

         , dba_hist_parameter e

     where b.snap_id(+)         = :BgnSnap

       and e.snap_id            = :EndSnap

       and b.dbid(+)            = :DID 

       and e.dbid               = :DID 

       and b.instance_number(+) = 1

       and e.instance_number    = 1

       and b.parameter_hash(+)  = e.parameter_hash

       and (   nvl(b.isdefault, 'X')   = 'FALSE'

            or nvl(b.ismodified,'X')  != 'FALSE'

            or     e.ismodified       != 'FALSE'

            or nvl(e.value,0)         != nvl(b.value,0)

           )

       and e.parameter_name not like '\_\_%' escape '\'

     order by e.parameter_name;

 

----

 

set linesize 600 pagesize 300

col sid for a20

col name for a20

col value for a70

col host_name  for a15

select sid,SYS_CONTEXT('USERENV', 'HOST', 15) host_name ,name,value from v$spparameter where isspecified='TRUE' 

--and name='event' 

 order by 2;

==========

set linesize 300 pagesize 20

col NAME for a20

col VALUE for a20

col DESCRIPTION for a70

select name, value, isdefault, isses_modifiable, issys_modifiable,isinstance_modifiable, isdeprecated, description from v$parameter where upper(name) = 'PARALLEL_MAX_SERVERS';

set linesize 400 pagesize 300

col name for a30

SELECT name,

       CASE

         WHEN type = 1 THEN 'Boolean'

         WHEN type = 2 THEN 'String'

         WHEN type = 3 THEN 'Integer'

         WHEN type = 4 THEN 'Parameter file'

         WHEN type = 5 THEN 'Reserved'

         WHEN type = 6 THEN 'Big integer'

       END AS parameter_type,

       default_value

 FROM v$parameter

  WHERE ispdb_modifiable = 'TRUE' 

AND isdeprecated = 'FALSE'

   ORDER BY name;

=================

SELECT name,

           CASE

             WHEN type = 1 THEN 'Boolean'

             WHEN type = 2 THEN 'String'

             WHEN type = 3 THEN 'Integer'

             WHEN type = 4 THEN 'Parameter file'

             WHEN type = 5 THEN 'Reserved'

             WHEN type = 6 THEN 'Big integer'

           END AS parameter_type,

          default_value

    FROM v$parameter

     WHERE ispdb_modifiable = 'TRUE' AND isdeprecated = 'FALSE'

      ORDER BY name;