IBM Fluid Query installation

[root@ip-xxxxxxxxx Fluid_1.7]# ./fluidquery_install.sh --datamove_path /mnt/fluid/
-------------------------------------------------------------------------------
IBM Fluid Query Installer
(C) Copyright IBM Corp. 2015, 2016. All rights reserved.
Version 1.7.0.1 [Build 161026-10]
-------------------------------------------------------------------------------
Installing data movement...
Data movement path: /mnt/fluid/
Checking for previous installation of IBM Fluid Query data movement...
Previous installation of data movement does not exist in /mnt/fluid/...
Creating HDFS directory /fluidquery...
HDFS directory /fluidquery created.
Copying files to /mnt/fluid/...
Copying license to /mnt/fluid/...
Copying uninstaller to /mnt/fluid/...
Copying jars to HDFS...
IBM Fluid Query data movement installed succesfully in /mnt/fluid/!
For details see the log file at /mnt/fluid//var/log/fluidquery_install/fluidquery_install.2017-06-20.15:57:59.log


[root@ip-xxxxxxxxxxxxx fluid]# export HADOOP_CLASSPATH=/mnt/fluid/nzjdbc3.jar
[root@ip-xxxxxxxxxxxx fluid]# hadoop jar /mnt/fluid/nzcodec.jar -conf fq-import-remote-updated.xml
FluidQuery version: 1.7.0.1 [Build 161026-10]

 https://www.ibm.com/support/knowledgecenter/en/SSULQD_1.7.0/com.ibm.nz.hdp.doc/tt_dm_lesson_12.html

https://www.ibm.com/support/knowledgecenter/en/SSULQD_1.7.0/com.ibm.nz.hdp.doc/r_hdp_fdm_ts.html

AWS cli query examples

using the query option in aws cli makes it very easy to work with cli

below are few examples of using JMESPath query :

•   use of contains

 aws emr list-clusters --region us-east-1 --query 'Clusters[?Name==`{{ clustername }}`]|[?contains(Status.State, `TERMINATED`) == `false`].[Id]

• use of length to get the count

aws emr list-clusters --region us-east-1 --query 'Clusters[?Name==`{{ current_run_reference }}`]|[?contains(Status.State, `TERMINATED`) == `false`].[Id]|length(@)' 

• list route53 records of a IP

aws route53 list-resource-record-sets --hosted-zone-id ZXXXXXXXXXXXXX --query 'ResourceRecordSets[?ResourceRecords[0].Value==`10.253.xx.xxx`]' 

• Filter with more than one condition

aws route53 list-resource-record-sets --hosted-zone-id ZXXXXXXXXXXXXX --query 'ResourceRecordSets[?ResourceRecords[0].Value==`10.253.xx.xx`]|[?Type==`CNAME`]'

Postgres column encryption using pgcrypto on RDS

This should work on NON-RDS postgres databases also

Add pycrypto extension

CREATE EXTENSION pgcrypto;

To check if pycrypto is added:

SHOW rds.extensions;

pgsql offers two method of column encryption, Below is example where we need encryption and decrytion as well.
perform the steps to create a public and private before.

create a table:
CREATE TABLE secured_table(property SERIAL PRIMARY KEY, property varchar(100), value bytea);


test for rows :
qa=> select * from secured_table;
 id | property | value
----+----------+-------
(0 rows)

insert encrypted data:


INSERT INTO secured_table(property, value)
SELECT robotccs.property, pgp_pub_encrypt(robotccs.value, keys.pubkey) As value
FROM (VALUES ('awskey', 'xfadgghlrgsadff'),
    ('secretkey', 'aCFDGsfsabasfafdh') ) As robotccs(property, value)
     CROSS JOIN (SELECT dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
-----END PGP PUBLIC KEY BLOCK-----') As pubkey) As keys;



check rows after insert , it shows only encrypted values:

qa=> select * from secured_table;
 id | property  | value
----+-----------+----------------------------------------------
  1 | awskey    | \xc1c04c038068b7aeb7cbdfd5010800b10b11ef0fa2
  2 | secretkey | \xc1c04c038068b7aeb7cbdfd50107ff5cc130e2e216
(2 rows)

to find decrypted original values:

SELECT property, pgp_pub_decrypt(value, keys.privkey) As ccdecrypt
FROM secured_table
    CROSS JOIN
    (SELECT dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
-----END PGP PRIVATE KEY BLOCK-----') As privkey) As keys;
 property  |     ccdecrypt
-----------+-------------------
 awskey    | xfadgghlrgsadff
 secretkey | aCFDGsfsabasfafdh
(2 rows)

STEPS TO GENERATE THE KEY:

[user@server ~]$ gpg --gen-key
gpg (GnuPG) 2.0.14; Copyright (C) 2009 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Please select what kind of key you want:
   (1) RSA and RSA (default)
   (2) DSA and Elgamal
   (3) DSA (sign only)
   (4) RSA (sign only)
Your selection?
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048)
Requested keysize is 2048 bits
Please specify how long the key should be valid.
         0 = key does not expire
      <n>  = key expires in n days
      <n>w = key expires in n weeks
      <n>m = key expires in n months
      <n>y = key expires in n years
Key is valid for? (0)
Key does not expire at all
Is this correct? (y/N) y

GnuPG needs to construct a user ID to identify your key.

Real name: admin1
Email address: admin1@company.com
Comment:
You selected this USER-ID:
    "admin1 <admin1@company.com>"

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.

can't connect to `/home/user/.gnupg/S.gpg-agent': No such file or directory
You don't want a passphrase - this is probably a *bad* idea!
I will do it anyway.  You can change your passphrase at any time,
using this program with the option "--edit-key".

We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.

We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
asgdhgjasdhad
asghadfhj


gpg: /home/user/.gnupg/trustdb.gpg: trustdb created
gpg: key 966B48BB marked as ultimately trusted
public and secret key created and signed.

gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 1u
pub   2048R/966B48BB 2016-09-27
      Key fingerprint = 7243 4195 E241 8B7E A994  6817 8F12 326A 966B 48BB
uid                  admin1 <admin1@company.com>
sub   2048R/B7CBDFD5 2016-09-27


[[user@server ~]$ gpg --list-secret-keys
/home/user/.gnupg/secring.gpg
---------------------------------
sec   2048R/966B48BB 2016-09-27
uid                  admin1 <admin1@company.com>
ssb   2048R/B7CBDFD5 2016-09-27

[user@server ~]$ gpg -a --export B7CBDFD5
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)

mQENBFfqtg0BCADixmjg0xac4ylRMBdQhlu+K1iDNeYJWYh1IqpiTwZEfO2mSpm+
uQVk2jHNs9n3oDTng9ggtkq5gQDvDmsIk4X2LqXEgzKaMIQRZAVTL5CbRtUManbn
ANPOrlPtLQJ301T5faZ7EnosSvEtVyNqapnwkkBG7KSMnUfSGLo2RrdlPMPPvWjg
c35wN3Yqr/U02cxkYyDcmPIpzJD3HdwlDCb2d5PAo89gcu9oTT5k54cBBMx17Izc
ge1rdMyvAKdaoJyHrgjItu9SE81i3ydDZJ8FkStt2coxGJXmN9V6rZCM9YxEjnwr
u0yEne7bNep8XnN8OU1QD62IsJfgX0MFykaLC2ndp0kaEbPfztjTp9keGFiTPsLY
MvNf28LwEoXc0uVhVGnoHOkCbVEVHg75jQxKAqnqTMh78TcqxyxrVf2gKPNhN/T4
YhA7t5x5u0YDy0K2FaRdKiZQv1+mcrOcpvY=
=6cNj
-----END PGP PUBLIC KEY BLOCK-----
[user@server ~]$ gpg -a --export-secret-keys 966B48BB
-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)

lQOYBFfqtg0BCADixmjg0xac4ylRMBdQhlu+K1iDNeYJWYh1IqpiTwZEfO2mSpm+
uQVk2jHNs9n3oDTng9ggtkq5gQDvDmsIk4X2LqXEgzKaMIQRZAVTL5CbRtUManbn
ANPOrlPtLQJ301T5faZ7EnosSvEtVyNqapnwkkBG7KSMnUfSGLo2RrdlPMPPvWjg
c35wN3Yqr/U02cxkYyDcmPIpzJD3HdwlDCb2d5PAo89gcu9oTT5k54cBBMx17Izc
ge1rdMyvAKdaoJyHrgjItu9SE81i3ydDZJ8FkStt2coxGJXmN9V6rZCM9YxEjnwr
WUrCdJ6Vx78b9YfoxkTjYK9xMhG+xj7bZanbABEBAAEAB/0cXIdwrsBiPgAZ2POM
LPGRDBIVft/WhYMNG0PHPcOL5zqVlI474x9Ak2VX057MBcD4FMN/+9cb8igFaH6M
Y7Qvq3qB5Df86VprDmD4HA0azmCwx9PiZMvDSobRNSgyMj+rYEKPm9T5xc758QRL
N9UpgtuqbcglPzpklUEcrqVwcfe3D6fqfrpUPdGqzfdH2LEOF5cydFnRzbuswsAu
ySXnWbuaDTgMxO2w5f/7VodPzK5wu2tsPwvVos1GKqiKPHlfLWhOn3qNpk6ODRIM
/ioZ+hXVu0yEne7bNep8XnN8OU1QD62IsJfgX0MFykaLC2ndp0kaEbPfztjTp9ke
GFiTPsLYMvNf28LwEoXc0uVhVGnoHOkCbVEVHg75jQxKAqnqTMh78TcqxyxrVf2g
KPNhN/T4YhA7t5x5u0YDy0K2FaRdKiZQv1+mcrOcpvY=
=MRtJ
-----END PGP PRIVATE KEY BLOCK-----
[user@server ~]$

procedure to drop empty partitions

set serveroutput on
declare
v_count number(4);
Var_sql varchar2(1000);
Var_sql1 varchar2(1000);
BEGIN
  FOR item IN
  ( SELECT partition_name
from dba_tab_partitions
where partition_name in ('P20061412_7','P20061412_5')
and table_name='SC_RAW'
and table_owner='RRS' )
  LOOP
var_sql:='select count(1)  from rrs.SC_RAW partition(' || item.partition_name || ')';
execute immediate var_sql into v_count;
dbms_output.put_line(var_sql);
dbms_output.put_line(v_count);
        if v_count=0
then
dbms_output.put_line('dropping this partition');
var_sql1:='alter table rrs.sc_raw drop partition '||item.partition_name ;
dbms_output.put_line(var_sql1);
execute immediate var_sql1;
END IF;
   END LOOP;
END;
/

Technical Explanation is included below 

Usually  CELLSRV observes that the service time on the disk increases, its proactively marked as confinedOffline.  This is a first phase of detection of poor performance on the disk. At this stage, we see the alert being generated. After this, the disk is ready for further tests.      Now MS runs the performance tests on the CELLDISK. If it turns out that the disk is performing well, MS will notify CELLSRV that the disk is fine and CELLSRV will notify ASM to put the grid disks ONLINE.   If it comes out that the performance is bad in the tests, the disk status is changed to proactive failure.    Now the GRIDDISK  are dropped from ASM. Then the status of the disk is set to predictive failure.   If MS has indicated that the disk is performing well and hence is set back to normal and a notification for the alert clearance is updated in alert history.   This is all automated operation and no interference is required. 

As the alert is cleared and disk status changed to NORMAL , there is no further action required on this disk.  However in your case as indicated by Travis yesterday this frequently happening, so this need to go to Oracle Support immediately. 

Do you have the full rman log? To restore a cold backup you have to use the noredo.

restore database;
recover database noredo;



1. I need additional information to analyze and troubleshoot your issue.


You can copy and paste into sqlplus the complete script and it will create
results01.txt. I will review it and see what our options are.

Note: Do this as the SYSDBA.

REM # Please run the following SQL statements, as shown below, via SQL*Plus,
REM # and then upload the "results01.txt" for further analysis. You can run
REM # these statements while the database is at least mounted. Cut and paste
REM # the script into sqlplus.
REM ##########################################################################
REM

spool results01.txt
set echo on feedback on
set pagesize 50
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select * from v$version;
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as current_date from dual;
column name format a10
select dbid, name,
to_char(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode, log_mode,
to_char(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
to_char(controlfile_change#, '999999999999999') as controlfile_change#,
to_char(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time
from v$database;

select * from v$instance;

SELECT to_char(Current_scn, '9999999999999999') FROM V$DATABASE;

select to_char(checkpoint_change#, '999999999999999') as ck_chg from v$database;

select min(fhscn) from x$kcvfh;


Select min(fhafs) "minvalue" ,max(fhafs) "max value" from x$kcvfh;

select max(to_number(fhafs)) "Minimum PITR SCN", min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

archive log list;


select SEQUENCE#, to_char(FIRST_CHANGE#,'999999999999999') as CHK_CHANGE, group#, archived, status,thread#
from v$log order by first_change#;


column member format a45
select group#, status,type,member from v$logfile;

column name format a79
select name, file#, status, enabled, creation_change#,
to_char(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time, to_char(checkpoint_change#, '999999999999999') as checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, to_char(offline_change#, '999999999999999') as offline_change#, to_char(online_change#, '999999999999999') as online_change#, to_char(online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time, to_char(bytes, '9,999,999,999,990') as bytes
from v$datafile
where status <> 'ONLINE'
or checkpoint_change# <> (select checkpoint_change# from v$database);


select distinct to_char(checkpoint_change#, '9999999999999999') from v$datafile;

select distinct(status)from v$datafile;
select FILE#,TS# , status, NAME from v$datafile
where status not in ('SYSTEM','ONLINE')
order by 1;

select distinct (to_char(checkpoint_change#,'9999999999999999')) from v$datafile_header;

REM #
REM # fuzzy (fhsta=4 for example).
REM # Status to open the database is fhsta=0 (except system datafile(s) may be something like 8192)
REM # status 4 which means "online fuzzy" this datafile should be restored again.
REM # You can only cancel recovery once all datafiles are at status 0 using this query
REM# Absolutely FuZzy in Status 16 / 64 / 80 and the database will not open
REM# Status 16 - Media recovery fuzzy
REM# Status 64 - Absolute Fuzzy If you see status 64 redo the recover and apply
REM# the next log, keep going until there are no status 64.


REM# Status 80 - Media fuzzy & Absolute.
REM #Summarize the fhsta information from above in case query above returns hundreds of rows.

select distinct FHSTA from X$KCVFH;

REM#You may run the following query to determine the sequence# of the archivelogs needed for the initial recovery.

select distinct FHRBA_SEQ Sequence, FHTHR Thread from X$KCVFH
order by FHTHR, FHRBA_SEQ;


select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

REM #
REM#

select to_char(hxfil,'99999999999') as FILE#,fhsta STAT,fhscn SCN,
fhthr thrd, fhrba_Seq SEQ,fhtnm T_SPACE
from x$kcvfh order by 1;


select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity, FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;


select df.file#, to_char(df.checkpoint_change#, '9999999999999999') controlfile_change,
to_char(dh.checkpoint_change# , '9999999999999999') datafile_change, df.status
from v$datafile df, v$datafile_header dh
where df.file# = dh.file#;

select a.recid, a.thread#, a.sequence#, a.name, a.archived, a.deleted,
to_date(a.completion_time, 'DD-MON-YYYY HH24:MI:SS') as completed
from v$archived_log a, v$log l
where a.thread# = l.thread#
and a.sequence# = l.sequence#;

REM # If the query return 1 row with STATUS='ONLINE', FUZZY='NO', and similar CHECKPOINT_TIMEthen you REM # can proceed to open the database.

set echo on feedback on time on timing on pagesize 100 linesize 80 numwidth 13
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select error, fuzzy, status, checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by error, fuzzy, status, checkpoint_change#,checkpoint_time
order by checkpoint_change#, checkpoint_time;


select * from v$recovery_log;

select file#, to_char(change#, '9999999999999999') from v$recover_file;

rem# Use the third value: RECOVER DATABASE UNTIL SCN xxxxxxxxxx;

select min(FHSCN) "LOW FILEHDR SCN", max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN"
from X$KCVFH ;


SELECT f.name, f.file#, b.status, to_char(b.change#, '9999999999999999'), b.time
FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';

SELECT status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;

SELECT fhthr thread,
fhrba_seq sequence,
fhscn scn,
fhsta status,
count(*)
FROM x$kcvfh
group by fhthr,fhrba_seq,fhscn,fhsta;


spool off
REM
REM ##########################################################################
REM # Note:
REM # 1. The "results01.txt" will help me analyze the latest status of
REM # the datafiles and controlfile, particularly their checkpoint change#.



---- Upload results01.txt

---- Upload the alert log

3. Do the following so we will know your RMAN configuration.

To get the right time of the backup you have to make sure NLS date is set.

If using csh

setenv NLS_DATE_FORMAT 'DD-MON-YYYY HH24:MI:SS'

If using ksh use:

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

For Windows:

sql 'alter session set NLS_DATE_FORMAT = "Mon dd yyyy hh24:mi:ss"';
set echo on;


crosscheck backup of database;
crosscheck backup of archivelog all;

spool log to rman_report.log;
set echo on;
show all;

list incarnation of database;

list backup summary;

list backup of database archivelog all;

list backup of controlfile;

list archivelog all;

report schema;

list copy of database;

restore preview database;

-- Upload rman_report.log  

Purpose 
-------

To provide Information on time sharing scheduling policy on HP and how Oracle 
makes use of the SCHED_NOAGE scheduling policy.
 
SCOPE & APPLICATION
-------------------

This note is intended for DBAs/ UNIX Admin to improve performance in OLTP.


HP SCHED_NOAGE Scheduling Policy
--------------------------------
On HP, most processes use a time sharing scheduling policy. Time sharing can 
have detrimental effects on Oracle performance by descheduling an Oracle process
during critical operations, for example, holding a latch. HP has a 
modified scheduling policy, referred to as SCHED_NOAGE, that specifically  
addresses this issue. Unlike the normal time sharing policy, a process scheduled
using SCHED_NOAGE does not increase or decrease in priority, nor is it preempted.

This feature is suited to online transaction processing (OLTP) environments 
because OLTP environments can cause competition for critical resources.  
In laboratory tests, the use of the SCHED_NOAGE policy with Oracle9i increased 
performance by up to 10 percent in OLTP environments. The SCHED_NOAGE policy 
creates little or no gains in decision support (DSS) Environments because 
there is little resource competition in these environments. Because each 
application and server environment is different, you should test and verify 
whether your environment benefits from the SCHED_NOAGE policy.


Enabling SCHED_NOAGE for Oracle
-------------------------------


To allow Oracle to use the SCHED_NOAGE scheduling policy, the group that the 
Oracle software owner belongs to (dba), must have the RTSCHED and RTPRIO
privileges to change the scheduling policy and set the priority level for Oracle 
processes. 


To give the dba group these privileges: 

1.As the root user, enter the following command: 

# setprivgrp dba RTSCHED RTPRIO 


2.To retain these privileges after rebooting, create the /etc/privgroup file, if 
it does not exist on your system, and add the following line to it: 

dba RTSCHED RTPRIO

___________________________________________________________________
Note:
Please remember to set the MLOCK privilege as well when using
raw devices.
___________________________________________________________________

3. The Oracle DBA must add the parameter HPUX_SCHED_NOAGE to the INIT.ORA file 
to enable the scheduling policy. HPUX_SCHED_NOAGE takes an integer parameter, 
and for HPUX 11.0 the valid ranges are 154 to 255; for HPUX 11i, the valid 
ranges are 178 to 255. Higher priorities are represented by lower values.

hpux_sched_noage=154 (for HP/UX 11.0)
hpux_sched_noage=178 (for HP/UX 11i)

Overall performance improvements of 5 to 10% especially for OLTP applications
can be expected.

References:
Note:217990.1 Init.ora Parameter  "HPUX_SCHED_NOAGE"  Reference
The HP documentation, rtsched(1) and rtsched(2) man page for more information 
on priority policies and priority ranges.