Infrastructure at your Service

Franck Pachot

OCM 12c preparation: RAT in multitenant

By Franck Pachot

.
I have several customers that have cases where Real Application Testing can be interesting, but they don’t use it because it’s an expensive option. Which is why it’s probably the topic listed for OCM 12c exam where I’ve the less experience. And I don’t even know at which level (CDB or PDB) it has to be run in multitenant. So I’ve tested it and came to a surprise.

In Enterprise Manager, when you select Database Replay from a PDB:

CaptureReplay002

It seems that you go back at CDB level:

CaptureReplay003

Capture

From there I’ve run a small capture, using the most simple and the default settings from the OEM wizard, and got a capture that has no reference to the PDB:

CaptureReplay004

Don’t hesitate to comment here because that looks strange for me that I cannot capture at PDB level.

Replay

So while the capture was running, I’ve created the SCOTT schema with utlsampl.sql and I’ve raised all salaries in EMP.
Now, to replay in the same state, I’ve re-created the SCOTT schema.

And once again, using all defaults in OEM wizard. But the replay had 2 errors:

CaptureReplayError

There’s probably a way to see the statements, but first I check the error messages:


[[email protected] ~]$ oerr ora 01918
01918, 00000, "user '%s' does not exist"
// *Cause:  User does not exist in the system.
// *Action: Verify the user name is correct.
[[email protected] ~]$ oerr ora 65049
65049, 00000, "creation of local user or role is not allowed in CDB$ROOT"
// *Cause:   An attempt was made to create a local user or role in CDB$ROOT.
// *Action:  If trying to create a common user or role, specify CONTAINER=ALL.
//

As I know that the capture, running utlsampl.sql, did DROP USER SCOTT and then CREATE USER SCOTT, I can imagine that the replay was running on the CDB$ROOT.

I did the replay again, and the reason is clear. Because I’m at CDB level de defaut connection string for replay clients connects to the CDB$ROOT:

CaptureReplayErrorCDB

Let’s change it to PDB service name:

CaptureReplayErrorPDB

There I thought that the workload replay client had to connect to PDB but:


[email protected] ~]$ wrc system/[email protected]//vm111/PDB replaydir=/tmp/replay
 
Workload Replay Client: Release 12.1.0.2.0 - Production on Sun Dec 20 14:55:02 2015
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 
(wrc_main_6062.trc) ORA-15554: cannot start workload replay client because the database server is not in PREPARE mode

then I connect to the CDB$ROOT and everything is ok:


[[email protected] ~]$ wrc system/oracle replaydir=/tmp/replay
 
Workload Replay Client: Release 12.1.0.2.0 - Production on Sun Dec 20 14:55:17 2015
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 
Wait for the replay to start (14:55:17)
Replay client 1 started (14:55:39)

With this configuration, the replay had no errors: SCOTT recreated and salaries updated.

Conclusion

My conclusion here is that everything about RAT is done at CDB level (but you can filter to capture only what happens on one PDB).

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist