Hi all,
Since last post, some people asked me about how to make the charts using PL/SQL Developer. It basically works for every kind of query/data, like MS Excel.
I’d recommend you to use with historic data, setting time as “X” axis.
Here the example for the post Oracle TPS: Evaluating Transaction per Second:
And get:
PL/SQL Developer is a commercial tool of Allround Automations.
You can access more information about licensing here.
Have a nice day!
Matheus.
Sometimes this information has some ‘myth atmosphere’… Maybe because of that Oracle doesn’t have this information very clear and it’s not the most useful metric.
But for comparison to another systems and also to performance/’throughput’ with different infrastructure/database configuration, it can be useful.
It can be seen by AWR on “Report Summary” section, on “Load Profile”, “Transactions” item:
But if you want to calculate it through SQL query?
And if you want to have a historic from this metric?
I found a reference for this calculation here, using v$sysstat.
It’s the only reference I found, and it on 10g documentation… It refers this metric as:
Number of Transactions = (DeltaCommits+DeltaRollbacks)/Time
It also refers as DeltaCommits and DeltaRollbacks, respectively, “user commits” and user “rollbacks”.
Where it goes a possible SQL to do that:
WITH hist_snaps
AS (SELECT instance_number,
snap_id,
round(begin_interval_time,'MI') datetime,
( begin_interval_time + 0 - LAG (begin_interval_time + 0)
OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time
FROM dba_hist_snapshot), hist_stats
AS (SELECT dbid,
instance_number,
snap_id,
stat_name,
VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY snap_id)
delta_value
FROM dba_hist_sysstat
WHERE stat_name IN ('user commits', 'user rollbacks'))
SELECT datetime,
ROUND (SUM (delta_value) / 3600, 2) "Transactions/s"
FROM hist_snaps sn, hist_stats st
WHERE st.instance_number = sn.instance_number
AND st.snap_id = sn.snap_id
AND diff_time IS NOT NULL
GROUP BY datetime
ORDER BY 1 desc;
I like to use PL/SQL Developer to see this kind of data. And it regards us to make very good charts very quickly. I try it in a small database here, just for example:
Jedi Master Jonathan Lewis wrote a good post about Transactions and this kind of AWR metric here.
See ya!
Matheus.
I had to troubleshoot a situation, after GoldenGate capture some DML and replicate that, Oracle database needs to run insert trigger making some business integration.
After to upgrade this enviroment from GG 11.1.1.1 to 12.1.2 and DB 11.2.0.3 to 12.1.0.2, was identified that GoldenGate wasn’t running this triggers
So, found interesting resolution on Oracle Docs:
SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS
Valid for nonintegrated Replicat for Oracle. Controls whether or not triggers are fired during the Replicat session. Provides an alternative to manually disabling triggers. (Integrated Replicat does not require disabling of triggers on the target system.)
SUPPRESSTRIGGERS is the default and prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. SUPPRESSTRIGGERS is valid for Oracle 11.2.0.2 and later 11gR2 versions. SUPPRESSTRIGGERS is not valid for 11gR1.
So, added ‘DBOPTIONS NOSUPPRESSTRIGGERS’ in the replicat parameter file.
Regards!
Maiquel.
First I think was some error with Database Backup To Cloud, when testing. Then I realized it was a simple mistake by myself.
Let me show you. First trying to restore datafile:
[oracle@mydbsrvr archivelogs]$ rman target / RMAN> restore datafile 6; Starting restore at 03-MAY-2016 20:00:30 using channel ORA_SBT_TAPE_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=178 device type=DISK channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00006 to /db/u1001/test/cloud_test/test_restore.dbf channel ORA_SBT_TAPE_1: reading from backup piece 0sr4mdun_1_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 05/03/2016 20:00:34 ORA-19870: error while restoring backup piece 0sr4mdun_1_1 ORA-19913: unable to decrypt backup ORA-28365: wallet is not open
Ok, it might happen because I forgot to set encryption password:
RMAN> SET ENCRYPTION ON IDENTIFIED BY "matheusdba" only; executing command: SET encryption RMAN> restore datafile 6; Starting restore at 03-MAY-2016 20:00:30 using channel ORA_SBT_TAPE_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=178 device type=DISK channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00006 to /db/u1001/test/cloud_test/test_restore.dbf channel ORA_SBT_TAPE_1: reading from backup piece 0sr4mdun_1_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 05/03/2016 20:00:34 ORA-19870: error while restoring backup piece 0sr4mdun_1_1 ORA-19913: unable to decrypt backup ORA-28365: wallet is not open
It hapen again?
This point I suspect some kind of bug… But it was my mistake and is not related to Cloud, but to Encryption use. To undestand:
For Backup: Use ENCRYPTION
For Restore/Recover: Use DECRYPTION
Obviously, but take me a minute to realize…
Setting decryption, and problem solved:
RMAN> set DECRYPTION identified by "matheusdba"; executing command: SET decryption RMAN> restore datafile 6; Starting restore at 03-MAY-2016 20:00:58 using channel ORA_SBT_TAPE_1 using channel ORA_DISK_1 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00006 to /db/u1001/test/cloud_test/test_restore.dbf channel ORA_SBT_TAPE_1: reading from backup piece 0sr4mdun_1_1 channel ORA_SBT_TAPE_1: piece handle=0sr4mdun_1_1 tag=TAG20160503T193239 channel ORA_SBT_TAPE_1: restored backup piece 1 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:03 Finished restore at 03-MAY-2016 20:01:02
See ya!
Matheus.
Every time you need to execute ICommand, you must enter the user and password of the application server running Oracle BAM.
With the configuration below, it is no longer necessary to inform username and password every time.
In the file “BAMICommandConfig.xml” located in “$BEA_HOME/Oracle_SOA1/bam/config/”, add the lines below to the end of the file, before the tag “/BAMICommand”:
weblogic YOUR_PASSWORD
Restart the Oracle BAM.
Jackson.
Hi all!
When running Oracle Database Cloud Backup Module, found this error:
Command:
java -jar opc_install.jar -serviceName Storage -identityDomain usmatheusdba -opcId 'matheus@boesing.com.br' -opcPass 'BestBlog2016' -walletDir /db/oracle/admin/cloud/wallet -libDir /db/oracle/admin/cloud/libs
(Credential values changed, of course…)
Error:
Oracle Database Cloud Backup Module Install Tool, build 2016-02-04 java.net.UnknownHostException: usmatheusdba.storage.oraclecloud.com at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:175) at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:385) at java.net.Socket.connect(Socket.java:546) at sun.security.ssl.SSLSocketImpl.connect(SSLSocketImpl.java:602) at sun.security.ssl.BaseSSLSocketImpl.connect(BaseSSLSocketImpl.java:160) at sun.net.NetworkClient.doConnect(NetworkClient.java:178) at sun.net.www.http.HttpClient.openServer(HttpClient.java:427) at sun.net.www.http.HttpClient.openServer(HttpClient.java:529) at sun.net.www.protocol.https.HttpsClient.(HttpsClient.java:275) at sun.net.www.protocol.https.HttpsClient.New(HttpsClient.java:332) at sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.getNewHttpClient(AbstractDelegateHttpsURLConnection.java:191) at sun.net.www.protocol.http.HttpURLConnection.plainConnect(HttpURLConnection.java:891) at sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.connect(AbstractDelegateHttpsURLConnection.java:177) at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1226) at sun.net.www.protocol.https.HttpsURLConnectionImpl.getInputStream(HttpsURLConnectionImpl.java:254) at oracle.backup.opc.install.OpcConfig.testConnection(OpcConfig.java:235) at oracle.backup.opc.install.OpcConfig.doOpcConfig(OpcConfig.java:204) at oracle.backup.opc.install.OpcConfig.main(OpcConfig.java:197) Could not authenticate to Oracle Database Cloud Backup Module
Solution:
Set Relication Policy of Oracle Storage Cloud Service.
In My Services Home, Oracle Storage Cloud Service will have a link to “Set Retention Policy”. It’s simply set it.
But pay attention, once you select a replication policy, you can’t change it.
As you can see, I already did it:
After that, everything worked fine. 🙂
KB:
Problems with Installing the Backup Module
Selecting a Replication Policy for Oracle Storage Cloud Service
See ya!
Matheus.
Hi everyone!
Often we caught ourselves trying to remember some simple commands to achieve what we need. And SRVCTL and its variations may be one of them 🙂
Sometimes we need to create a specific service_name to connect to an existing database, and we can, for example, have an application that use a SPECIFIC NODE, so we can configure the service name to use it that way. And we find ourselves looking for the right syntax for that. Ok, we are going to give you guys some basic examples that may be helpful
In order to check ALL the available services already created via SRVCTL we should use:
srvctl status service -d
it should retrieve an output like that:
dbsrv {/home/oracle}: srvctl status service -d dbgrepora
Service grepora-app1 is running on instance(s) dbgrepora1
Please bear in mind that the does not necessarily match the instance name, so to make sure about the database name, run:
srvctl config database
Example:
dbsrv {/home/oracle}: srvctl config database
dbgrepora
If you have more than one database on that server, it will be returned too.
Ok, now let’s try to create a new service name for your database. In the node that you want to create the service_name, please run the following.
srvctl add service -d -s
where follow the rule already described above, and you can create as you wish.
Ok GREPORA, but what if i want to create a service_name to multiple instances ? You got it!
The syntax follows the same idea, but we should include different parameter in there, which is:
-r
Example:
srvctl add service -d dbgrepora -s service_dbg -r dbgrepora1,dbgrepora2
Creating the service_dbg service, and checking the status, you’ll have an output like:
dbsrv {/home/oracle}: srvctl status service -d dbgrepora -s service_dbg
Service service_dbg is running on instance(s) dbgrepora1,dbgrepora2
To stop and remove a created service just use:
srvctl stop service -d -s
srvctl remove service -d -s
Hope it comes to help!
Best Regards,
Rafael.
Hi all!
Do you have any question about a post? Do you have a suggestion to improve the blog? Is there any subject you like to see in a post?
Please, let us know!
For now on, there is a link in the right menu (below Categories) to send us an email. Feel free to contribute and ask.
Have a nice day!
Hi all,
Do you know what mean the parameters on installing 12c?
This parameter controls the kernel’s behaviour when an oops or bug is encountered:
OOPS is a deviation from correct behavior of the Linux kernel, one that produces a certain error log.
The better-known kernel panic condition results from many kinds of oops, but other instances of an oops event may allow continued operation with compromised reliability.
This is recommended in a system where we want to have node evicted in case of any hardware failure or any other issue.
To adjust as recommended by Oracle?
1. Put an entry in sysctl.conf for having it permanent:
kernel.panic_on_oops = 1
2. Refresh running command:
sysctl -p
KB: https://www.kernel.org/doc/Documentation/sysctl/kernel.txt
Matheus.
Hi all!
Again talking about cloud backups for on-premise databases: An important aspect is to compress the data, so network consumption might be reduced once less data is being transfered.
It’s also important to evaluate CPU consumption. As higher compress algorithm is, as much CPU it uses. So, pay attention!
Now, how to choose the compression algorithm? Here the options Oracle give us:
SQL> col ALGORITHM_NAME for a15 set line 200 SQL> select ALGORITHM_NAME,INITIAL_RELEASE,TERMINAL_RELEASE,ALGORITHM_DESCRIPTION,ALGORITHM_COMPATIBILITY from v$rman_compression_algorithm; ALGORITHM_NAME INITIAL_RELEASE TERMINAL_RELEASE ALGORITHM_DESCRIPTION ALGORITHM_COMPATIB -------------- ------------------ ------------------ ---------------------------------------------------------------- ------------------ BZIP2 10.0.0.0.0 11.2.0.0.0 good compression ratio 9.2.0.0.0 BASIC 10.0.0.0.0 good compression ratio 9.2.0.0.0 LOW 11.2.0.0.0 maximum possible compression speed 11.2.0.0.0 ZLIB 11.0.0.0.0 11.2.0.0.0 balance between speed and compression ratio 11.0.0.0.0 MEDIUM 11.2.0.0.0 balance between speed and compression ratio 11.0.0.0.0 HIGH 11.2.0.0.0 maximum possible compression ratio 11.2.0.0.0
How to identify our compression algorithm?
RMAN> show COMPRESSION ALGORITHM; RMAN configuration parameters for database with db_unique_name EZM_PRFL are: CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
And how to change it?
RMAN> CONFIGURE COMPRESSION ALGORITHM 'HIGH'; new RMAN configuration parameters: CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE; new RMAN configuration parameters are successfully stored RMAN> show COMPRESSION ALGORITHM; RMAN configuration parameters for database with db_unique_name EZM_PRFL are: CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
Ok,
But how to evaluate my compression ratio?
See the difference between INPUT_BYTES_DISPLAY and OUTPUT_BYTES_DISPLAY columns from the query:
prddb> col STATUS for a10 prddb> col INPUT_BYTES_DISPLAY for a15 prddb> col OUTPUT_BYTES_DISPLAY for a15 prddb> col TIME_TAKEN_DISPLAY for a20 prddb> SELECT SESSION_KEY, 2 INPUT_TYPE, 3 STATUS, 4 TO_CHAR(START_TIME, 'mm/dd/yy hh24:mi') start_time, 5 TO_CHAR(END_TIME, 'mm/dd/yy hh24:mi') end_time, 6 -- ELAPSED_SECONDS / 3600 hrs, 7 COMPRESSION_RATIO, 8 INPUT_BYTES_DISPLAY, 9 OUTPUT_BYTES_DISPLAY, 10 TIME_TAKEN_DISPLAY 11 FROM V$RMAN_BACKUP_JOB_DETAILS 12 where input_type like 'DB%' 13 ORDER BY SESSION_KEY 14 /SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME COMPRESSION_RATIO INPUT_BYTES_DIS OUTPUT_BYTES_DI TIME_TAKEN_DISPLAY ----------- ------------- ---------- -------------- -------------- ----------------- --------------- --------------- -------------------- 2 DB FULL COMPLETED 04/22/16 12:59 04/22/16 13:06 6,84838963 4.26G 636.50M 00:06:57 9 DB FULL COMPLETED 04/22/16 13:47 04/22/16 13:54 6,83764706 4.26G 637.50M 00:06:37 14 DB FULL COMPLETED 04/22/16 16:26 04/22/16 16:33 6,84189878 4.26G 637.25M 00:06:48
KB: https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmconfa.htm#BRADV89466
Done?
If you have any question, please let me know in the comments! 😉
Matheus.