Migrating to 11gR2 – with an Existing Logical Standby

Upgrading Oracle Database with a Logical Standby Database In Place [ID 437276.1]

  • Migrating from 10.2.0.4 to 11.2.0.2
  • Primary database is approximately 80 GB
  • Binary software has already been installed on all nodes
  • RMAN catalog upgraded
  • Oracle listeners already migrated to newer version.
  • Clients (sqlplus, procobol, pro C, odbc) are already at 11gR2.

This is all part of the Step-Ordered Approach to Migrating, see the following page for a case study.

These are not all of the steps, just an overview. Your mileage may vary. Please test in a non-production environment before implementing any information found here.

1.Deferred log shipping to the logical standby before first upgrading the primary database.

PRIMARY>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=DEFER SCOPE=BOTH;

2. Stop SQL Apply on the standby database:

REPT> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

3. Upgrade the primary database to the new release, these steps came from the case study page.

a.  run /tmp/utlu112i.sql, run /u01/app/oracle/clones/droplinks.sql, shutdown immediate;, exit session.

b. Double check the 11.2.0.2 initSID.ora is in place. New clean session, edit /etc/oratab, set to 11.2.x home, edit locally-modified files with changed environmental variables.

c. SQL>startup upgrade, @?/rdbms/admin/catupgrd, startup @?/rdbms/admin/catuppst, @?/rdbms/admin/utlrp

d. after11gupgrade.sql – this recreated the dropped links, dropped the password-protected roles.

e. Create password file

5. Create Network ACLS

6. Upgrade Oracle Wallet – start owm &, open existing wallet, make sure auto login is checked, save as in the same location.

4. Upgrade the logical standby database.

a. run /tmp/utlu112i.sql, run /u01/app/oracle/clones/droplinks.sql, shutdown immediate, exit session.

b. Double check the 11.2.0.2 initSID.ora is in place. New clean session, edit /etc/oratab, set to 11.2.x home, edit locally-modified files with changed environmental variables. Modified initSID.ora in the 11.2.x home, created spfile.

c. SQL>startup upgrade, @?/rdbms/admin/catupgrd, startup, @?/rdbms/admin/utlu112s.sql @?/rdbms/admin/catuppst, @?/rdbms/admin/utlrp

d. after11gupgrade.sql — this recreated the dropped links, removed any local password-protected roles.

e. Create password file

f. On the upgraded logical standby database, restart the database and restart SQL Apply

LOGICAL>STARTUP

LOGICAL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

5. Enable archiving to the upgraded logical standby database:

PRIMARY>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;

6. Monitor the progress of the logical SQL APPLY process:

LOGICAL> SELECT APPLIED_SCN, APPLIED_TIME, READ_SCN, READ_TIME,  NEWEST_SCN, NEWEST_TIME  FROM DBA_LOGSTDBY_PROGRESS;

LOGICAL>SELECT PID, TYPE, STATUS, HIGH_SCN FROM V$LOGSTDBY;

9. Instantiate any previously skipped tables that have caused the SQL APPLY process to stop in the past. 11gR2 has added additional supported datatypes for the logical standby database. See the following MOS note: Step by Step Approach of How to Instantiate a Table in Logical Standby Database [ID 842160.1]

LOGICAL>alter database stop logical standby apply;

LOGICAL> select statement_opt,owner,name from dba_logstdby_skip where owner=’&SCHEMANAME’;

LOGICAL> exec dbms_logstdby.unskip(‘DML’, ‘SCHEMANAME’, ‘TABLENAME”);

LOGICAL>exec dbms_logstdby.instantiate_table(‘SCHEMANAME’,’TABLENAME’,’LINKNAME”);

LOGICAL>LOGICAL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Advertisements

About April C Sims

Oracle DBA for over a decade...enough said.
This entry was posted in Uncategorized and tagged , , , , , , , , . Bookmark the permalink.

One Response to Migrating to 11gR2 – with an Existing Logical Standby

  1. April C Sims says:

    What is your comment?

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s