Oracle database upgrade on RDS AWS and SPM baselines
A couple of days ago I saw a question posted online asking about how AWS upgrades an RDS Oracle database and what happens with SPM baselines. To be more precise whether the existing Oracle SPM baselines would survive the upgrade or not. There were some debates about that, and for me, it looked interesting enough to take a look under the hood even though I could guess the answer. I want to clarify that I do not work for AWS, have no access to any internal information and use the tools and method that are accessible to everyone. I created an AWS RDS instance with Oracle 11.2.0.4 v.6 using default settings:
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/01_init11ginstance.png?width=1052&height=360&name=01_init11ginstance.png)
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/02_init11g_options.png?width=1042&height=528&name=02_init11g_options.png)
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
orcl.inst01.us-east-1.rds.amazonaws.com:1521/orcl> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX
------------------------------ ------------------------------ ---------------------------------------- --- --- ---
SQL_afe447b28e378f0f SQL_PLAN_azt27qa73g3sg34c84014 select count(*) from scott.table_x where YES YES NO
txt1='c'
orcl.inst01.us-east-1.rds.amazonaws.com:1521/orcl> select count(*) from scott.table_x where txt1='c';
COUNT(*)
------------------
33
orcl.inst01.us-east-1.rds.amazonaws.com:1521/orcl> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 6w0wmda09cpam, child number 1
-------------------------------------
select count(*) from scott.table_x where txt1='c'
Plan hash value: 1529419959
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| TABLE_X | 33 | 66 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TXT1"='c')
Note
-----
- SQL plan baseline SQL_PLAN_azt27qa73g3sg34c84014 used for this statement
Without baseline the query is using an index range scan.
orcl.inst01.us-east-1.rds.amazonaws.com:1521/orcl> alter session set optimizer_use_sql_plan_baselines=false;
Session altered.
orcl.inst01.us-east-1.rds.amazonaws.com:1521/orcl> select count(*) from scott.table_x where txt1='c';
COUNT(*)
------------------
33
orcl.inst01.us-east-1.rds.amazonaws.com:1521/orcl> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 6w0wmda09cpam, child number 0
-------------------------------------
select count(*) from scott.table_x where txt1='c'
Plan hash value: 2333958128
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX RANGE SCAN| IND_TABLE_X_TXT1 | 33 | 66 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TXT1"='c')
So far so good, we have a baseline created on our database version 11.2.0.4 and we are ready for the upgrade. The upgrade in AWS RDS is easy and you need only to modify instance picking up an available version for the upgrade. By default you will be offered the latest compatible version. In my case it was 12.1.0.2 v9.
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/03_choose_version.png?width=1538&height=732&name=03_choose_version.png)
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/04_modify_instance.png?width=1760&height=760&name=04_modify_instance.png)
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/05_warning_before_upg.png?width=1704&height=778&name=05_warning_before_upg.png)
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/06_upgrading_instance.png?width=1504&height=374&name=06_upgrading_instance.png)
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/07_alarm_and_recent_events.png?width=1388&height=612&name=07_alarm_and_recent_events.png)
Completed: ALTER DATABASE MOUNT
Fri Dec 01 17:43:29 2017
alter database end backup
ORA-1142 signaled during: alter database end backup...
The next step looked like converting the spfile to the new format, but, again I was able to see it several times later.
Spfile /rdsdbbin/oracle/dbs/spfileORCL.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.
Fri Dec 01 17:44:07 2017
ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY;
Stopping background process QMNC
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Shutting down instance (immediate)
After that the instance is started again with enabled restricted session and following execution of the standard "utlrp.sql" script to recompile all invalid objects.
MMNL started with pid=16, OS id=17145
ALTER SYSTEM enable restricted session;
Fri Dec 01 17:44:24 2017
SERVER COMPONENT id=UTLRP_BGN: timestamp=2017-12-01 17:44:24
SERVER COMPONENT id=UTLRP_END: timestamp=2017-12-01 17:44:25
Fri Dec 01 17:44:29 2017
After that the database is going down again. All the previous actions were done when the database was started using the same 11.2.0.4 software. But now the database is started using the new 12.1.0.2 binaries:
NOTE: Using default ASM root directory ASM
NOTE: Cluster configuration type = NONE [2]
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
The database is mounted and opened in the upgrade mode:
Fri Dec 01 17:47:55 2017
ALTER DATABASE MOUNT
Fri Dec 01 17:48:00 2017
Control file expanded from 478 to 498 blocks for upgrade.
Fri Dec 01 17:48:00 2017
...
Fri Dec 01 17:48:00 2017
Database mounted in Exclusive Mode
...
Fri Dec 01 17:48:00 2017
alter database open upgrade
...
Fri Dec 01 17:48:05 2017
ALTER SYSTEM enable restricted session;
Fri Dec 01 17:48:05 2017
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
...
Fri Dec 01 17:48:05 2017
CJQ0 started with pid=27, OS id=18102
Completed: alter database open upgrade
Fri Dec 01 17:48:13 2017
As you can see, everything looks pretty much normal for an in-place upgrade of an Oracle database. The following output just confirm the execution of the standard upgrade scripts.
Fri Dec 01 17:56:26 2017
SERVER COMPONENT id=CATPROC: timestamp=2017-12-01 17:56:26
Fri Dec 01 17:56:29 2017
SERVER COMPONENT id=RDBMS: status=UPGRADED, version=12.1.0.2.0, timestamp=2017-12-01 17:56:29
...
Fri Dec 01 17:57:04 2017
SERVER COMPONENT id=CONTEXT: status=VALID, version=12.1.0.2.0, timestamp=2017-12-01 17:57:04
...
Fri Dec 01 17:57:58 2017
SERVER COMPONENT id=XDB: status=VALID, version=12.1.0.2.0, timestamp=2017-12-01 17:57:58
...
SERVER COMPONENT id=CATREQ_BGN: timestamp=2017-12-01 17:59:13
SERVER COMPONENT id=CATREQ_END: timestamp=2017-12-01 17:59:19
SERVER ACTION=UPGRADE id=: Upgraded from 11.2.0.4.0
SERVER COMPONENT id=ACTIONS_END: timestamp=2017-12-01 17:59:20
SERVER COMPONENT id=UPGRD_END: timestamp=2017-12-01 17:59:20
...
After the first upgrade phase the instance rebooted to run the next set of scripts:
Completed: ALTER DATABASE MOUNT
Fri Dec 01 17:59:57 2017
ALTER DATABASE OPEN MIGRATE
Fri Dec 01 17:59:57 2017
...
Fri Dec 01 18:00:00 2017
CJQ0 started with pid=27, OS id=23176
Completed: ALTER DATABASE OPEN MIGRATE
[rds-upgrade]: startupupg.sql
Fri Dec 01 18:00:01 2017
[rds-upgrade]: starting 3.upgrade.sql
Fri Dec 01 18:00:01 2017
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
[rds-upgrade]: finished 3.upgrade.sql
Fri Dec 01 18:00:02 2017
[rds-upgrade]: starting 4.upgrade.sql
Fri Dec 01 18:00:02 2017
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
[rds-upgrade]: finished 4.upgrade.sql
Fri Dec 01 18:00:10 2017
[rds-upgrade]: starting 5.upgrade.sql
[rds-upgrade]: finished 5.upgrade.sql
Fri Dec 01 18:00:11 2017
[rds-upgrade]: starting 6.upgrade.sql
Fri Dec 01 18:00:11 2017
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
[rds-upgrade]: finished 6.upgrade.sql
Fri Dec 01 18:00:12 2017
[rds-upgrade]: starting 7.upgrade.sql
[rds-upgrade]: finished 7.upgrade.sql
...
After that the database shut down to start back in normal mode. The compatibility is going to be changed as well.
...
Fri Dec 01 18:05:03 2017
ALERT: Compatibility of the database is changed from 11.2.0.0.0 to 12.0.0.0.0.
Increased the record size of controlfile section 12 to 96 bytes
Control file expanded from 498 blocks to 502 blocks
...
The database is going to be backed up again to have the first post upgrade backup.
Fri Dec 01 18:10:31 2017
alter database backup controlfile to '/rdsdbdata/tmp/backup_control_file' reuse
Completed: alter database backup controlfile to '/rdsdbdata/tmp/backup_control_file' reuse
alter database begin backup
Completed: alter database begin backup
alter database end backup
Completed: alter database end backup
The final backup is finishing the upgrade procedure and the database is open and available for tests. We could see that the upgrade was more or less similar to what we would do during a manual upgrade of an Oracle database. So, we should expect the same behavior and, returning to the original question, the SPM baselines should stay and work as before. Let's test it:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
orcl.inst01.us-east-1.rds.amazonaws.com:1521/orcl> select count(*) from scott.table_x where txt1='c';
COUNT(*)
------------------
33
orcl.inst01.us-east-1.rds.amazonaws.com:1521/orcl> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 6w0wmda09cpam, child number 1
-------------------------------------
select count(*) from scott.table_x where txt1='c'
Plan hash value: 1529419959
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| TABLE_X | 33 | 66 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TXT1"='c')
Note
-----
- SQL plan baseline SQL_PLAN_azt27qa73g3sg34c84014 used for this statement
As you can see we have the same baseline and the same execution plan. Here is a short summary. The upgrade for an AWS RDS Oracle database is not different in general than an upgrade using Oracle standard approach and we may expect the standard behavior for our database. And, speaking about SPM baselines, they have to stay and provide the expected plan stability. I hope that this small exercise may help a few people save the time and effort it takes to dig through documentation searching for an answer.
Share this
You May Also Like
These Related Stories
Oracle 18c Non-CDB databases in the Oracle Database Cloud Service (ODBCS)
Oracle 18c Non-CDB databases in the Oracle Database Cloud Service (ODBCS)
Apr 6, 2018
7
min read
EBS: R12 Default OACORE Memory Settings Are Not Enough
EBS: R12 Default OACORE Memory Settings Are Not Enough
Dec 10, 2012
2
min read
Dipping Your Toes Into Building an Analytics Platform on Google Cloud Platform
![](https://www.pythian.com/hubfs/Imported_Blog_Media/John-Laham-post-1-e1515094512317.png)
Dipping Your Toes Into Building an Analytics Platform on Google Cloud Platform
Sep 17, 2020
7
min read
No Comments Yet
Let us know what you think