Upgrade from 10g to 11g Release 2 using the Database Upgrade Assistant on Windows Environment with dataguard

Upgrade from 10g to 11g Release 2 using the Database Upgrade Assistant on Windows Environment

Pre Upgrade Steps

1. Take full backup of the database

2. TRUNCATE the SYS.AUD$ Table

Take backup of audit table if needed:

#create table aud$_backup as select * from aud$;

#truncate table sys.aud$;

3. Purge the Recycle Bin

# PURGE DBA_RECYCLEBIN;

4. Make a note of the Count of INVALID Objects

you can also try utlrp.sql for compile all objects

# @?/rdbms/admin/utlrp.sql

# spool c:\invalidobects.txt
# Select  owner,object_name,object_type from dba_objects Where status=’INVALID’;
# spool off

5. Gather Dictionary Statistics

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

6. Dataguard : Check log shipping status

#select destination,status from v$archive_dest_status where dest_id <3;

Check archive gap in Dataguard Environment
——————————————
SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.’),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,’.’)-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’
and resetlogs_id in ( select max(resetlogs_id) from v$archived_log)
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED ,TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
and resetlogs_id in ( select max(resetlogs_id) from v$archived_log)
);

Disable Log Shipping on the Primary Database
SQL> alter system set log_archive_dest_state_2=’DEFER’;

On the Standby machine create a pfile if one does not exist
SQL>  create pfile from spfile;

Now Shutdown the 10g Standby Database
SQL> shutdown immediate;

7. Configure Listener for standby:
If the 10g listener is running then stop it
D:\oracle\product\10.2.0\db\BIN>lsnrctl stop

Now create the 11g Listener on the Standby machine
NETCA for Oracle Home of 11g

Delete the service of Orace 10g from standby machine
D:\oracle\product\10.2.0\db\BIN>oradim -delete -sid ORCL
Instance deleted.

Note- Change to the 11g Release Home to create the 11g Service
D:\oracle\product\11.2.0\db\BIN>oradim -new -sid ORCL -startmode AUTO
Instance created.

8. Copy Paster the pfile and Password file from oracle 10g to Oracle 11g

edit the following file:
*.diagnostic_dest:’D:\oracle’

Note – also remove the parameter standby_archive_dest as it has been deprecated in 11g

9. Start the 11g Oracle standby database:

SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.

SQL> alter database mount;
Database altered.

Start the managed recovery on the 11g Standby database
SQL> recover managed standby database disconnect from session;
Media recovery complete.

Start log shipping again on Primary
SQL> alter system set log_archive_dest_state_2=’ENABLE’;
System altered.

Note-
Stop the 10g listener and create a new 11g listener on the same port –
the DBUA will also configure Enterprise Manager which requires the 11g listener to be running

10. Launch DBUA from Start Programs > 11g Oracle Home > Configuration and Migration Tools  On Primary Site.

Enter the SYS password and click Next
We can make a note of the Warning messages displayed, but ignore the same.
Click Yes
Note – DO NOT turn off Archiving during the upgrade process as the redo needs to be shipped to the standby site.
Select “Do Not Move Database Files as part of the Upgrade”.
Click Next
we are not using Flashback Database we can ignore the option to specify a Flash Recovery Area.
Click Next
Enter the password for the DBSNMP and SYSMAN users
Click Next
Summary Page of Database Upgradation
Click Finish

Note:While the upgrade is in progress, we can check the redo apply activity as it
 is being recorded in the alert log of both the Primary as well as Standby database.

Note – the DBUA displays an error message regarding the Oracle Workspace Manager – we can ignore this error and click the Ignore button.
ORA-00942: table or view does not exist ORA-06512: “WMSYS.OWM_MIG_PKG”

Post Upgrade Tasks
————————-
Restore the AUD$ table back from backup table

SQL> insert into SYS.AUD$ select * from SYS.AUD$_BACKUP;
insert into SYS.AUD$
                *
ERROR at line 1:
ORA-00947: not enough values.

SQL> alter table aud$_backup add OBJ$EDITION varchar2(30);

SQL> insert into SYS.AUD$ select * from SYS.AUD$_BACKUP;

Make the Standby database READ ONLY

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database open read only;
Database altered.

SQL> col comp_name format a50
SQL> set linesize 120
SQL> select comp_name,version,status from dba_registry

Start the Managed recovery again on the Standby Database
SQL> recover managed standby database disconnect from session;
Media recovery complete.

Change the COMPATIBLE Parameter
COMPATIBLE parameter to ’11.2.0’.

check the invalid objects

select owner,object_name,object_type from dba_objects where status=’INVALID’

Run the Post Upgrade Status Tool

SQL> @?/rdbms/admin/utlu112s.sql;

“Post-Upgrade Status Script utlu112s.sql Fails with ORA-942 [ID 1051991.1”
Recreate the EM Database Control Manually for 11g
C:\Documents and Settings\IMODBA>emca -config dbcontrol db -repos recreate

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.