Recently, I was working to simulate a production workload on a new server and analyze the potential impact of changes made to the Oracle Database. Armed with my favorite testing tool from Oracle, Real Application Testing (RAT), everything seemed to progress smoothly until I encountered a roadblock during the replay phase on the target system via workload clients (wrc replay clients). This incident occurred while conducting workload replay on Oracle Database 21c Release 3 within one of the Pluggable Databases (PDB).
Yes, you read it right – replaying workload within a PDB! Since Oracle Release 19c, a significant enhancement allows capturing and replaying workloads at the individual PDB level. In the past, this capability was confined to capturing and replaying multitenant databases at the root multitenant container database (CDB) level. If you’re curious about this enhancement, I delved into it in a previous blog post, providing comprehensive details and explanations. You can explore it here: Real Application Testing for Capture and Replay in a PDB: A Great Addition Made in 19c.
Now, before we delve into the intricacies of the encountered issue, let’s acknowledge an observation. There were instances where, amidst the excitement of working with new options, we totally forgot to enable PREPARE mode for REPLAY within the PDB, especially when working with versions 19c & above. It might seem trivial, but it’s a common oversight that can lead to unexpected hurdles. In such cases, the CDB might be in PREPARE mode, but the oversight within the PDB can be the root cause.
Assuming you’ve ensured that PREPARE mode is correctly set up for your PDB, and you’re still facing issues, let’s dive into troubleshooting this error. The next steps involve a meticulous investigation to understand what’s happening behind the scenes.
SQL> select id, name, status from dba_workload_replays;
ID NAME STATUS
---------- ---------------------------------------- ----------------------------------------
1 REPLAY_MYDB12_3HRS_252024 PREPARE
[oracle@testbox-fatdba ~]$ wrc rattest/rattest@pdb1 mode=replay replaydir=/home/oracle/replaydir
Workload Replay Client: Release 21.3.0.0.0 - Production on Sat Feb 24 12:51:50 2024
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
(wrc_main_507216.trc) ORA-15554: cannot start workload replay client because the database server is not in PREPARE mode
Lets see if we have to redo of prepare mode solves the problem.
SQL> execute DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(synchronization => 'SCN');
BEGIN DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(synchronization => 'SCN'); END;
*
ERROR at line 1:
ORA-20223: Invalid input. Database already in PREPARE mode.
ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY_I", line 5519
ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 153
ORA-06512: at line 1
No, lets try and create a separate user and assign all required permissions to it and see if that solves the issue.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL>
SQL> create user appuser identified by oracle90;
User created.
SQL> grant ratuser to appuser;
Grant succeeded.
SQL>
SQL> grant create session to appuser;
Grant succeeded.
SQL>
SQL> Create role ratuser;
Role created.
SQL> Grant create session to ratuser;
Grant succeeded.
SQL> Grant execute on dbms_workload_repository to ratuser;
Grant succeeded.
SQL> Grant administer SQL tuning set to ratuser;
Grant succeeded.
SQL> Grant create job to ratuser;
Grant succeeded.
SQL> Grant execute on dbms_workload_capture to ratuser;
Grant succeeded.
SQL> Grant execute on dbms_workload_replay to ratuser;
Grant succeeded.
SQL> Grant oem_advisor to ratuser;
Grant succeeded.
SQL> Grant create session to ratuser;
Grant succeeded.
SQL> Grant become user to ratuser;
Grant succeeded.
SQL> Grant create any directory to ratuser;
Grant succeeded.
SQL> Grant select_catalog_role to ratuser;
Grant succeeded.
SQL>
SQL> grant ratuser to appuser;
Grant succeeded.
Let’s try now and see how it goes with this new application user that we just created with all right permissions.
[oracle@testbox-fatdba ~]$ wrc appuser/oracle90@pdb1 mode=replay replaydir=/home/oracle/replaydir
Workload Replay Client: Release 21.3.0.0.0 - Production on Sat Feb 24 13:14:19 2024
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Wait for the replay to start (13:14:19)
Yeah, and this is working fine.
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 