Posted in 2016

RESTful Services

I am collecting information about REST..

http://crunchify.com/how-to-build-restful-service-with-java-using-jax-rs-and-jersey/
https://github.com/zorkian/nagios-api

Advertisements
Posted in 2016

Purge Job for Auditing

There is not much to write about this. It’s a purge job for the audit trail 😀 E: it will not work 😅 i have changed it a little 😅

DECLARE
lat TIMESTAMP := TO_TIMESTAMP('30-06-17 00:00:00.00','DD-MM-YYYY HH24:MI:SS.FF');
BEGIN

dbms_audit_mgmt.init_cleanup(AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std, DEFAULT_CLEANUP_INTERVAL => 1);

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => lat);

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'AUD_PURGE_JOB',
USE_LAST_ARCH_TIMESTAMP => TRUE );

dbms_audit_mgmt.deinit_cleanup(AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std);

END;
/

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'advance_archive_timestamp',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
-- Advance the archive timestamp
dbms_audit_mgmt.init_cleanup(AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std, DEFAULT_CLEANUP_INTERVAL => 1);

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => systimestamp – 120);

dbms_audit_mgmt.deinit_cleanup(AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std);
END; ',
start_date => SYSDATE,
repeat_interval => 'freq=DAILY;BYHOUR=0' ,
enabled => true,
auto_drop => FALSE);
END;
/

Posted in 2016

Oracle FS 12.2 New Feature

.While reading Oracle 12.2 New Features I discovered that we can create filesystems on tablespaces. So I had to try it and it works, but somehow wrong.

Docs used:

Database PL/SQL Packages and Types Reference

In the Large Objects guide you will not find very much: 😀

Database SecureFiles and Large Objects Developer’s Guide

I am using ASM so I decided to create a new diskgroup, I used the /dev/sde1 device for the new diskgroup OFS, I like to have everything ordered.

[oracle@ora01 data]$ lsblk
NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda               8:0    0   20G  0 disk
├─sda1            8:1    0  476M  0 part /boot
└─sda2            8:2    0 15.3G  0 part
  ├─ol-root     253:0    0   14G  0 lvm  /
  └─ol-products 253:2    0 23.3G  0 lvm  /products
sdb               8:16   0   12G  0 disk
└─sdb1            8:17   0   12G  0 part
sdc               8:32   0   12G  0 disk
└─sdc1            8:33   0   12G  0 part
sdd               8:48   0   30G  0 disk
└─sdd1            8:49   0 25.7G  0 part
  ├─ol-swap     253:1    0  3.7G  0 lvm  [SWAP]
  └─ol-products 253:2    0 23.3G  0 lvm  /products
sde               8:64   0   20G  0 disk
└─sde1            8:65   0   20G  0 part
sr0              11:0    1 1024M  0 rom

As you see it’s 20GB.

First we have to create a tablespace:

[oracle@ora01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Apr 12 12:24:37 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
SQL> alter session set db_create_file_dest='+OFS' ;

Session altered.

SQL> create tablespace OFS datafile size 20000m ;

Tablespace created.

SQL> select file_name from dba_data_files ;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/CDB1/DATAFILE/system.261.940672671
+DATA/CDB1/DATAFILE/sysaux.263.940672691
+DATA/CDB1/DATAFILE/undotbs1.265.940672693
+DATA/CDB1/DATAFILE/users.269.940672735
+OFS/CDB1/DATAFILE/ofs.256.941113555

So let’s create the ofs filesystem:

SQL> exec dbms_fs.make_oracle_fs(fstype=>'ofs',fsname=>'ofs_fs1',fsoptions=>'TABLESPACE=OFS')

PL/SQL procedure successfully completed.

Create the directory:

[oracle@ora01 ~]$ sudo mkdir -p /ofs/data
[oracle@ora01 ~]$ sudo chown -R oracle:oinstall /ofs/data

Mount it:

SQL> exec dbms_fs.mount_oracle_fs(fstype=>'ofs',fsname=>'ofs_fs1',mount_point=>'/ofs/data',mount_options=>'default_permissions, allow_other, persist');
BEGIN dbms_fs.mount_oracle_fs(fstype=>'ofs',fsname=>'ofs_fs1',mount_point=>'/ofs/data',mount_options=>'default_permissions, allow_other, persist'); END;

*
ERROR at line 1:
ORA-17676: Failed to mount 'ofs_fs1' with error:'Generic ofslib mount error'
ORA-06512: at "SYS.DBMS_FS", line 25
ORA-06512: at line 1

Ahh I forgot to install fuse

[root@ora01 ~]# yum install fuse
[root@ora01 ~]# usermod -a -G fuse oracle
[root@ora01 ~]# cat /etc/fuse.conf
# mount_max = 1000
user_allow_other

[root@ora01 ~]# reboot

Once the DB was up again I could create the filesystem

[oracle@ora01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Apr 12 12:51:15 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exec dbms_fs.mount_oracle_fs(fstype=>'ofs',fsname=>'ofs_fs1',mount_point=>'/ofs/data',mount_options=>'default_permissions, allow_other, persist');

PL/SQL procedure successfully completed.

OK, great we have it! check it on OS

[oracle@ora01 ~]$ df -hT
Filesystem              Type      Size  Used Avail Use% Mounted on
/dev/mapper/ol-root     xfs        14G  3.8G   11G  28% /
devtmpfs                devtmpfs  986M     0  986M   0% /dev
tmpfs                   tmpfs    1001M     0 1001M   0% /dev/shm
tmpfs                   tmpfs    1001M  8.5M  992M   1% /run
tmpfs                   tmpfs    1001M     0 1001M   0% /sys/fs/cgroup
/dev/mapper/ol-products xfs        24G   20G  3.7G  85% /products
/dev/sda1               xfs       473M  194M  280M  41% /boot
12.2.0.1                vboxsf    3.4T  2.3T  1.2T  67% /media/sf_12.2.0.1
tmpfs                   tmpfs     201M     0  201M   0% /run/user/54321
/dev/fuse               fuse       32G     0   32G   0% /ofs/data
[oracle@ora01 data]$ ls -ld /ofs/
drwxr-xr-x. 3 oracle oinstall 17 Apr 12 12:36 /ofs/
[oracle@ora01 data]$ ls -ld /ofs/data/
drwxrwxrwx. 1 nobody nobody 2048 Apr 12 12:30 /ofs/data/

It’s there, but do you see it???

/dev/fuse               fuse       32G     0   32G   0% /ofs/data

My diskgroup and tablespace is only 20GB.. and that’s a problem I think.

To have it complete, I unmount it too.

SQL> exec dbms_fs.unmount_oracle_fs(fsname=>'ofs_fs1',mount_point=>'/ofs/data')

PL/SQL procedure successfully completed.

Wow, I generated an ORA-600, after dropping the tablespace without unmounting it, creating this error:

ORA-00600: internal error code, arguments: [ksfsmLookup-p], [942], [ORA-00942: table or view does not exist

----- Abridged Call Stack Trace -----
ksedsts()+346<-kjzduptcctx()+868<-kjzdicrshnfy()+1113<-ksuitm_opt()+1678<-ksbrdp()+4494<-opirip()+609<-opidrv()+602<-sou2o()+145<-opimai_real()+202<-ssthrdmain()+417<-skgp_thread_async_main()+338<-start_thread()+197

Posted in 2016

filebeat and logstash – first setup

First, let’s install logstash, I have changed my yum repository to have access to their server.

root@serv-1: ~
> yum install logstash

Installed:
  logstash.noarch 1:5.2.1-1

Complete!

root@serv-1: /etc/logstash/conf.d
> cat /etc/logstash/conf.d/input.yml
input {
        beats {
                port => 5044
        }
}

output {
        file {
              path => "/tmp/output.log"
        }
}

root@serv-1: /etc/logstash/conf.d
> service logstash start
Redirecting to /bin/systemctl start  logstash.service

Now install and configure filebeat on the database host, I have to install an Oracle database too, I found somewhere on my disk SE2 install files so I used them and configured a very simple database

[root@dbhost01 oracle]# yum install filebeat

Installed:
  filebeat.x86_64 0:5.2.1-1

Complete!

[root@dbhost01 filebeat]# cd /etc/filebeat/
[root@dbhost01 filebeat]# cat filebeat.yml
filebeat.prospectors:
- input_type: log
  paths:
    - "/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log"

output.logstash:
  hosts: ["192.168.56.102:5044"]

logging:
  level: debug
  to_syslog: false
  to_files: true
  files:
    path: /var/log/filebeat
    name: filebeat.log

[root@dbhost01 ~]# service filebeat start

Installing database…

[oracle@dbhost01 database]$ ./runInstaller -silent -responseFile 
/u01/app/oracle/install/database/response/db_install.rsp -ignoreSysPrereqs

[oracle@dbhost01 dbs]$ cat initorcl.ora
db_name='orcl'
db_unique_name='orcl'
sga_target=300m
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='none'
db_block_size=8192
db_domain=''
db_create_file_dest='/u01/app/oracle/oradata'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
[oracle@dbhost01 dbs]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@dbhost01 dbs]$ mkdir -p /u01/app/oracle/oradata
[oracle@dbhost01 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area
[oracle@dbhost01 dbs]$
[oracle@dbhost01 dbs]$
[oracle@dbhost01 dbs]$
[oracle@dbhost01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 15:02:18 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile ;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  2923920 bytes
Variable Size             121635440 bytes
Database Buffers          184549376 bytes
Redo Buffers                5464064 bytes
SQL> create database orcl
  2  character set AL32UTF8
  3  national character set AL16UTF16
  4  extent management local
  5  undo tablespace undotbs1 ;

Database created.

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> connect system/oracle
SQL> @?/sqlplus/admin/pupbld.sql

Our database and filebeat is configured, so let’s generate a message

[oracle@dbhost01 trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 15:53:04 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> exec dbms_system.ksdwrt(2,'ORA-1000 This message is not an error')

PL/SQL procedure successfully completed.

Check on logstash server the output file.

root@serv-1: /etc/logstash
> tail -1f /tmp/output.log
{"@timestamp":"2017-02-21T14:53:32.536Z","offset":18825,"@version":"1","input_type":"log","beat":
{"hostname":"dbhost01","name":"dbhost01","version":"5.2.1"},
"host":"dbhost01","source":"/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log",
"message":"ORA-1000 This message is not an error","type":"log","tags":["beats_input_codec_plain_applied"]}

Yes, it works. Now you can start to read about these programs and configure it with elasticsearch and also somehow parsing the messages coming from the database.

Posted in 2016

RAC to single standby – revisited

Hi, today I want to show how I think it’s possible to migrate a RAC to single.

  1. First let’s identify free disks
    [root@vmware oracle]# lsblk 
    NAME                  MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
    sda                     8:0    0   20G  0 disk 
    ├─sda1                  8:1    0  500M  0 part /boot
    └─sda2                  8:2    0 19.5G  0 part 
      ├─ol_vmware-root    252:0    0 17.5G  0 lvm  /
      └─ol_vmware-swap    252:1    0    2G  0 lvm  [SWAP]
    sdb                     8:16   0  100G  0 disk 
    └─sdb1                  8:17   0  100G  0 part 
      └─vg_oracle-product 252:2    0  100G  0 lvm  /u01
    sdc                     8:32   0   30G  0 disk 
    sdd                     8:48   0   20G  0 disk 
    sr0                    11:0    1 56.3M  0 rom 
    [root@vmware oracle]# fdisk /dev/sdc 
    [root@vmware oracle]# fdisk /dev/sdd
    [root@vmware ~]$ cat /etc/udev/rules.d/99-oracle.rules 
    KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VB2060caf8-3d055279", SYMLINK+="asm-disk1", OWNER="oracle", GROUP="oinstall", MODE="0660"
    KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VB0d4b5c6c-5b81abe5", SYMLINK+="asm-disk2", OWNER="oracle", GROUP="oinstall", MODE="0660"
    [root@vmware rules.d]# systemctl restart systemd-udevd

  2. Install GI and DB Home

  3. Prepare your spfile

    [oracle@vmware dbs]$ strings spfileORCL.ora 
    *.audit_file_dest='/u01/app/oracle/admin/ORCL_SITE2/adump'
    *.thread=1
    *.audit_trail='db','extended'
    *.cluster_database=false
    *.compatible='12.1.0.2.0'
    *.control_file_record_keep_time=90
    *.db_block_size=8192
    *.standby_file_management='AUTO'
    *.db_create_file_dest='+DATA'
    *.db_file_name_convert='ORCL_SITE1','ORCL_SITE2'
    *.db_name='ORCL'
    *.db_recovery_file_dest='+FRA'
    *.db_recovery_file_dest_size=10g
    *.db_securefile='ALWAYS'
    *.db_unique_name='ORCL_SITE2'
    *.diagnostic_dest='/u01/app/oracle'
    *.log_file_name_convert='ORCL_SITE1','ORCL_SITE2'
    *.pga_aggregate_target=200m
    *.remote_login_passwordfile='exclusive'
    *.sga_target=600m
    *.undo_tablespace='UNDOTBS1'
    

  4. Prepare listener

    [oracle@vmware admin]$ cat listener.ora
    # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2_grid/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = vmware.example.com)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = ORCL_SITE2_DGMGRL.example.com)
          (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2)
          (SID_NAME = ORCL)
        )
      )
    
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
    VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET		# line added by Agent
    
    [oracle@vmware admin]$ srvctl stop listener
    [oracle@vmware admin]$ srvctl start listener
    [oracle@vmware admin]$ lsnrctl services
    
    LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-FEB-2017 11:37:56
    
    Copyright (c) 1991, 2014, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmware.example.com)(PORT=1521)))
    Services Summary...
    Service "ORCL_SITE2_DGMGRL.example.com" has 1 instance(s).
      Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0
             LOCAL SERVER
    The command completed successfully
    

  5. Create a passwordfile

  6. Define your connect identifiers

    [oracle@vmware admin]$ cat tnsnames.ora 
    ORCL_SITE1 = (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL_SITE1_DGMGRL))(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.example.com)(PORT=1521)))
    ORCL_SITE2 = (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL_SITE2_DGMGRL))(ADDRESS=(PROTOCOL=TCP)(HOST=vmware.example.com)(PORT=1521)))
    

  7. Once you can login from these aliases without problems you can duplicate the DB

    [oracle@vmware dbs]$ tnsping orcl_site1
    
    TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 14-FEB-2017 11:46:22
    
    Copyright (c) 1997, 2014, Oracle.  All rights reserved.
    
    Used parameter files:
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL_SITE1_DGMGRL))(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.example.com)(PORT=1521)))
    OK (0 msec)
    [oracle@vmware dbs]$ rman target=sys/oracle@orcl_site1 auxiliary=sys/oracle@orcl_site2
    
    Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 14 11:46:24 2017
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: ORCL (DBID=145047211)
    connected to auxiliary database: ORCL (not mounted)
    
    RMAN> duplicate target database for standby from active database dorecover ;
    Starting Duplicate Db at 14-FEB-17
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=22 device type=DISK
    current log archived
    
    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  '+DATA/ORCL_SITE1/PASSWORD/pwdorcl_site1.260.935837221' auxiliary format 
     '/u01/app/oracle/product/12.1.0.2/dbs/orapwORCL'   ;
    }
    executing Memory Script
    
    Starting backup at 14-FEB-17
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=57 instance=ORCL2 device type=DISK
    Finished backup at 14-FEB-17
    
    contents of Memory Script:
    {
       sql clone "alter system set  control_files = 
      ''+DATA/ORCL_SITE2/CONTROLFILE/current.257.935927295'', ''+FRA/ORCL_SITE2/CONTROLFILE/current.256.935927295'' comment=
     ''Set by RMAN'' scope=spfile";
       restore clone from service  'orcl_site1' standby controlfile;
    }
    executing Memory Script
    
    sql statement: alter system set  control_files =   ''+DATA/ORCL_SITE2/CONTROLFILE/current.257.935927295'', ''+FRA/ORCL_SITE2/CONTROLFILE/current.256.935927295'' comment= ''Set by RMAN'' scope=spfile
    
    Starting restore at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    output file name=+DATA/ORCL_SITE2/CONTROLFILE/current.259.935927295
    output file name=+FRA/ORCL_SITE2/CONTROLFILE/current.258.935927295
    Finished restore at 14-FEB-17
    
    contents of Memory Script:
    {
       sql clone 'alter database mount standby database';
    }
    executing Memory Script
    
    sql statement: alter database mount standby database
    RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
    
    contents of Memory Script:
    {
       set newname for tempfile  1 to 
     "+DATA";
       switch clone tempfile all;
       set newname for datafile  1 to 
     "+DATA";
       set newname for datafile  2 to 
     "+DATA";
       set newname for datafile  3 to 
     "+DATA";
       set newname for datafile  4 to 
     "+DATA";
       set newname for datafile  5 to 
     "+DATA";
       restore
       from service  'orcl_site1'   clone database
       ;
       sql 'alter system archive log current';
    }
    executing Memory Script
    
    executing command: SET NEWNAME
    
    renamed tempfile 1 to +DATA in control file
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    Starting restore at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    Finished restore at 14-FEB-17
    
    sql statement: alter system archive log current
    current log archived
    
    contents of Memory Script:
    {
       restore clone force from service  'orcl_site1' 
               archivelog from scn  445938;
       switch clone datafile all;
    }
    executing Memory Script
    
    Starting restore at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=35
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=36
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=2 sequence=14
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=2 sequence=15
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=2 sequence=16
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    Finished restore at 14-FEB-17
    
    datafile 1 switched to datafile copy
    input datafile copy RECID=6 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/system.260.935927305
    datafile 2 switched to datafile copy
    input datafile copy RECID=7 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/sysaux.261.935927319
    datafile 3 switched to datafile copy
    input datafile copy RECID=8 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/undotbs2.262.935927327
    datafile 4 switched to datafile copy
    input datafile copy RECID=9 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/undotbs1.263.935927335
    datafile 5 switched to datafile copy
    input datafile copy RECID=10 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/users.264.935927337
    
    contents of Memory Script:
    {
       set until scn  446200;
       recover
       standby
       clone database
        delete archivelog
       ;
    }
    executing Memory Script
    
    executing command: SET until clause
    
    Starting recover at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    starting media recovery
    
    archived log for thread 1 with sequence 35 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_35.259.935927347
    archived log for thread 1 with sequence 36 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_36.260.935927349
    archived log for thread 2 with sequence 15 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_15.262.935927351
    archived log for thread 2 with sequence 16 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_16.263.935927353
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_35.259.935927347 thread=1 sequence=35
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_15.262.935927351 thread=2 sequence=15
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_36.260.935927349 thread=1 sequence=36
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_16.263.935927353 thread=2 sequence=16
    media recovery complete, elapsed time: 00:00:00
    Finished recover at 14-FEB-17
    Finished Duplicate Db at 14-FEB-17
    
    RMAN> 

  8. Register this database

    srvctl add database -db ORCL_SITE2 -oraclehome /u01/app/oracle/product/12.1.0.2 -domain example.com -spfile /u01/app/oracle/product/12.1.0.2/dbs/spfileORCL.ora -pwfile /u01/app/oracle/product/12.1.0.2/dbs/orapwORCL -role PHYSICAL_STANDBY -startoption mount -dbname ORCL -diskgroup "DATA,FRA"
    srvctl enable database -db ORCL_SITE2

  9. Start dg broker

    SQL> show parameter dg_broker
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    dg_broker_config_file1		     string	 /u01/app/oracle/product/12.1.0
    						 .2/dbs/dr1ORCL_SITE2.dat
    dg_broker_config_file2		     string	 /u01/app/oracle/product/12.1.0
    						 .2/dbs/dr2ORCL_SITE2.dat
    dg_broker_start 		     boolean	 FALSE
    SQL> alter system set dg_broker_start=true ;
    
    System altered.

  10. Create standby logfiles

    alter database add standby logfile thread 1 ;
    alter database add standby logfile thread 1 ;
    alter database add standby logfile thread 1 ;
    
    alter database add standby logfile thread 2 ;
    alter database add standby logfile thread 2 ;
    alter database add standby logfile thread 2 ;

  11. Create DG Broker Configuration

    [oracle@vmware dbs]$ dgmgrl sys/oracle@orcl_site1
    DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
    
    Copyright (c) 2000, 2013, Oracle. All rights reserved.
    
    Welcome to DGMGRL, type "help" for information.
    Connected as SYSDBA.
    DGMGRL> create configuration dg_orcl as PRIMARY DATABASE IS orcl_site1 connect identifier is orcl_site1 ;
    Configuration "dg_orcl" created with primary database "orcl_site1"
    DGMGRL> add database orcl_site2 as connect identifier is orcl_site2 ;
    Database "orcl_site2" added
    DGMGRL> enable configuration ;
    Enabled.

  12. Check Configuration

    DGMGRL> validate database orcl_site1
    
      Database Role:    Primary database
    
      Ready for Switchover:  Yes
    
      Flashback Database Status:
        orcl_site1:  Off
    
    DGMGRL> validate database orcl_site2
    
      Database Role:     Physical standby database
      Primary Database:  orcl_site1
    
      Ready for Switchover:  Yes
      Ready for Failover:    Yes (Primary Running)
    
      Capacity Information:
        Database     Instances        Threads        
        orcl_site1   2                2              
        orcl_site2   1                2              
        Warning: the target standby has fewer instances than the
        primary database, this may impact application performance
    
      Flashback Database Status:
        orcl_site1:  Off
        orcl_site2:  Off
    

And you have now a standby database.

Posted in 2016

Docker with 12c SE2, step by step

Hi all,
maybe you are interrested in docker or you want to learn new things like me, I have created a docker guide to a point where you can tnsping the listener. To create a database would be just uninterresting. Everything is in the documentation too, but there is always someone who wants it faster. So you will find it there too.. serv-1 is my virtualbox machine for hosting the containers which I call host-[1,2,3] in this text. I have downloaded 12c SE2 zip files and stored them in /software in serv-1.

  1. find a device for btrfs
    [root@serv-1 ~]# lsblk
    NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
    sda 8:0 0 30G 0 disk
    ├─sda1 8:1 0 500M 0 part /boot
    └─sda2 8:2 0 29.5G 0 part
    ├─ol-root 251:0 0 28.5G 0 lvm /
    └─ol-swap 251:1 0 1G 0 lvm [SWAP]
    sdb 8:16 0 50G 0 disk
    sr0 11:0 1 1024M 0 rom
    

  2. create a btrfs on a disk
    [root@serv-1 ~]# mkfs.btrfs /dev/sdb -f
    

  3. define a target to mount btrfs

    [root@serv-1 ~]# cat /etc/systemd/system/var-lib-docker.mount
    [Unit]
    Description = Docker Image Store
    
    [Mount]
    What = UUID=b1d40d65-f3c8-4774-9b70-ac3a73d49339
    Where = /var/lib/docker
    Type = btrfs
    
    [Install]
    WantedBy = multi-user.target
    
    [root@serv-1 ~]# systemctl enable var-lib-docker.mount
    [root@serv-1 ~]# systemctl start var-lib-docker.mount
    

  4. Install the docker engine

    [root@serv-1 ~]# yum install docker-engine
    Installed:
    docker-engine.x86_64 0:1.12.2-1.0.1.el7
    

  5. Filesystem starts first
    [root@serv-1 docker]# cat /etc/systemd/system/docker.service.d/var-lib-docker-mount.conf
    [Unit]
    Requires=var-lib-docker.mount
    After=var-lib-docker.mount
    
  6. Start docker

    [root@serv-1 ~]# systemctl start docker
    [root@serv-1 ~]# systemctl enable docker
    Created symlink from /etc/systemd/system/multi-user.target.wants/docker.service to
    /usr/lib/systemd/system/docker.service.
    

  7. Pull an oraclelinux image

    [root@serv-1 ~]# docker pull oraclelinux:7
    

  8. Now you can start a container

    [root@serv-1 ~]# docker run -i -t -d --name guest1 --restart=always oraclelinux:7
    

  9. To login you can either “docker attach” or “docker exec”, I learned that exec is better with starting a container with -d

    [root@serv-1 ~]# docker exec -i -t guest1 /bin/bash
    

  10. Preparing the docker image (oraclelinux) for Oracle software, for this I created a script and mount a readonly directory to the container

    [root@serv-1 software]# pwd
    /software
    [root@serv-1 software]# cat define_oracle.sh
    export http_proxy=http://172.X.X.X:8080
    echo "Preparing environment..."
    mkdir -p /products/oracle/install && \
    groupadd dba && \
    useradd -g dba -d /home/oracle -m -s /bin/bash oracle && \
    echo oracle:oracle | chpasswd
    sleep 2
    echo "Installing necessary packages..."
    yum -y install unzip tar openssl wget binutils.x86_64 \
    compat-libcap1.x86_64 \
    compat-libstdc++-33.i686 \
    compat-libstdc++-33.x86_64 \
    gcc.x86_64 \
    gcc-c++.x86_64 \
    glibc.i686 \
    glibc.x86_64 \
    glibc-devel.i686 \
    glibc-devel.x86_64 \
    ksh \
    libaio.i686 \
    libaio.x86_64 \
    libaio-devel.i686 \
    libaio-devel.x86_64 \
    libgcc.i686 \
    libgcc.x86_64 \
    libstdc++.i686 \
    libstdc++.x86_64 \
    libstdc++-devel.i686 \
    libstdc++-devel.x86_64 \
    libXi.i686 \
    libXi.x86_64 \
    libXtst.i686 \
    libXtst.x86_64 \
    make.x86_64 \
    sysstat.x86_64 | tee -a /tmp/install_packages.log > /dev/null 2>&1
    
    yum clean all > /dev/null 2>&1
    mkdir /products/oracle/oradata
    unzip -d /products/oracle/install/ /media/software/linuxamd64_12102_database_se2_1of2.zip | tee -a /tmp/unzip.log > /dev/null 2>&1
    
    unzip -d /products/oracle/install/ /media/software/linuxamd64_12102_database_se2_2of2.zip | tee -a /tmp/unzip.log > /dev/null 2>&1
    
    chown -R oracle:dba /products
    echo "Preparation finished..."
    

With this command I mount the /software directory with the oracle zip files and my script

[root@serv-1 software]# docker run -i -t --hostname host-01 --name guest1 --restart=always --shm-size 1GB -v /software/:/media/software:ro oraclelinux:7
[root@host-01 /]#
[root@host-01 /]# /media/software/define_oracle.sh
Preparing environment...
Installing necessary packages...
Preparation finished...
[oracle@host-01 /]$ cd /products/oracle/install/database/response
  1. Here you are already in the part where you can define the response file for the installation, once you modified it, you can run the installation
    [oracle@host-01 database]$ ./runInstaller -silent -responseFile
    /products/oracle/install/database/response/db_install.rsp -ignorePrereq
    Starting Oracle Universal Installer...
    Checking Temp space: must be greater than 500 MB. Actual 45844 MB Passed
    Checking swap space: must be greater than 150 MB. Actual 964 MB Passed
    
    Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-12-30_12-27-32PM. Please
    wait ...[oracle@host-01 database]$ [WARNING] [INS-32055] The Central Inventory is located in the
    Oracle base.
    
    ACTION: Oracle recommends placing this Central Inventory in a location outside the Oracle base directory.
    
    You can find the log of this install session at:
    
    /products/oracle/oraInventory/logs/installActions2016-12-30_12-27-32PM.log
    
    The installation of Oracle Database 12c was successful.
    Please check '/products/oracle/oraInventory/logs/silentInstall2016-12-30_12-27-32PM.log' for more details.
    
    As a root user, execute the following script(s):
    1. /products/oracle/oraInventory/orainstRoot.sh
    2. /products/oracle/dbhome/12.1.0.2/root.sh
    
    Successfully Setup Software.
    [oracle@host-01 database]$ exit
    exit
    [root@host-01 /]# /products/oracle/oraInventory/orainstRoot.sh
    Changing permissions of /products/oracle/oraInventory.
    Adding read,write permissions for group.
    Removing read,write,execute permissions for world.
    Changing groupname of /products/oracle/oraInventory to dba.
    The execution of the script is complete.
    
    [root@host-01 /]# /products/oracle/dbhome/12.1.0.2/root.sh
    Check /products/oracle/dbhome/12.1.0.2/install/root_host-01_2016-12-30_12-35-10.log for the
    output of root script
    
  2. Remove the installation files so your image is not big

    [root@host-01 /]# cd /products/oracle/install/
    [root@host-01 install]# ll
    total 0
    drwxr-xr-x 1 oracle dba 110 Jul 6 2015 database
    [root@host-01 install]# rm -rf database/
    [root@host-01 install]#
    
  3. Login to the serv-1 and stop the container

    login as: root
    root@192.168.56.102's password:
    Last login: Fri Dec 30 07:29:54 2016 from 192.168.56.1
    [root@serv-1 ~]# docker stop guest1
    guest1
    [root@serv-1 ~]#
  4. Create your image, this takes time..

    [root@serv-1 ~]# docker commit -m "Oracle 12c SE2" -a "Peter Sorger" `docker ps -l -q` oracledb12c/se2:v1.0
    
  5. Create your first host for the database, I define the port mapping (serv-1:host-1)

    [root@serv-1 ~]# docker run -i -t -d --hostname host-01 --name guest1 --restart=always --shm-size 1GB 
    -p 1521:1521 oracledb12c/se2:v1.0
  6. Login and start the listener, int this step you could create the database too, but that’s another story or even create the database, define everything, commit and deploy, as you want

    [root@serv-1 ~]# docker exec -it guest1 /bin/bash
    [root@host-01 /]# su oracle
    [oracle@host-01 /]$ . oraenv
    ORACLE_SID = [oracle] ?
    ORACLE_HOME = [/home/oracle] ? /products/oracle/dbhome/12.1.0.2
    The Oracle base has been set to /products/oracle
    [oracle@host-01 /]$ lsnrctl start
    LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-DEC-2016 12:59:21
    Starting /products/oracle/dbhome/12.1.0.2/bin/tnslsnr: please wait...
    TNSLSNR for Linux: Version 12.1.0.2.0 - Production
    
    Log messages written to /products/oracle/diag/tnslsnr/host-01/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host-01)(PORT=1521)))
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
    Start Date 30-DEC-2016 12:59:21
    Uptime 0 days 0 hr. 0 min. 0 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Log File /products/oracle/diag/tnslsnr/host-01/listener/alert/log.xml
    
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host-01)(PORT=1521)))
    The listener supports no services
    The command completed successfully
    [oracle@host-01 /]$ exit
    [root@host-01 /]# exit 
  7. Install a client or any application that can connect to the listener or database if you created it. I have installed a client.

    [oracle@serv-1 client]$ ./runInstaller -silent -responseFile /software/client/response/client_install.rsp -ignorePrereq
    
    You can find the log of this install session at:
    /products/oracle/oraInventory/logs/installActions2016-12-30_08-43-47AM.log
    The installation of Oracle Client 12c was successful.
    
    Please check '/products/oracle/oraInventory/logs/silentInstall2016-12-30_08-43-47AM.log' for more details.
    
    As a root user, execute the following script(s):
    1. /products/oracle/oraInventory/orainstRoot.sh
    Successfully Setup Software.
    [oracle@serv-1 client]$ exit
    [root@serv-1 client]# /products/oracle/oraInventory/orainstRoot.sh
    Changing permissions of /products/oracle/oraInventory.
    Adding read,write permissions for group.
    Removing read,write,execute permissions for world.
    Changing groupname of /products/oracle/oraInventory to dba.
    The execution of the script is complete. 
  8. Connect to the database

    [root@serv-1 client]# su oracle
    [oracle@serv-1 client]$ export ORACLE_HOME=/products/oracle/client
    [oracle@serv-1 client]$ export LD_LIBRARY_PATH=/products/oracle/client/lib
    [oracle@serv-1 client]$ export PATH=$PATH:/products/oracle/client/bin
    [oracle@serv-1 client]$ tnsping localhost:1521/test
    
    TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-DEC-2016 08:47:54
    Copyright (c) 1997, 2014, Oracle. All rights reserved.
    
    Used parameter files:
    Used HOSTNAME adapter to resolve the alias
    
    Attempting to contact
    (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=test))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
    OK (370 msec)
    

And now you can work with your database in a container.

Posted in 2016

How to Update data 10 at a time..

SYS> create user test identified by test default tablespace users ;

User created.

SYS> grant connect, resource to test ;

Grant succeeded.

SYS> grant execute on dbms_lock to test ;

Grant succeeded.

SYS> alter user test quota unlimited on users ;

User altered.

TEST> create table data (laenge number, breite number, shape number) ;

Table created.

TEST> insert into data (laenge,breite) select dbms_random.value(1,100),dbms_random.value(100,200) from dual connect by level<=100;
commit;

CREATE or REPLACE procedure EXECUTE_GEOMETRY_BUILDING_TEST
IS
CURSOR c_geodata IS SELECT LAENGE, BREITE, SHAPE FROM data ;
TYPE t_geodata_table IS TABLE OF c_geodata%rowtype INDEX by PLS_INTEGER ;
values_geodata t_geodata_table ;
BEGIN
open c_geodata ;
loop
    fetch c_geodata BULK COLLECT INTO values_geodata LIMIT 10 ;

EXIT WHEN
    values_geodata.COUNT = 0;  

for idx in 1..values_geodata.count
LOOP
    UPDATE data
    SET SHAPE = 1000 where laenge=values_geodata(idx).laenge and breite=values_geodata(idx).breite ;
/* here I update only that rows which I selected */
END LOOP ;
commit ; /* commit so I can see them in a second session, and maybe reuse them */
dbms_lock.sleep(20) ; 

END LOOP ;
close c_geodata ;

END EXECUTE_GEOMETRY_BUILDING_TEST;
/