Oracle by Anand: October 2017

How to find the Uptime of MySQL Instance

How can I find, since when my MySQL Instance is running

login to your MySQL server and execute

mysql> show status like 'Uptime';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Uptime        | 2507101 |
+---------------+---------+
1 row in set (0.00 sec)

Note:- The Uptime value displayed here is in Seconds. My MySQL instance is running since
2507101 seconds

OR

mysql> \s;
--------------
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id:          289795
Current database:
Current user:           oracle@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.73-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn. characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 29 days 21 min 3 sec
Threads: 6 Questions: 14313149 Slow queries: 9 Opens: 77 Flush tables: 1 Open tables: 57 Queries per second avg: 5.709
--------------
ERROR:
No query specified

OR

mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id:          289820
Current database:
Current user:           oracle@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.73-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn. characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 29 days 28 min 45 sec
Threads: 6 Questions: 14314196 Slow queries: 9 Opens: 77 Flush tables: 1 Open tables: 57 Queries per second avg: 5.708
--------------
mysql>

List Oracle patches Applied in Database and Usages of DBMS_QOPATCH

How can I list all of the patches that I have applied

Run opatch lsinventory from $ORACLE_HOME/OPatch to check the patches applied

$opatch lsinventory
or

$opatch lsinventory -display
or

$opatch lsinventory -details

Execute below command directly in the database as dba to check the list of patches registered in the database

SQL> select * from sys.registry$history;

for 12c

SQL> select * from sys.dba_registry_sqlpatch

from 12c onwards you can even use dbms_qopatch package

SQL> set serverout on;
SQL> exec dbms_qopatch.get_sqlpatch_status;
SQL> select dbms_qopatch.GET_OPATCH_LIST from dual;

Query for any specific patch that has been applied?

SQL> select xmltransform(dbms_qopatch.is_patch_installed('21359755'), dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;

Patch installed?
-------------------------------------------------------
Patch Information:
21359755: applied on 2015-10-21T23:48:17Z

All you can do with DBMS_QOPATCH package

SQL> desc dbms_qopatch

FUNCTION ADD_OINV_JOB RETURNS BOOLEAN Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
NNAME                          VARCHAR2                IN
INAME                          VARCHAR2                IN

PROCEDURE CONFIG_OINV_JOBS

FUNCTION DROP_OINV_JOB RETURNS BOOLEAN
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
NNAME                          VARCHAR2                IN
INAME                          VARCHAR2                IN

FUNCTION GET_OPATCH_BUGS RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PNUM VARCHAR2 IN DEFAULT

FUNCTION GET_OPATCH_COUNT RETURNS XMLTYPE

FUNCTION GET_OPATCH_DATA RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PNUM VARCHAR2 IN

FUNCTION GET_OPATCH_FILES RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PNUM VARCHAR2 IN

FUNCTION GET_OPATCH_INSTALL_INFO RETURNS XMLTYPE

FUNCTION GET_OPATCH_LIST RETURNS XMLTYPE

FUNCTION GET_OPATCH_LSINVENTORY RETURNS XMLTYPE

FUNCTION GET_OPATCH_OLAYS RETURNS XMLTYPE Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PNUM VARCHAR2 IN

FUNCTION GET_OPATCH_PREQS RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PNUM VARCHAR2 IN

FUNCTION GET_OPATCH_XSLT RETURNS XMLTYPE

FUNCTION GET_PENDING_ACTIVITY RETURNS XMLTYPE

PROCEDURE GET_SQLPATCH_STATUS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PNUM VARCHAR2 IN DEFAULT

FUNCTION IS_PATCH_INSTALLED RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PNUM VARCHAR2 IN

PROCEDURE OPATCH_INV_REFRESH_JOB

PROCEDURE OPATCH_RUN_JOB

FUNCTION PATCH_CONFLICT_DETECTION RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILENAME VARCHAR2 IN

PROCEDURE REFRESH_OPATCH_DATA

PROCEDURE REPLACE_DIRS_INT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PF_ID NUMBER IN

PROCEDURE REPLACE_LOGSCRPT_DIRS

PROCEDURE SET_CURRENT_OPINST
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
NODE_NAME                      VARCHAR2                IN     DEFAULT
INST_NAME                      VARCHAR2                IN     DEFAULT

PROCEDURE SET_DEBUG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DEBUG BOOLEAN IN

PROCEDURE SKIP_SANITY_CHECK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SKIP BOOLEAN IN

Oracle Launches worlds first fully automated database oracle 18c

Oracle launches 18c, its autonomous database and automated cybersecurity system

Oracle chief technology officer Larry Ellison unveiled the company's autonomous database called 18c in the oracle OpenWorld as well as a cybersecurity system that rides shot gun with it.

Key Takeaway from the seminar

Oracle autonomous database runs on Exadata Infrastructure
Provides total Automation based on machine learning
Oracle 18c is available on-premise, in Oracle's public cloud and cloud at customer
warehouse version of Oracle 18c is available in December 2017. The OLTP version will be available in June 2018.
Online scaleup and scaledown of resouces eg CPU

Autonomous Database For Datawarehouse Oracle 18c

Availiable from December2017
Fully Automated provisioning, patch,upgrade and Tune while the database is running
Eliminates human labour and therefore human error
Minimize h/w resource consumption
Guaranteed half the cost for the same workload running at Amazon
Guaranteed 99.995% availablily

Autonomous Database For OLTP Oracle 18c

Availiable from June2018
Fully Automated provisioning, patch,upgrade and Tune while the database is running
Handels OLTP and mixed workloads
provision Mission Critical with high performance and 99.995% availablily
Provision Low Cost for non critical workloads eg test or dev

Click Here to see the Oracle OpenWorld webcast

I am sure as a DBA you will not like this post but I will still ask you to hit the like button :)