jump to navigation

Upgrade Oracle to 11.2 including Standby and DG Broker 1 July 2010

Posted by David Alejo Marcos in Dataguard Broker, Oracle 11.2, Standby.
Tags: , , ,
trackback

This is a step by step guide of how I upgraded several databases from 11.1 to 11.2.0.1.

The problem:

We have a primary database and two standby managed by DG Broker. On this document, I will also include the steps to upgrade RDBMS DST (Daylight Savings Time) to 11g.

Standby databases are open in read only, the importance of this fact will be unveiled at the end of the article.

The server is running Linux 86-64 and the Oracle version was 11.1.0.7.

The solution:

The steps I followed were:

1.- Download and install Oracle following software:

Oracle 11.2:
linux.x64_11gR2_database_1of2.zip
linux.x64_11gR2_database_2of2.zip

Opatch:
p6880880_112000_Linux-x86-64.zip

PSU1:
p9352237_112010_Linux-x86-64.zip

2.- Check the previous backup run without problems or perform a full backup.

3.- Disable DG Broker:
connect to the primary box and execute:

Dgmgrl
Connect /
Disable fast_start failover
Disable configuration

4.- Connect to primary database and execute:
Sqlplus ‘/as sysdba’
Alter system set dg_broker_start=false scope=both;

5.- backup broker configuration on all 3 servers (primary and standby):
Sqlplus ‘/as sysdba’
show parameter dg_broker_config
copy those files (cp command) indicated on DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2.

We are now in a position to start the upgrade.

6.- Check all Oracle components are VALID on the primary database:

SET pagesize500
SET linesize 100

SELECT SUBSTR (comp_name, 1, 40) comp_name,
status,
SUBSTR (version, 1, 10) version
FROM dba_registry
ORDER BY comp_name;

SELECT SUBSTR (object_name, 1, 40) object_name,
SUBSTR (owner, 1, 15) owner,
object_type
FROM dba_objects
WHERE status='INVALID'
ORDER BY owner, object_type;

SELECT owner,
object_type,
COUNT (*)
FROM dba_objects
WHERE status='INVALID'
GROUP BY owner, object_type
ORDER BY owner, object_type;

if the queries above reports invalid objects, we can recompile them by executing:
sqlplus ‘/as sysdba’
@?/rdbms/admin/utlrp.sql

7.- Check we do not have duplicates on SYS or SYSTEM:

SELECT object_name, object_type
FROM dba_objects
WHERE object_name ||object_type IN
(SELECT object_name||object_type FROM dba_objects WHERE owner = 'SYS')
AND owner = 'SYSTEM'

If the query returns the following, it is fine:

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY

if there are other duplications, we can follow NOTE.1030426.6 HOW TO CLEAN UP DUPLICATE OBJECTS OWNED BY SYS AND SYSTEM

8.- pre-upgrade checks:
copy
$ORACLE_HOME/rdbms/admin/utlu112i.sql
$ORACLE_HOME/rdbms/admin/utltzuv2.sql

to /tmp (ORACLE_HOME should be set to your 11.2 ORACLE_HOME).

execute:
cd /tmp
sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off

and check the output.
Note 1.- We kept one of the standby databases down during the upgrade and the testing phase as rollback mechanism.

9.- execute bdupgdiag.sql
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

10.- Check all snapshot refreshes finished:
SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;

11.- Check there are not files that need media recovery:
SELECT * FROM v$recover_file;

12.- Check there are not files on backup mode:
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

13.- Resolve outstanding distributed transactions prior to the upgrade.
SQL> select * from dba_2pc_pending;

If this returns rows you should do the following:

SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

14.- Check standby databases are in sync

15.- Disable batch and crontab jobs.

16.- Check default tablespace for users SYS and SYSTEM:
SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');

If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace to SYSTEM by using the below command.

SQL> ALTER user SYS default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM;

17.- Check the aud$ table exists and that it is in the sys schema and in the system tablespace.
SQL> SELECT owner,tablespace_name
FROM dba_tables
WHERE table_name='AUD$';

18.- Stop listener on all three databases:
$ lsnrctl stop

19.- Stop dbconsole, agent and/or isqlplus:
$ emctl stop dbconsole
$ isqlplusctl stop
$ emctl stop agent

20.- Shutdown all three databases:
Primary:
$ sqlplus "/as sysdba"
SQL> shutdown immediate;

Standby:
(Althought it can be done using dgmgrl, for simplicity I have posted the “old style”):
$ sqlplus "/as sysdba"
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate;

21.- Create a new entry on /etc/oratab on all three servers:
Create entry on /etc/oratab:
db_name:/opt/oracle/product/11.2.0/dbhome_1:N
Comment out old entry:
#db_name:/opt/oracle/product/11.1.0/db_1:N

Execute:
. oratab
db_name

And check ORACLE_HOME and ORACLE_SID

21.1.- Copy Spfile (or init.ora file), orapw file, listener.ora, tnsnames.ora and sqlnet.ora from old ORACLE_HOME to new ORACLE_HOME.

21.2.- Modify ORACLE_HOME on the listener.ora under the SID_LIST_LISTENER to reflect the new ORACLE_HOME.

22.- Get ready to execute the upgrade:
On primary:
$ cd $ORACLE_HOME/rdbms/admin
$ lsnrctl start
$ sqlplus '/ as sysdba'
SQL> startup UPGRADE

On standby:
$ cd $ORACLE_HOME/rdbms/admin
$ lsnrctl start
$ sqlplus '/ as sysdba'
SQL> startup mount
SQL> alter database recover managed standby database disconnect;

23.- Execute the upgrade:
On primary:
$ sqlplus '/ as sysdba'
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off

check alert.log on standby to see archivelogs (or redo logs) are being applied.
Below is not true as broker is disabled. Put DB in recovery:
Alter database recover managed standby database disconnect;
Will give errors as listener is down. My decision is not to start listener until upgrade is done.

24.- Check logs:
Once the script has finished, check the log file to verify all is ok and run the Post-Upgrade Status tool

On primary:
$ sqlplus '/as sysdba'
SQL> STARTUP
SQL> @utlu112s.sql
SQL> @catuppst.sql
SQL> @utlrp.sql

25.- Change compatible parameter on primary and standby:
On primary:
$ sqlplus '/as sysdba'
SQL> alter system set compatible='11.2.0' scope=spfile;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

On Standby:
$ sqlplus "/as sysdba"
SQL> alter system set compatible='11.2.0' scope=spfile;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> alter database recover managed standby database cancel;

Now we have an upgraded database. The next steps are to upgrade the RDBMS DST:

26.- Check current RDBMS DST version and “DST UPGRADE STATUS”.
$ sqlplus '/as sysdba'
SQL> SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

DST_PRIMARY_TT_VERSION should match the value found when selecting

$ sqlplus '/as sysdba'
SQL> SELECT version FROM v$timezone_file;

27.- Check if there is any data that cannot be upgraded:
$ sqlplus '/as sysdba'
SQL> exec DBMS_DST.BEGIN_PREPARE(11);

28.- Check for prepare status
$ sqlplus '/as sysdba'
SQL> SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

The output should be:
— PROPERTY_NAME VALUE
— —————————— ——————————
— DST_PRIMARY_TT_VERSION
— DST_SECONDARY_TT_VERSION
— DST_UPGRADE_STATE PREPARE

29.- Truncate logging tables if they exist.
$ sqlplus '/as sysdba'
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
SQL> TRUNCATE TABLE sys.dst$affected_tables;
SQL> TRUNCATE TABLE sys.dst$error_table;

30.- Log affected data
$ sqlplus '/as sysdba'
SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/

31.- Check what tables have affected data that cannot be resolved automatically.
if this gives no rows then there is no problem at all

$ sqlplus '/as sysdba'
SQL> SELECT * FROM sys.dst$affected_tables;

IF previous select gives rows then you can see what kind of problem there are in those rows:

SELECT * FROM sys.dst$error_table;

32.- Check for different errors:
All “error_on_overlap_time” rows
SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

All “error_on_nonexisting_time” rows
SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

Check for all other possible problems
SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

33.- End prepare window, the rows above will stay in those tables.

EXEC DBMS_DST.END_PREPARE;

Check if this is ended

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

The output should be:
— PROPERTY_NAME VALUE
— —————————- ——————————
— DST_PRIMARY_TT_VERSION
— DST_SECONDARY_TT_VERSION 0
— DST_UPGRADE_STATE NONE

34.- Startup the database for the upgrade:
$sqlplus '/as sysdba'
SQL> shutdown immediate;
SQL> startup upgrade;

Purge the 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;

Start upgrade window
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);

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

The output should be similar to:
— PROPERTY_NAME VALUE
— ————————— ——————————
— DST_PRIMARY_TT_VERSION
— DST_SECONDARY_TT_VERSION
— DST_UPGRADE_STATE UPGRADE

Check what tables need to updated using

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

Restart the database
shutdown immediate
startup

36.- 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;
/

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;
/

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

The output should be something like:
— PROPERTY_NAME VALUE
— —————————- ——————————
— DST_PRIMARY_TT_VERSION
— DST_SECONDARY_TT_VERSION 0
— DST_UPGRADE_STATE NONE

SELECT * FROM v$timezone_file;

— needed output:
— FILENAME VERSION
— —————— ———-
— timezlrg_.dat

The last step is to configure broker:

38.- Connect to the primary and standby databases and execute:
Sqlplus '/as sysdba'
Alter system set dg_broker_start=true scope=both;

39.- Connect to the DG Broker and finish the configuration:
dgmgrl
Connect /
CREATE CONFIGURATION 'db_name_DG' AS PRIMARY DATABASE IS 'db_name' CONNECT IDENTIFIER IS db_name;
onserveroutputSQL> set

add standby databases:
ADD DATABASE 'stdby_name1' AS CONNECT IDENTIFIER IS stdby_name1 MAINTAINED AS
PHYSICAL;

ADD DATABASE 'stdby_name2' AS CONNECT IDENTIFIER IS stdby_name2 MAINTAINED AS
PHYSICAL;

ENABLE CONFIGURATION;

Connect to the standby database and re-start it:
sqlplus '/as sysdba'
SQL> shutdown immediate;
SQL> startup mount;

I will strongly recommend performing a full backup after the upgrade.

I kept the standby databases in mount state for some time (24 hours) as I found a problem with having the database in read only.

The error I was having on the primary database (alert log) was:

————————————————————-
Current log# 5 seq# 16477 mem# 0: /u04/oradata/DB_NAME/redo05.log
Thu Jul 01 13:12:22 2010

TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.xx.xxx)(PORT=nnn))
WARNING: inbound connection timed out (ORA-3136)

TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.xx.xxx)(PORT=nnnn))
WARNING: inbound connection timed out (ORA-3136)
Tue Jun 29 13:08:03 2010
Thread 1 advanced to log sequence 16053 (LGWR switch)
Current log# 1 seq# 16053 mem# 0: /u04/oradata/DB_NAME/redo01.log
Tue Jun 29 13:08:03 2010
***********************************************************************
Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Linux: Version 11.2.0.1.0 – Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 – Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 – Production
Time: 29-JUN-2010 13:08:03
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.xx.xx)(PORT=nnn))
WARNING: inbound connection timed out (ORA-3136)
————————————————————-

40.- Relink libobk.so if you are using NetBackup:

ln -sf /usr/openv/netbackup/bin/libobk.so64 /opt/oracle/product/11.2.0/dbhome_2/lib/libobk.so

to avoid:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on c01 channel at 01/15/2011 14:16:34
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

There is also the consideration of changing ORACLE_HOME on your backup scripts if you are using catalog, etc…

As always, comments are welcome.

Comments»

1. Don Seiler - 4 January 2011

Great guide. However you seem to be missing the steps where you should create a pfile from the old databases and copy the pfile and orapw file to the new ORACLE_HOME/dbs dir before starting up the instances under the new ORACLE_HOME dirs.

I assume you’ll get an error about no instance being defined if you try to startup with no pfile or spfile for that ORACLE_SID.

See http://download.oracle.com/docs/cd/E11882_01/server.112/e17222/upgrade.htm#i1011979

David Alejo Marcos - 4 January 2011

Thank you Don.

You are right, the step to copy spfile, orapw, tnsnames, sqlnet and listener was not detailed on the post.

I have amended it.

Regards,

David Alejo-Marcos.


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

%d bloggers like this: