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:
And parameters group:
To verify the baseline behavior I have created a sample schema SCOTT with a table TABLE_X for tests. The created baseline is using full table scan for the query.
Check option to apply the changes immediately, pressing "Continue" and "Modify instance" buttons.
Before pressing the "Modify instance" button you are going to be warned about potential risk of the upgrade.
After that, your instance is going to be switched to the upgrade mode until end of the upgrade process which will go through several stages.
Of course, we don't have direct access to the OS and the scripts used for the upgrade but we have an AWS log for the actions and also have access to the alert log for the database. That information is going to be sufficient enough to give us a high overview of the process. The first step is to take backup of your database before the upgrade. The name for backup will be something like "rds:preupgrade-orcl-11-2-0-4-v6-to-12-1-0-2-v9-...". It has the source and target versions along with the instance name and a clear tag "preupgrade". The instance will be shut down and started up a couple of times.
When we look to the alert log for the database we can see that the first action reported in the alert log has been about 5-7 min later than the time when we've started the upgrade. Most likely that time was spent to prepare for the upgrade gathering information and preparing parameters for the scripts. The first reboot was starting the database in exclusive mode. What was interesting that I saw the "end backup" in the alert log but no "begin backup". It looked like the "end backup" command was just a part of standard procedure to start an Oracle RDS instance. Later it was confirmed having the same for all subsequential restarts.
And parameters group:
To verify the baseline behavior I have created a sample schema SCOTT with a table TABLE_X for tests. The created baseline is using full table scan for the query.
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.
Check option to apply the changes immediately, pressing "Continue" and "Modify instance" buttons.
Before pressing the "Modify instance" button you are going to be warned about potential risk of the upgrade.
After that, your instance is going to be switched to the upgrade mode until end of the upgrade process which will go through several stages.
Of course, we don't have direct access to the OS and the scripts used for the upgrade but we have an AWS log for the actions and also have access to the alert log for the database. That information is going to be sufficient enough to give us a high overview of the process. The first step is to take backup of your database before the upgrade. The name for backup will be something like "rds:preupgrade-orcl-11-2-0-4-v6-to-12-1-0-2-v9-...". It has the source and target versions along with the instance name and a clear tag "preupgrade". The instance will be shut down and started up a couple of times.
When we look to the alert log for the database we can see that the first action reported in the alert log has been about 5-7 min later than the time when we've started the upgrade. Most likely that time was spent to prepare for the upgrade gathering information and preparing parameters for the scripts. The first reboot was starting the database in exclusive mode. What was interesting that I saw the "end backup" in the alert log but no "begin backup". It looked like the "end backup" command was just a part of standard procedure to start an Oracle RDS instance. Later it was confirmed having the same for all subsequential restarts.
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.
