TPS Chart on PL/SQL Developer

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:

PLSQL_Graph

And get:

7days_tps

PL/SQL Developer  is a commercial tool of Allround Automations.
You can access more information about licensing here.

Have a nice day!
Matheus.

Oracle TPS: Evaluating Transaction per Second

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:

awr_tps

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:

7days_tps

Jedi Master Jonathan Lewis wrote a good post about Transactions and this kind of AWR metric here.

See ya!
Matheus.

RMAN Raise ORA-19913 ORA-28365 On Restore from Cloud Backup

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.

UnknownHostException: Could not authenticate to Oracle Database Cloud Backup Module

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:
retention_policy

After that, everything worked fine. 🙂

KB:
Problems with Installing the Backup Module
Selecting a Replication Policy for Oracle Storage Cloud Service

See ya!
Matheus.

Contact us!

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!

questions
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!

kernel.panic_on_oops: New Oracle 12c Installation Requirement

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:

  • 0: try to continue operation
  • 1: panic immediately.  If the `panic’ sysctl is also non-zero then the machine will be rebooted.

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.

Oracle Database: Compression Algorithms for Cloud Backup

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.Compress_PDF
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.

Installing and Configuring ASMLIb on Oracle Linux 7

Hi all!
For those are familiar with RHEL/OEL 4 and 5, there is some differences to start ASMLib on OEL 6 and 7.

spanner.png
So, a quick guide to install (done on OEL 7), start and configure:

1. Install the ASMLib kernel module package as root using the following command:

yum install kmod-oracleasm

2. Install the ASMLib library package and utilities package

yum install oracleasm-support oracleasmlib oracleasm-`uname -r`

It’s possible some package to not found. For example:

No package oracleasmlib available.

So, you can download rpm libs from here and install via rpm:

[root@dbsrv01 oracle]# rpm -Uvh ~/oracleasmlib-2.0.12-1.el6.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
1:oracleasmlib-2.0.12-1.el6        ################################# [100%]

Ok, now, lets configure/start services:

[root@dbsrv01 ~]# /etc/init.d/oracleasm configure

Nothing happen? Ok, let’s try to start it:

[root@dbsrv01 ~]# /etc/init.d/oracleasm start
Starting oracleasm (via systemctl):  Job for oracleasm.service failed because the control process exited with error code. See "systemctl status oracleasm.service" and "journalctl -xe" for details.
[FAILED]

Hmmm… Are these commands correct?

[root@dbsrv01 ~]# /etc/init.d/oracleasm
Usage: /etc/init.d/oracleasm {configure|createdisk|deletedisk|querydisk|listdisks|scandisks|status}

Ok… So, what to do?

Take a look:

[root@dbsrv01 ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm

Victory!
Now, let’s configure:

[root@dbsrv01 ~]# oracleasm configure
ORACLEASM_UID=
ORACLEASM_GID=
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"

It shows, but how configure?

Just put “-i” clause, like:

[root@dbsrv01 ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.
Default user to own the driver interface []: grid
Default group to own the driver interface []: oinstall
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done

And you can list again:

[root@dbsrv01 ~]# oracleasm configure
ORACLEASM_UID=grid
ORACLEASM_GID=oinstall
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"
[root@dbsrv01 ~]# oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

To add a disk, the same process can be followed on earlier versions:

[root@dbsrv01 ~]# oracleasm createdisk SDD /dev/sdd1
Writing disk header: done
Instantiating disk: done
[root@dbsrv01 ~]# oracleasm listdisks
SDD

For all commands:

[root@dbsrv01 ~]# oracleasm -h
Usage: oracleasm [--exec-path=]  [  ]
oracleasm --exec-path
oracleasm -h
oracleasm -V
The basic oracleasm commands are:
configure        Configure the Oracle Linux ASMLib driver
init             Load and initialize the ASMLib driver
exit             Stop the ASMLib driver
scandisks        Scan the system for Oracle ASMLib disks
status           Display the status of the Oracle ASMLib driver
listdisks        List known Oracle ASMLib disks
querydisk        Determine if a disk belongs to Oracle ASMlib
createdisk       Allocate a device for Oracle ASMLib use
deletedisk       Return a device to the operating system
renamedisk       Change the label of an Oracle ASMlib disk
update-driver    Download the latest ASMLib driver

And to see arguments for each one:

[root@dbsrv01 ~]# oracleasm configure -h
Usage: oracleasm-configure [-l ] [-i|-I] [-e|-d] [-u ] [-g ] [-b|-p] [-s y|n] [[-o ] ...] [[-x ] ...]

Have a nice day!
See ya!
Matheus.

Oracle Licensing: Weblogic Tip!

Like a complement to yesterday post, about Oracle Database Licensing, today’s post is a little tip to Weblogic licensing evaluating, considering an auditing…

The restricted services can be checked on WebLogic Server Basic – Restricted Primary Services in WebLogic Server.

There is also a py script on Orace Support that can be executed via WLST on Admin Sever. Please take a look on: WebLogic Server Basic License Feature Usage Measurement Script (Doc ID 885587.1)

And it’s all by now! 🙂

Hugs!
Matheus.

Oracle Database Licensing: First Step!

Oracle licensing is always a complex question, right?
I made some search about it today and decided to share, on quick mode. As I usually do. I focused on Database, by the way. 🙂

The first step is to understand Features vs Options vs Packs relation. Oracle documentation is always good for that. I recommend you to spend some time on Database Licensing Information User Manual.

Ok, now the best way to undestand how evaluate my environment is searching on Oracle Support, right?
And it do not disappoint: Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2 and 12c (Doc ID 1317265.1)
In this note you can get a complete and actual script used to evaluate features/options/packs utilization (options_packs_usage_statistics.sql). This is a good way if you are preparing for an auditing…

SOFTWARE-LICENSE-REVIEW-300x199

I made some simple queries to validate/understand results from Oracle Script. So, if you don’t have access to Oracle Support, it might help you:

Get Features usage:

SELECT u1.name,
u1.detected_usages,
u1.currently_used,
u1.version,
u1.description
FROM   dba_feature_usage_statistics u1
WHERE  u1.version = (SELECT MAX(u2.version)
FROM   dba_feature_usage_statistics u2
WHERE  u2.name = u1.name)
AND    u1.dbid = (SELECT dbid FROM v$database)
--WHERE DETECTED_USAGES>0 -- To get used features only
ORDER BY name
/

Get Options usage:

col parameter for a50
select parameter,value from v$option
--where value='TRUE' -- To get used options only
/

Information about Session license limits:

SELECT sessions_max s_max,sessions_warning s_warning,
sessions_current s_current,sessions_highwater s_high,users_max
FROM v$license;

Information about CPU license limits:

select cpu_count_current, cpu_core_count_current,CPU_SOCKET_COUNT_CURRENT,
CPU_COUNT_HIGHWATER,CPU_CORE_COUNT_HIGHWATER,CPU_SOCKET_COUNT_HIGHWATER
FROM v$license;

An interesting point is that you can disable and enable options through the command chopt. But, you must to get database down first. Example to disable partitioning option:

chopt disable partitioning

The complete explanation and examples (including right values to activate/deactivate options) can be found on Oracle Database Postinstallation Tasks – Enabling and Disabling Database Options.

Some time ago I wrote a post about evaluating Database license in all database park through OEM. It remains valid, I recommend you take a look in this post too.

 

KB and other interesting stuffs:
Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2 and 12c (Doc ID 1317265.1)
12c Release 1 Database Licensing Information User Manual
11g Release 1 Database Licensing Information User Manual
Enabling and Disabling Database Options
Excellent and actual presentation by Martin Berger
Article about top license pitfalls. Good to reflection. Written by OMTCO Consulting

Matheus.