We will now see a step-by-step approach to upgrade a database from 11.2.0.1 to 11.2.0.3.
SQL> select * from v$version; BANNER -----------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE
The COMPATIBLE
initialization parameter should be same as the software release version. Once we upgrade to the new release and after all the post checks, we can change the compatible parameter value.
SQL> show parameter compatible NAME TYPE VALUE ----------------- -------- ------------- compatible string 11.2.0.0.0
SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------ -------- ------- dg_broker_start boolean FALSE
SQL> select db_unique_name,flashback_on from v$database; DB_UNIQUE_NAME FLASHBACK_ON --------------- ------------------ INDIA_UN NO SQL> alter database flashback on; Database altered. SQL> select db_unique_name, flashback_on from v$database; DB_UNIQUE_NAME FLASHBACK_ON --------------- ------------------ turkey_un YES
On the alert log, you will get the following:
Sun Dec 30 21:42:53 2012 alter database flashback on Starting background process RVWR Sun Dec 30 21:42:57 2012 RVWR started with pid=20, OS id=21651 Sun Dec 30 21:43:18 2012 Allocated 3981120 bytes in shared pool for flashback generation buffer Sun Dec 30 21:43:33 2012 Flashback Database Enabled at SCN 955828 Completed: alter database flashback on
In 11gR2, we no longer need to restart database to the mount
state in order to enable or disable flashback
on a primary database. Therefore, we can enable/disable flashback
when the database is in the read-write mode. However, we can't enable or disable flashback on a standby database when MRP is running (ORA-01153: an incompatible media recovery is active
). In order to perform this on the standby database, we must stop Redo Apply.
SQL> create restore point Rolling_Upgrade_Turkey guarantee flashback database;
Restore point created.
SQL> select name,guarantee_flashback_database,scn from v$restore_point;
NAME GUA SCN
------------------------------ --- ----------
ROLLING_UPGRADE_TURKEY YES 972018
On the alert log, you will get the following:
Sun Dec 30 22:09:19 2012 Created guaranteed restore point ROLLING_UPGRADE_TURKEY
Now create a guaranteed restore point on the standby database. In order to create a restore point, we must cancel the recovery.
SQL> alter database recover managed standby database cancel; Database altered. SQL> create restore point Rolling_Upgrade_India guarantee flashback database; Restore point created.
SQL> execute dbms_logstdby.build; PL/SQL procedure successfully completed.
In the alert log, add the following:
SUPLOG: unique = ON, foreign key = OFF, all column = OFF SUPLOG: procedural replication = OFF Completed: alter database add supplemental log data (primary key, unique index) columns alter database add supplemental log data for procedural replication SUPLOG: Previous supplemental logging attributes at scn = 998811
KEEP IDENTITY
clause so that the database name and DBID remain the same as those in the primary database.SQL> alter database recover managed standby database cancel; Database altered. SQL> shutdown immediate ORACLE instance shut down. SQL> startup mount exclusive; Database mounted. SQL> alter database recover to logical standby keep identity; Database altered.
On the alert log, you will get the following:
Online log /u01/app/oracle/oradata/orcl/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 1003598 Mon Dec 31 00:49:04 2012 Setting recovery target incarnation to 3 RECOVER TO LOGICAL STANDBY: Complete - Database mounted as logical standby Completed: alter database recover to logical standby keep identity
SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database open; Database altered. SQL> select database_role from v$database; DATABASE_ROLE ---------------- LOGICAL STANDBY
SQL> alter database start logical standby apply immediate; Database altered.
On the alert log, you will get the following:
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_DICTIONARY$ have been marked unusable Indexes of table SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable SQL> SELECT NAME, VALUE, TIME_COMPUTED FROM V$DATAGUARD_STATS WHERE NAME='transport lag'; NAME VALUE TIME_COMPUTED ------------- --------------- ------------------------------ transport lag +00 00:00:00 12/31/2012 00:59:25
If any active DDLs/DMLs are in progress, you can monitor them using v$logstdby_state
.
SQL> select state from v$logstdby_state; STATE ------------- APPLYING SQL> / STATE ------------- IDLE
defer
.SQL> alter system set log_archive_dest_state_2='defer'; System altered. SQL> select dest_id,status from v$archive_dest where dest_id=2; DEST_ID STATUS --------------- --------- 2 DEFERRED
Stop SQL Apply from the logical standby database.
SQL> alter database stop logical standby apply; Database altered.
On the alert log, you will get the following:
Mon Dec 31 01:06:16 2012 LOGSTDBY status: ORA-16128: User initiated stop apply successfully completed Completed: alter database stop logical standby apply
SQL> create restore point Rolling_Upgrade_India2 guarantee flashback database; Restore point created. SQL> select name from v$restore_point; NAME ----------------------------------- ROLLING_UPGRADE_INDIA ROLLING_UPGRADE_INDIA2
ORACLE_HOME
locations of 11.2.0.3 and upgrade the database after setting the environment variables to point to the new home, 11.2.0.3. Then run the upgrade scripts as shown in the following code:[oracle@oracle-stby ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 31 01:14:12 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade Database mounted. Database opened. SQL> @?/rdbms/admin/catupgrd.sql DOC>############################################################## DOC>############################################################## DOC> DOC> The first time this script is run, there should be no error messages DOC> generated; all normal upgrade error messages are suppressed. ............. SQL> REM END OF CATUPGRD.SQL SQL> SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql. SQL> REM This forces user to start a new sqlplus session in order SQL> REM to connect to the upgraded db. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
On the alert log, you will find the following:
Database Characterset is WE8MSWIN1252 Updating 11.2.0.1.0 NLS parameters in sys.props$ -- adding 11.2.0.3.0 NLS parameters. ............. Mon Dec 31 01:30:10 2012 SERVER COMPONENT id=CATPROC: timestamp=2012-12-31 01:30:10 SERVER COMPONENT id=RDBMS: status=VALID, version=11.2.0.3.0, timestamp=2012-12-31 01:30:15
DBA_REGISRY
.SQL> select comp_name, status from dba_registry; COMP_NAME STATUS ---------------------------------------- ----------- OWB VALID Oracle Application Express VALID Oracle Enterprise Manager VALID OLAP Catalog VALID Spatial VALID Oracle Multimedia VALID
Enable redo transport by running the following statement on the primary database.
SQL> alter system set log_archive_dest_state_2='enable'; System altered.
SQL> select count(*) from packt; COUNT(*) --------------- 14 SQL> insert into packt select * from packt; 14 rows created. SQL> commit; Commit complete. SQL> select count(*) from packt; COUNT(*) --------------- 28
packt
.SQL> alter database start logical standby apply immediate; Database altered. SQL> select db_unique_name,database_role from v$database; DB_UNIQUE_NAME DATABASE_ROLE -------------------- ---------------- INDIA_UN LOGICAL STANDBY SQL> select count(*) from scott.packt; COUNT(*) ---------- 28
First issue the switchover
command from the primary database.
SQL> alter database commit to switchover to logical standby; Database altered. SQL> select db_unique_name,switchover_status,open_mode from v$database; DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE --------------- -------------------- -------------------- turkey_un NOT ALLOWED READ WRITE
On the alert log, you will find the following:
Mon Dec 31 02:22:20 2012 NSA2 started with pid=26, OS id=12227 Beginning log switch checkpoint up to RBA [0x34.2.10], SCN: 1009787 ........... LOGSTDBY: Switchover complete (TURKEY) LOGSTDBY: enabling scheduler job queue processes. JOBQ: re-enabling CJQ0 Completed: alter database commit to switchover to logical standby
switchover
command from the upgraded logical standby database:SQL> select db_unique_name,switchover_status,open_mode from v$database; DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE -------------------- -------------------- -------------------- INDIA_UN TO PRIMARY READ WRITE SQL> alter database commit to switchover to logical primary; Database altered. SQL> select db_unique_name,switchover_status,open_mode from v$database; DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE -------------------- -------------------- -------------------- INDIA_UN NOT ALLOWED READ WRITE
SQL> alter system set log_archive_dest_state_2='defer'; System altered.
Now flash back to the restore point before the upgrade.
SQL> select db_unique_name,database_role from v$database; DB_UNIQUE_NAME DATABASE_ROLE --------------- ---------------- turkey_un LOGICAL STANDBY SQL> shutdown immediate ORACLE instance shut down. SQL> startup mount Database mounted. SQL> select name from v$restore_point; NAME ------------------------------ ROLLING_UPGRADE_TURKEY SQL> flashback database to restore point ROLLING_UPGRADE_TURKEY; Flashback complete. SQL> shutdown immediate ORACLE instance shut down.
PFILE/SPFILE
, the password file, and network configuration files to the new installed ORACLE_HOME
location and start the database in the MOUNT
status.[oracle@oracle-primary ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 31 02:43:45 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount Database mounted. SQL> alter database convert to physical standby; Database altered.
Shut down instance
and start up in the MOUNT
status using the following code:
SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount Database mounted.
SQL> alter system set log_archive_dest_state_2='enable'; System altered.
Now start Redo Apply on the standby database to apply all the redo of the upgrade script.
SQL> alter database recover managed standby database using current logfile disconnect; Database altered.
On the alert log, you will see the following:
Mon Dec 31 02:51:28 2012 MRP0 started with pid=26, OS id=13970 MRP0: Background Managed Standby Recovery process started (TURKEY) Serial Media Recovery started Managed Standby Recovery starting Real Time Apply ........... ORA-19906: recovery target incarnation changed during recovery Managed Standby Recovery not using Real Time Apply Completed: alter database recover managed standby database using current logfile disconnect
The previous errors are expected; if any archives are unable to fetch, copy the archive logs from the primary database and then catalogue them with the database using the RMAN
command catalog start with 'arch location'
.
Mon Dec 31 04:08:50 2012 alter database recover managed standby database disconnect from session Attempt to start background Managed Standby Recovery process (TURKEY) .................... Media Recovery Log /u01/app/oracle/flash_recovery_area/TURKEY_UN/archivelog/2012_12_31/1_55_803436544.dbf Media Recovery Log /u01/app/oracle/flash_recovery_area/TURKEY_UN/archivelog/2012_12_31/1_56_803436544.dbf Media Recovery Waiting for thread 1 sequence 57
On the primary database, add the following:
SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 56
On the standby database, add the following:
SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 56
Verify once if all the components of the registry are valid.
SQL> @?/rdbms/admin/utlu112s.sql
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Database 11.2 Post-Upgrade Status Tool 12-31-2012 04:18:17
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.3.0 .................
Gathering Statistics
. 00:03:27
Total Upgrade Time: 00:46:51
PL/SQL procedure successfully completed.
We have seen how to perform a rolling upgrade using the transient logical standby database with very little downtime. With this method, 96 percent of upgrade downtime can be avoided.