Posts

Showing posts from 2018

Oracle open world 2017 short summary

Image
Hi, The main subject in this conference was Autonomous Database, it's engine is used by another buzzword called: Machine Learning. 
Oracle claim they are investing in autonomous to prevent data theft, but it seems as convenience for handling only one version in the cloud.
They will use the same "Machine Learning" engine that they used for the optimizer and memory management decisions (This engine is well known for bad decisions). 18c is an alias for 12.2.0.2 and planned to be available: Autonomous Database for Data Warehouses - by December 2017Autonomous Database for OLTP – by June 2018 A companion solution for the "Machine Learning" will be used via the OEM (Cloud Control) to gather: Infrastructure logs: Network, Server, Storage, VM, OSPlatform logs: Database, Java, Analytics, etc.Application logs: ERP, CX, HCM, Custom, etc. The logs will be gathered to detect and connect anomalous events. SLA Guarantees 99.995% reliability and availability (less than 30 minute…

Oracle Linux ASM docker recipe

Image
General information In this setup we are:
Installing dockerCreating Non-root user (ynixon) with sudo and docker privilegesASM device: /dev/sdb1Enabling sqlnet + ssh to the containerDefault ASM port is 1521ssh port 2222Passwords for root + grid os users in the container are “ynixon”Password for sys ASM user is “ynixon”Grid software is 12.2 without any patchesContainer Operating system is Oracle Linux 7.5Within the container, there is no use of UDEV / ASMLIB or ASMFD – the asm_diskstring='/dev/asm*' ,'/dev/*'All test done on regular Ubuntu 14.04There is a crontab job to keep 15 days of trace files + remove audit files. Prepare host for ASM device Make sure the device has permissions of the same container ids by applying UDEV rules $ vi /etc/udev/rules.d/100-asm.rules
KERNEL=="sdb1", NAME="ASM_DISK", OWNER="54421", GROUP="54421", MODE="0660"

udevadm trigger --sysname-match=sdb1 --verbose Verify the device has ASM lables DI…

Bypassing the Listener and Connecting the Instance without a Password from Java Code

Image
In the past I wrote a post about Bypassing the listener and connecting to ASM without a password from Python code

Now, I've been asked to prove that it can be done in Java. The solution is almost the same.

Java sample code using OCI:

$ vi OracleCon.java
import java.sql.*;
class OracleCon {
    public static void main(String args[]) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection(
                "jdbc:oracle:oci:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=BEQ) (PROGRAM=oracle) (ARGV0=oracle+ASM) (ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') (ENVS = 'ORACLE_HOME=/oracle/product/12.1.0.2/grid,ORACLE_SID=+ASM'))) (CONNECT_DATA= (SID=+ASM)))", "sys as sysdba", "change on install");
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("select * from v$instance");
      …

Automatic restart in Grid Infrastructure for Far Sync

Image
Hi,
Checking if the Far Sync instance is configured to restart automatically after reboot / shutdown of the host / cluster:
$ crsctl stat res ora.fdb1.db -f |grep AUTO_START
AUTO_START=restore "restore" means that after restart, the database will remain at the previous state it was before.
Changing the cluster to startup the database in any case.
$ crsctl stat res ora.fdb1.db -f |grep AUTO_START
AUTO_START=always Changing database to start always after reboot
$ crsctl modify resource ora.fdb1.db -attr  AUTO_START=always -unsupported Modify the startup state:
$ srvctl modify database -o mount View modified configuration
$ srvctl config database -d fdb1 -all
Database unique name: fdb1
Database name: fdb1
Oracle home: /oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: FAR_SYNC
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
Database is enabled
OSDBA group:
OSOPER group:
Databa…

Query Historical messages of RMAN

Image
Hi, Recently I realized that this very small and useful feature of RMAN is not known to many of us.
I have just learned about this option just two years ago.
I am talking about the ability to query from database (in-memory view) 32768 lines of RMAN output, even if it was used by crontab or any 3rd-party vendor.
So it is a sample sql command from v$rman_output, in the following example I filter just the last 7 days.
SQL>  col OUTPUT for a135 trunc
SQL> set pages 0
SQL> select output from gv$rman_output where session_recid in (select session_recid from v$rman_status where start_time > sysdate-7) order by recid ;
connected to target database: PDB11 (DBID=3890652951, not open)

crosscheck archivelog all;
delete noprompt archivelog like '+DATA%';
delete noprompt archivelog like '+RECO%';
delete noprompt archivelog like '/DB%';
delete noprompt obsolete;
crosscheck copy;
delete noprompt expired copy;
exit;

using target database control file instead of reco…

My impressions from Oracle OpenWorld 2018

Image
Hi, This was my third time at Oracle OpenWorld, and still I was thrilled like the first time. Howard Street was dressed in big, red banners everywhere—just take a picture and share it in a social network.



This time I was also there as part of the Oracle ACE program, a community of Oracle experts and advocates. We all met on several occasions during the conference, giving me the opportunity to meet in person with the people I follow and whose sessions I attend.  

This year’s main keynote by Larry Ellison was about Oracle Generation 2 Cloud, which was built from the ground up for better performance, pricing, and security. I assume that Oracle’s architecture is indeed better than the competitors, as Larry kept saying, because Oracle hired the best minds in the industry - people with deep experience in cloud technology - to build Generation 2. I just hope that Oracle’s implementation will be as good as its design. Oracle announced that version 18c, and later 19c, will be fully independen…

Orphaned Files in ASM

Image
Hi,
In our lab environments we test Data Guard on a daily basis, and we frequently “play” with failover, switchover, and flashback . The output of this playground is that we have some leftovers in the ASM; we call these leftovers orphan files.
To solve this, I created SQL to query ASM views against database views.
This query should run on the database (not ASM).
SET VERIFY OFF

SET LINESIZE 200
SET SERVEROUTPUT ON
SET PAGESIZE 50000

DECLARE
   cmd   CLOB;
BEGIN
   FOR c IN (SELECT name Diskgroup
               FROM V$ASM_DISKGROUP)
   LOOP
      FOR l
         IN (SELECT 'rm ' || files files
               FROM
                    (SELECT '+' || c.Diskgroup || files files, TYPE
                       FROM (    SELECT UPPER
                                        (
                                           SYS_CONNECT_BY_PATH (aa.name, '/')
                                        )
                                           files
                                …

Data Guard Broker 19c

Image
Hi,
In an earlier version of Data Guard, when the broker had problems, one of the automatic answers I got from support was to recreate the broker configuration.
In other words:
Drop the configuration. Create the configuration. If your broker is simple, this is not a huge request. Actually, you could follow Step By Step How to Recreate Data Guard Broker Configuration (Doc ID 808783.1).
But in my case, using Active Data Guard with Far Sync, with many fine-tuned configurations, it was not so convenient. I had a number of ways I tried to be efficient:
I could write down all broker commands - to be able to run them again next time.For versions 11.2.0.4 and 12.1.0.2, I found a way to query the broker and re-create a current configuration creation script (Extract Data Guard Commands).I also had some issues trying to Drop/remove Far-Sync Configurations from broker 12.1.On version 12.2.0.1, I found out that the broker metadata was changed, so I had to update my previous script for Extract Data …

SLOB

SLOB is an Oracle I/O workload generation tool kit, supports testing extreme REDO logging I/O (minimal amount of CPU overhead)
The SLOB package can be downloaded from Kevin Closson's Blog: SLOB Resources
SLOB 2.4.2 requires Linux with Oracle Client
InstallationUnzip the SLOB tar.gz file in the desired directory ($SLOB).
Create tablespace for SLOB data (the script creates IOPS)
$ cd $SLOB
$ sqlplus / as sysdba @ misc/tsLoad SLOB data by running setup.sh script located in the $SLOB directory. Using two mandatory parameters:
Tablespace into which SLOB will create and load the test schemasThe number of schemas to create and load$ ./setup.sh IOPS 8configurationOne time compilation
$ cd $SLOB/wait_kit
$ makeEdit runtime parameter in slob.conf configuration file
$ cd $SLOB
$ vi slob.confExecute slob using number of SLOB schemas
$ cd $SLOB
$ ./runit.sh 8We can edit the slob.conf file to modify some parameters.
UPDATE_PCT - Percentage of SLOB update operationsSCAN_PCT - percentage of short scan S…

Swingbench Short cookbook commands

Image
In this example we are using Swingbench 2.6 with Order Entry stress test - using a configuration file (SOE_Server_Side_V2.xml)

Connection String:
The connection will always be defined to pluggable database (not CDB) - if installed. //hostname/service_name valid for driver type: Oracle oci Driver & Oracle jdbc Driver (oewizard,sbutil use only this option) .
Make sure the service name is recognized by the listener (lsnrctl services | grep Service) Tnsnames alias valid only for driver type: Oracle oci Driver – requires Oracle 12.2 client Creating the schema:
cd swingbench/bin
$ ./oewizard -dbap change_on_install -ts SOE -nopart -u soe -p soe -cl -df +DATA -create -scale 1 -cs //db_server/db_serviceRunning:
If you need to use Connection Pooling:
Change the driver type to "Oracle oci Driver"Change the format of the connection string to tns aliasFor further needed changes, look at: Application Continuity in Oracle Database 12c (12.1.0.2)charbench - character mode benchmark
miniben…

Steps To Shutdown / Startup The Exadata

Image
ShutDownLog in to the first database server as root.Change to the OneCommand directory# cd /opt/oracle.SupportTools/onecommand Note whether the Grid Infrastructure is currently enabled for autostart, so that this state can be restored later:# dcli -g dbs_group -l root /u01/app/12.1.0.2/grid/bin/crsctl config crs Disable the Grid Infrastructure for autostart on the database servers if the previous step indicated it is currently enabled for autostart.# dcli -g dbs_group -l root /u01/app/12.1.0.2/grid/bin/crsctl disable crs Note: This is step is [Optional] and it can required during maintenance operation like “firmware patches” which requires to reboot the Compute Node several times. Stop the Grid Infrastructure stack on the database servers (compute nodes):# dcli -g dbs_group -l root "/u01/app/12.1.0.2/grid/bin/crsctl stop crs" Verify that the Grid Infrastructure stack has shutdown successfully on the database servers. The following command should show no output if the GI stack has…