upgrade database from 11.2.0.3 to 11.2.0.4 when recovered using standby controlfile

All DBAs are hands on with backup/restore and upgrade scenarios. Here we will discuss how we will take backup without any corrupted block and to restore as is or corrupt free process. Rather than backup control-file we will take backup of standby control file and restore from this.

Someone ask why? This way we ll transfer archivelogs and keep on applying manually with will of dataguad commands which are common to all DBAs. So that we can take very less downtime to finish the upgrade process.

So, here are the step-by-step process :

Phase-1 :-- Restore and Recover ( Using standby controlfile)

a) Restore database
Precautions to restore to avoid block corruption:

1) Enable force-logging the primary database.

-- To enable
SQL> ALTER DATABASE FORCE LOGGING;

-- To verify
SQL> select force_logging from v$database;

FOR
---
YES

Note: The FORCE LOGGING option is the safest method to ensure that all the changes made in the database will be captured and available for recovery in the redo logs. Force logging is the new feature added to the family of logging attributes.

2) validate the database and backups before restore.
-- To validate database:

RMAN> validate database;
OR
RMAN> BACKUP CHECK LOGICAL VALIDATE DATABASE;

To Take logically valid backup:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

-- To validate specific datafiles:
RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup check logical validate datafile x,y,z;
release channel d1;
release channel d2;
}

-- To validate backup piece:
RMAN> VALIDATE BACKUPSET <n>;
e.g.,
RMAN> VALIDATE BACKUPSET 22;

Now in standby restore the database.
RMAN> restore database;

You can follow the below secure process also:

RMAN> RESTORE DATABASE VALIDATE;

Note : use set new name if any datafile path is change.

b) Recover database
Precautions to restore to avoid block corruption:

in standby:

-- To enable 
alter system set DB_BLOCK_CHECKING = TRUE ;
alter system set DB_BLOCK_CHECKSUM = TRUE ;
alter system set "_DB_BLOCK_CHECK_FOR_DEBUG" = TRUE;

-- To disable ( must be disable when standby is open for production in r/w operation)

alter system set DB_BLOCK_CHECKING = FALSE ;
alter system set DB_BLOCK_CHECKSUM = FALSE ;
alter system set "_DB_BLOCK_CHECK_FOR_DEBUG" = FALSE;

-- now start MRP
SQL> alter database recover managed standby database disconnect from session;

-- Catalog archivelog location to recover
RMAN> catalog '/u03/backup_logs/archivelogs';

Phase-2 :-- Stop synch

-- To see the current database status, role

SQL> select name,open_mode,database_role from v$database;

-- Cancel the recovery after applying all redo logs

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;


Phase-3 :-- Convert to primary

SQL> select name,open_mode,database_role from v$database;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

SQL> select name,open_mode ,database_role from v$database;
SQL> select count(1) from dba_objects where status='INVALID';

Phase-4 :-- Upgrade
SQL> connect / as sysdba
SQL> shut immediate;
SQL> startup upgrade;
----------verify the registry before catupgrd--------
SQL> set line 120;
SQL> column COMP_NAME format a35;
SQL> column VERSION format a10;
SQL> column status format a10;
SQL> select COMP_NAME, VERSION,status from dba_registry;
----------------------------------------------------
SQL> @?/rdbms/admin/catupgrd.sql   (default)
SQL> @?/rdbms/admin/utlrp.sql     (default)
SQL> @/home/oracle/work_dir/dbupgdiag.sql (downloaded)
SQL> @?/rdbms/admin/utlrp.sql     (default) -- once more
SQL> shut immediate;
SQL> startup;

----------verify the registry after catupgrd--------
SQL> set line 120;
SQL> column COMP_NAME format a35;
SQL> column VERSION format a10;
SQL> column status format a10;
SQL> select COMP_NAME, VERSION,status from dba_registry;
----------------------------------------------------

SQL> select name,open_mode ,database_role from v$database;
SQL> select count(1) from dba_objects where status='INVALID';

------------------commands ------------------------------------

-- standby

SQL> select name,open_mode ,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD  MOUNTED              PHYSICAL STANDBY

SQL> select max(sequence#),thread# 
from v$archived_log 
where applied='YES' group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
         39691          1


-- primary


SQL> select max(sequence#),thread# from v$archived_log where group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
         39691          1

SQL> select count(1) from dba_objects where status='INVALID';

  COUNT(1)
----------
      1264

-- standby

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      MOUNTED              PHYSICAL STANDBY

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      MOUNTED              PRIMARY

SQL>
SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup upgrade;
ORACLE instance started.

.....
Database mounted.
Database opened.
SQL>

SQL> set line 120;
column COMP_NAME format a35;
column VERSION format a10;
column status format a10;
select COMP_NAME, VERSION,status from dba_registry;
SQL>
COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Enterprise Manager           11.2.0.3.0 VALID
Oracle XML Database                 11.2.0.3.0 VALID
Oracle Expression Filter            11.2.0.3.0 VALID
Oracle Rules Manager                11.2.0.3.0 VALID
Oracle Workspace Manager            11.2.0.3.0 VALID
Oracle Database Catalog Views       11.2.0.3.0 VALID
Oracle Database Packages and Types  11.2.0.3.0 VALID
JServer JAVA Virtual Machine        11.2.0.3.0 VALID
Oracle XDK                          11.2.0.3.0 VALID
Oracle Database Java Packages       11.2.0.3.0 VALID

10 rows selected.

SQL>

SQL> select count(1) from dba_objects where status='INVALID';  -- this count will vary

  COUNT(1)
----------
      1328

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

SQL> startup;
ORACLE instance started.
.....
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> set line 120;
SQL> column COMP_NAME format a35;
SQL> column VERSION format a10;
SQL> column status format a10;
SQL> select COMP_NAME, VERSION,status from dba_registry;
COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Enterprise Manager           11.2.0.4.0 VALID
Oracle XML Database                 11.2.0.4.0 VALID
Oracle Expression Filter            11.2.0.4.0 VALID
Oracle Rules Manager                11.2.0.4.0 VALID
Oracle Workspace Manager            11.2.0.4.0 VALID
Oracle Database Catalog Views       11.2.0.4.0 VALID
Oracle Database Packages and Types  11.2.0.4.0 VALID
JServer JAVA Virtual Machine        11.2.0.4.0 VALID
Oracle XDK                          11.2.0.4.0 VALID
Oracle Database Java Packages       11.2.0.4.0 VALID

10 rows selected.
SQL>
and then,

@?/rdbms/admin/utlrp.sql
@/home/oracle/work_dir/dbupgdiag.sql
SQL> select count(1) from dba_objects where status='INVALID';  
-- this count will vary

  COUNT(1)
----------
       393

1 row selected.

SQL>

Note: Invalid objects counts must be less or equal after upgrade. Check owner-wise valid objects counts also before and after upgrade.

-- Post upgrade activity -- if live for production

a) create/ reconfigure listener
b) create/ modify tnsnames.ora
c) gather stats:
   1) gather all schema's stats:
        SQL> exec dbms_stats.gather_table_stats('owner','table_name',cascade => TRUE);
Best practice after upgrade- gather tables like below:
SQL>
exec dbms_stats.gather_table_stats(ownname=>'user_name', 
tabname=>'table_name', 
estimate_percent => 100, 
cascade=>true, 
method_opt=>'for all columns size AUTO'); 

   2) gather sys views and fixed objects:
   SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; 
    SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; 

d) disable below parameters:( enable if you compromise performance):
-- To disable ( must be disable when standby is open for production in r/w operation)

alter system set DB_BLOCK_CHECKING = FALSE ;
alter system set DB_BLOCK_CHECKSUM = FALSE ;
alter system set "_DB_BLOCK_CHECK_FOR_DEBUG" = FALSE;

e) Take FULL rman backup before handover as production.

Note: Some Oracle 11.2.0.x bug fixes for AIX platform:

1) to get better execution plan: If your performance degraded, then OFF it and try.

SQL> ALTER session "_fix_control"='5483301:OFF'; 

2) Stop weekend oracle default maintenance windows

Thanks!!

ORA-01666: control file is for a standby database - failover over standby as primary

Few and quick steps to open the standby database as primary with failover option. Note that primary is not avialbe to switch over. Dataguad broker not configured.

Quick round-up:

SQL> select name,open_mode ,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD     READ ONLY            PHYSICAL STANDBY

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

Total System Global Area 3.4206E+10 bytes
Fixed Size                  2238616 bytes
Variable Size            3422553960 bytes
Database Buffers         3.0736E+10 bytes
Redo Buffers               45682688 bytes
Database mounted.
SQL>


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01666: control file is for a standby database


SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD     MOUNTED              PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required

-- Fail-over

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL>

-- Now verify the database status

SQL> select name,open_mode ,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      MOUNTED              PRIMARY

-- Now open the database

SQL> alter database open;

Database altered.

SQL> select name,open_mode ,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      READ WRITE           PRIMARY


-- If you have flashback on, make it off if you want.

SQL> alter database flashback off;

Database altered.


Thanks.

Upgrade Oracle database from 10.2.0.4 to 11.2.0.4 ( Real time steps)

Upgrade your database from Oracle 10.2.0.4 to 11.2.0.4 database
Sharing real time knowledge,issues on Oracle DBA

1. Upgrade Procedure

      1. Current database version is 10.2.0.4.0 64 Bit
      2. Install 11.2.0.3 Software on current server
      3. Direct upgrade to 11.2.0.4

2. Download 11.2.0.4 software

Patch 10404530: 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
Platform: Linux x86-64

Download above software from Oracle Support and keep in a staging location

3. Check Kernel version & OS Version

2.6.32 or later
uname –a (Use this command to check Kernel version and OS version

4. Create new ORACLE_HOME location

mkdir -p /u01/app/oracle/product/11.2.0/db_1

4. Set Oracle Inventory

Edit /etc/oraInst.loc to point to /u01/app/oraInventory
mkdir -p /u01/app/oraInventory

5. Install 11.2.0.4 Enterprise edition Oracle software

Enable VNC, Enter following command on VNC to start OUI for installation
/u03/software/oracle11204/runInstaller

Use new ORACLE_HOME for 11.2.0.4 as /u01/app/oracle/product/11.2.0/db_1

6. Install Oracle Database 11g Products from the 11g Examples CD

Enable VNC, Enter following command on VNC to start Example Installer
/u03/stage/11203_sw/examples/runInstaller

7. Set the environment

export ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
export PATH  = $ORACLE_HOME/bin: $ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib.


8. Take full backup before upgrade activity.
       RMAN and EXPDP
9. Apply recommended Patches for 11.2.0.4 ORACLE_HOME

Use Opatch to apply above patch

10. Apply 11.2.0.4 Performance patches:

11. Set the environment to Existing 10.2.0.4 Environment

Set SID,ORACLE_HOME,PATH,LD_LIBRARY_PATH

login to PROD database

12. Run Pre-Upgrade Information Tool in 10.2.0.4 database (Must)

sqlplus '/ as sysdba'
spool pre_upg_11204_info.log
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql
spool off

Check the output of the Pre-Upgrade Information Tool in upgrade_info.log
Run utlrp.sql to fix invalid objects or components issue

Important: If you see a warning about the presence of the release 10g DMSYS schema in the database, then you must drop the DMSYS schema before proceeding with the upgrade. Better raise a SR with Oracle support for confirmation. This is one way. Without drop also we can proceed. Follow my next steps.

13.  Check timezone version of 10.2.0.4 database and 11.2.0.3 and compare if both are same

SQL> SELECT version FROM v$timezone_file;

   VERSION
   ---------
         4

Note: When upgrading from 10.1.0.x , 10.2.0.x or 11.1.0.x to 11.2.0.3: (1358166.1)

* For 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 there is no need to apply any patchset before upgrading to 11.2.0.3 Upgrade to 11.2.0.3.No need to apply DST patches on the 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 side first. You can skip any DST related upgrade instructions. The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or

10.2.0.5.

* (recommended) update the 11.2.0.3 database(s) to DSTv14 (standard DST version of 11.2.0.3) by following Note 977512.1 Updating the RDBMS. DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step 3a) using "14" as (<the new DST version number>) in that note. When going to

DSTv14 there is no need to apply any DST patch to the 11.2.0.3 home.

14. Upgrade 11.2.0.1 to 11.2.0.3 using Manual upgrade method

15. Get DBA_REGISTRY information

sqlplus '/ as sysdba'
select COMP_ID,COMP_NAME,VERSION,STATUS,MODIFIED from dba_registry;

16.  Purge Recycle bin

PURGE DBA_RECYCLEBIN;

17. Gather Dictionary stats

EXECUTE dbms_stats.gather_dictionary_stats;

18.  Shutdown PROD database listener and database

shut immediate
lsnrctl stop PROD

19. Copy of parameter files and edit for upgrade

Copy the init parameter file from 10.2.0.4 ORACLE_HOME/dbs into 11.2.0.4 ORACLE_HOME/dbs

Adjust the parameter file for the upgrade

Adjust initialization parameters that might cause upgrade problems.

• Remove obsolete initialization parameters based on pre-upgrade tool output
• Set the COMPATIBLE parameter if not already explicitly set

Adjust the parameter file for the upgrade

Adjust initialization parameters that might cause upgrade problems.

• Remove obsolete initialization parameters based on pre-upgrade tool output
• The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST
• Set the COMPATIBLE parameter if not already explicitly set

If you are upgrading from 10.1.0.x or 10.2.0.x then you can leave the COMPATIBLE parameter set to it's current value until the upgrade has

been completed successfully. This will avoid any unnecessary ORA-942 errors from being reported in SMON trace files during the upgrade

(because the upgrade is looking for 10.2 objects that have not yet been created).

grep -i comp initPROD.ora
*.compatible = 10.2.0.4.0

20. Prepare Oracle environment

export ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
export PATH  = $ORACLE_HOME/bin: $ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib.
export ORACLE_BASE=/u01/oracle/PROD/

21. Verify /etc/oratab

PROD should point to /u01/app/oracle/product/11.2.0/db_1 as ORACLE_HOME

22. Run upgrade scripts in VNC session

cd $ORACLE_HOME/rdbms/admin
sqlplus '/ as sysdba'
STARTUP UPGRADE

Monitor the alert log file and check for any errors.

SPOOL upgrade_PROD_11203.log
@catupgrd.sql

The output is at:
/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/upgrade_PROD_11203.log

STARTUP

Post-Upgrade Status Tool.
@utlu112s.sql

Verify the results, and if there any errors, must be resolved at this point.

@catuppst.sql

Identifying Invalid Objects:

Any invalid SYS/SYSTEM objects found before upgrading the database are stored in the table named registry$sys_inv_objs. Any invalid non-SYS/SYSTEM objects found before upgrading the database are stored in registry$nonsys_inv_objs.To identify any new invalid objects due to the upgrade

After the upgrade, run ORACLE_HOME/rdbms/admin/utluiobj.sql

Compile invalids:

@?/rdbms/admin/utlrp.sql

23. Post Upgrade Steps

24. Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset [ID 1358166.1]

A) Applying 11.2.0.3 on 11.2.0.2 or 11.2.0.1

 Check the current version of the 11.2.0.2 or 11.2.0.1 Oracle RDBMS time zone definitions - this needs to be done for ALL databases in the

11.2.0.2 or 11.2.0.1 home:

SQL> conn / as sysdba
Connected.

SQL>SELECT version FROM v$timezone_file;

VERSION
----------
14

A.1)  The result is lower than 14 for all databases (typically 11 or 14)

Note : Every database in 11.2.0.1 home needs to be checked, if one or more has a result higher than 14 you need to follow step A.3)

If your current RDBMS time timezone version is  lower than 14 ,  install 11.2.0.3 in a new home and update the 11.2.0.2 or 11.2.0.1 databaseto 11.2.0.3 You can skip any DST related sections in the patchset documentation , there is no need to apply DST patches or check for DST issues for theupdate to 11.2.0.3
The 11.2.0.3 RDBMS DST version after the update to 11.2.0.3 will be the same as your 11.2.0.2 or 11.2.0.1 system.After the upgrade to 11.2.0.3 you can* (recommended) update to DSTv14 (standard DST version of 11.2.0.3) by following Note 977512.1 Updating the RDBMS DST version in 11gR2
(11.2.0.1 and up) using DBMS_DST from step 3a) onwards, when going to DSTv14 there is no need to apply any DST patch to the 11.2.0.3 home

* (optional) update to a higher DST than DSTv14 version if this is needed.

The latest available DST patch is found in Note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches

A.2)  The result is 14 for all databases
Note : Every database in 11.2.0.2 or 11.2.0.1 home needs to be checked, if one or more has a result higher than 14 you need to follow steps.

A.3)

If your current RDBMS time timezone version is 14 , install 11.2.0.3 in a new home and update the 11.2.0.2 or 11.2.0.1 database to 11.2.0.2.

You can skip any DST related sections in the patchset documentation , there is no need to apply DST patches or check for DST issues for theupdate to 11.2.0.3

The 11.2.0.3 RDBMS DST version after the update to 11.2.0.3 will be 14.

(optionally) After the upgrade to 11.2.0.3 you can check if there is a newer DST version out and , if needed, update every database to thisDST version. The latest available DST patch is found in Note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File

patches:

A.3)  The result is higher than 14 for one or more databases
If your current RDBMS time timezone version is higher then 14, you need to re-apply the same RDBMS DST version as found in step A) afterinstalling the 11.2.0.3 software . Or in other words, you need to apply the 11.2.0.3 RDBMS DST patch for the same DST version before openingthe database in the new 11.2.0.3 software home.

Apply the 11.2.0.3 RDBMS DST patch after installing the 11.2.0.3 software with the Oracle Universal installer.

If you also need to patch the OJVM then we suggest to apply the latest DST OJVM fix for 11.2.0.3, even if your current RDBMS DST patch is lower. This is best done right after installing the RDBMS DST patch, no need to follow the OJVM DST patch readme instructions as there is no database yet.

For the actual OJVM and RDBMS DST patches for your 11.2.0.3 release please see Note 412160.1 Updated Time Zones in Oracle Time Zone File


After installing the same RDBMS (and the OJVM - which may be the lastest/higher than the RDBMS DST version) DST patch in the 11.2.0.3 ORACLE_HOME upgrade to 11.2.0.3 without any action on TSLTZ or TSTZ data. Follow the upgrade or patchset apply instructions, you can skip any DST related sections.

The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.2 will be the same DST version as used in the 11.2.0.2 or 11.2.0.1 Oracle RDBMS.

(optionally) After the upgrade to 11.2.0.3 you can check if there is a newer DST version out and , if needed, update every database to this DST version. The latest available DST patch is found in Note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File


25. Do the actual RDBMS DST version update of the database using DBMS_DST:

conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on

-- check if previous prepare window is ended

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- output should be
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE

-- If DST_UPGRADE_STATE is "PREPARE" then you did not ended
-- the prepare window in step 3)

-- If there are objects containing TSTZ data in recycle bin,
-- please purge the bin now.
-- Otherwise dbms_dst.begin_upgrade will report "ORA-38301: Can not perform DDL/DML over objects in Recycle Bin".

purge dba_recyclebin;

-- clean used tables

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

-- this alter session might speed up DBMS_DST on some db's
-- see Bug 10209691

alter session set "_with_subquery"=materialize;

-- to avoid the issue in note 1407273.1
alter session set "_simple_view_merging"=TRUE;

-- start upgrade window

EXEC DBMS_DST.BEGIN_UPGRADE(14);

-- the message
-- "An upgrade window has been successfully started."
-- will be seen

-- check if this select

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- gives this output:
-- PROPERTY_NAME VALUE
-- --------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION <the old DST version number>
-- DST_UPGRADE_STATE UPGRADE

-- Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE
-- BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables (containing TSTZ data) with the UPGRADE_IN_PROGRESS

property.
-- even if this select gives no rows you still need to do to the rest of the steps
-- it simply gives an indication of how many user objects need to processed in the later steps
-- some oracle provided users may be listed here, that is normal

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

-- restart the database

shutdown immediate
startup

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;

-- now upgrade the tables who need action

set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

-- ouput of this will be a list of tables like:

-- Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
-- Number of failures: 0
-- ....
-- Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
-- Number of failures: 0
-- Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
-- Number of failures: 0
-- Failures:0

-- if there where no failures then end the upgrade.

VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

-- output that will be seen:
-- An upgrade window has been successfully ended.
-- Failures:0

-- last checks

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- needed output:
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE


SELECT * FROM v$timezone_file;

-- needed output:
-- FILENAME VERSION
-- ------------------ ----------
-- timezlrg_<new version>.dat <new version>

-- if registry$database exists then update this static table also with the new DST version
-- this table is used during upgrade and contians the DST version from before the upgrade
-- this update is mainly to avoid confusion when people notice this has a lower version

select TZ_VERSION from registry$database;

update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
commit;

26. Configuring Fine-Granined Access to External Network services After upgrading Oracle database

Create ACLs as per note id: 958129.1:

SQL> SELECT OWNER, REFERENCED_NAME FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL();
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE();
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL();

COMMIT;

SQL> select * from dba_network_acls;

27. Upgrade Oracle Text - None

After an upgrade to the new Oracle Database 11g release, copy the following files from the previous Oracle home to the new Oracle home:

■ Stemming user-dictionary files
■ User-modified KOREAN_MORPH_LEXER dictionary files
■ USER_FILTER executables

These files affect all databases installed in the given Oracle home.

You can obtain a list of these files by doing the following:
1. Looking at $ORACLE_HOME/ctx/admin/ctxf102.txt
2. Executing $ORACLE_HOME/ctx/admin/ctxf102.sql as database user SYS,SYSTEM, or CTXSYS

28. Configure and Start new database listener

Get your listener.ora and tnsnames.ora files from Old Home to New home
Set the TNS_ADMIN environment variable to the directory where you got your listener.ora and tnsnames.ora files.

Start listener


Cheers !!! Post a Comment

Oracle XML Database status INVALID in dba_registry during Upgrade - a case study

Database upgrade from Oracle 10.2.0.4(10g) to 11.2.0.4(11g) - Using Manual upgrade
Fix : Oracle XML Database status INVALID in dba_registry during Upgrade 

Activity:

I started to upgrade one of my production database which oracle version is 10.2.0.4. I have to upgrade as Oracle 11.2.0.4. Following steps I have completed: ( Manual Upgrade)

1) Downloaded Oracle 11.2.04 version software and copy to a location. Sure about Oracle Homes. Start runinstaller. Select different Oracle Home location as like mentioned below.
   10g Home : /u01/app/oracle/product/10.2.0/db_1
   11g Home : /u01/app/oracle/product/11.2.0/db_1
Caution: Don't install Oracle 11g software to exiting home. There will be change of DB crash.
2) After runinstaller, Select the following:
    - New Oracle Home and Base ( as above)
    - Software Only
    - Enterprise / Standard Edition
3)  Rune the pre-upgrade tool: ( from 11g Home) -- must run
    - SQL> spool "upgrade_stat.log";
    - SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql
It will give what to set or do. Here are sample history of log :
    - Minimum undo tbs size=512MB
    - Increase share_pool size = 224MB
    - Any patch set to apply ( if lower version, like 10.2.1 to 11.2.0.4) etc.
4) If any patchset to apply, download and apply with following steps:
    a) Take a full RMAN backup and down the database.
    b) Install patch set to any other location and start runinstaller and continue up to END..
    c) export ORACLE_SID
    d) Start the DB in Upgrade mode.
    e) SQL> @?/rdbms/admin/catupgrd.sql
    f) SQL> @?/rdbms/admin/utlrp.sql
    g) Shut immediate and startup again.
5) After patch set apply, set the recommended values like undo, shared_pool etc.
6) Again run the pre-upgrade tool. No issue should come. "No update parameter changes required" Message should come.
7) Shut down 10g database.
8) Change the location of parameter file(pfile) to new 11g 'dbs'.( $ORACLE_HOME/dbs)
9) Edit the /etc/oratab file and set new 11g home.
10) Edit the bash profile in case of Linux .
11) Edit the parameter file and set 11g specific parameters like diag_dest, audit_dest etc.
12) Now we will upgrade:
     $ sqlplus / as sysdba
     SQL> startup upgrade;
     SQL> @?/rdbms/admin/catupgrd.sql
     SQL> @?/rdbms/admin/utlrp.sql
     SQL> shut immediate;
     SQL> startup;

These are my basic steps. All steps are over but when I executed "catupgrd.sql" and "utlrp.sql". I faced the following:
    a) catupgrd.sql completed very fast which should not and shows below errors.

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following error is generated if the pre-upgrade tool has not been
DOC>   run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC>   SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC>                       *
DOC>      ERROR at line 1:
DOC>      ORA-01722: invalid number
DOC>
DOC>     o Action:
DOC>       Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC>       Revert to the original oracle home and start the database.
DOC>       Run pre-upgrade tool against the database.
DOC>       Review and take appropriate actions based on the pre-upgrade
DOC>       output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#

    b) utlrp.sql shows the following errors at end:

warning for:

Warning: XDB now invalid, could not find xdbconfig

ORDIM registered 5 XML schemas.

The following XML schemas are not registered:

http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0

http://xmlns.oracle.com/ord/dicom/anonymity_1_0

http://xmlns.oracle.com/ord/dicom/attributeTag_1_0

http://xmlns.oracle.com/ord/dicom/constraint_1_0

http://xmlns.oracle.com/ord/dicom/datatype_1_0

http://xmlns.oracle.com/ord/dicom/manifest_1_0

http://xmlns.oracle.com/ord/dicom/mapping_1_0

http://xmlns.oracle.com/ord/dicom/mddatatype_1_0

http://xmlns.oracle.com/ord/dicom/metadata_1_0

http://xmlns.oracle.com/ord/dicom/orddicom_1_0

http://xmlns.oracle.com/ord/dicom/preference_1_0

http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0

http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0

http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0

ORDIM DICOM repository has 0 documents.

The following default DICOM repository documents are not installed:

ordcman.xml

ordcmcmc.xml

ordcmcmd.xml

ordcmct.xml

ordcmmp.xml

ordcmpf.xml

ordcmpv.xml

ordcmsd.xml

ordcmui.xml

Fix/ Solution:

Investigation:

1) Verify the registry after upgrade:

set line 120;

column COMP_NAME format a35;

column VERSION format a10;

column status format a10;

select COMP_NAME, VERSION,status from dba_registry;

COMP_NAME                           VERSION    STATUS

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

Oracle Enterprise Manager           11.2.0.4.0 VALID

OLAP Catalog                        11.2.0.4.0 VALID

Spatial                             11.2.0.4.0 VALID

Oracle Multimedia                   11.2.0.4.0 INVALID

Oracle XML Database                 11.2.0.4.0 INVALID

Oracle Text                         11.2.0.4.0 VALID

Oracle Expression Filter            11.2.0.4.0 VALID

Oracle Rules Manager                11.2.0.4.0 VALID

Oracle Workspace Manager            11.2.0.4.0 VALID

Oracle Database Catalog Views       11.2.0.4.0 VALID

Oracle Database Packages and Types  11.2.0.4.0 VALID

JServer JAVA Virtual Machine        11.2.0.4.0 VALID

Oracle XDK                          11.2.0.4.0 VALID

Oracle Database Java Packages       11.2.0.4.0 VALID

OLAP Analytic Workspace             11.2.0.4.0 VALID

Oracle OLAP API                     11.2.0.4.0 INVALID

Here few components are not upgraded. Focus on Oracle XML Database which is require for my environment.

Misc: Verify your optimizer values like below. If not matched, then change values using 'alter system set ...'

SQL> show parameter optimizer;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.3
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>

2) Find the output for below query:

SQL> select any_path from resource_view;

select any_path from resource_view

*

ERROR at line 1:

ORA-31000: Resource 'http://xmlns.oracle.com/xdb/acl.xsd' is not an XDB schema document

From the above two investigation, it is confirmed that pre-upgraded tool is not executed or during startup upgrade session may be canceled or terminated or some other issue may came.

Fix/ Solution:

To validate Oracle Multimedia you can check Oracle Support note: How To Reload Oracle Multimedia Related Information When XML Database (=XDB) Has Been Reinstalled [ID 965892.1]. To validate Oracle Multimedia we followed the following procedure.

sqlplus / as sysdba

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

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

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

Now no message ( as above seen) are not cumming. Now do the following also.

SQL> alter session set current_schema="ORDSYS";

SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/imxreg.sql;

SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/impbs.sql;

SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/impvs.sql;

SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/imtyb.sql;

SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/implb.sql;

SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/imxrepos.sql;

SQL> exit

sqlplus / as sysdba 

SQL> set serveroutput on

SQL> exec validate_ordim;

SQL> exit

Now all of the components are valid.

Conclusion:

Before you upgrade your database version using the manual method always check OS environment variables pointing to new ORACLE HOME.