Skip navigation.

Pakistan's First Oracle Blog

Syndicate content
I Love What I do i.e. Oracle DBA: Blog By Fahd Mirza Chughtai
Updated: 12 hours 58 min ago

Creating User Schema Table and Projections in Vertica

Fri, 2015-08-28 01:25
Vertica is a an exciting database with some real nifty features. Projections is a ground breaking unique feature of Vertica which dramatically increases performance benefits in terms of querying and space benefits in terms of compression.



Following test commands are impromptu sesssion in which a user is being created, then a schema is created, and that user is authorized on that schema. Then a table is created with a default superprojection and then a projection is created and then we see its usage.

Create new vertica database user, create schema and authorize that user to that schema. Create 4 column table and insert data.

select user_name from v_catalog.users;

vtest=> create user mytest identified by 'user123';
CREATE USER
vtest=>

vtest=> \du
      List of users
 User name | Is Superuser
-----------+--------------
 dbadmin   | t
 mytest    | f
(2 rows)

vtest=> \dn
         List of schemas
     Name     |  Owner  | Comment
--------------+---------+---------
 v_internal   | dbadmin |
 v_catalog    | dbadmin |
 v_monitor    | dbadmin |
 public       | dbadmin |
 TxtIndex     | dbadmin |
 store        | dbadmin |
 online_sales | dbadmin |
(7 rows)


vtest=> \q
[dbadmin@vtest1 root]$ /opt/vertica/bin/vsql -U mytest -w user123 -h 0.0.0.0 -p 5433 -d vtest
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit


vtest=> create table testtab (col1 integer,col2 integer, col3 varchar2(78), col4 varchar2(90));
ROLLBACK 4367:  Permission denied for schema public

[dbadmin@vtest1 root]$ /opt/vertica/bin/vsql -U dbadmin -w vtest -h 0.0.0.0 -p 5433 -d vtest
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

vtest=> \du
      List of users
 User name | Is Superuser
-----------+--------------
 dbadmin   | t
 mytest    | f
(2 rows)

vtest=> create schema mytest authorization mytest;
CREATE SCHEMA
vtest=> select current_user();
 current_user
--------------
 dbadmin
(1 row)

vtest=>

vtest=> \q
[dbadmin@vtest1 root]$ /opt/vertica/bin/vsql -U mytest -w user123 -h 0.0.0.0 -p 5433 -d vtest
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

vtest=> create table testtab (col1 integer,col2 integer, col3 varchar2(78), col4 varchar2(90));
CREATE TABLE
vtest=> select current_user();
 current_user
--------------
 mytest
(1 row)

vtest=>

vtest=> \dt
               List of tables
 Schema |  Name   | Kind  | Owner  | Comment
--------+---------+-------+--------+---------
 mytest | testtab | table | mytest |
(1 row)

vtest=> insert into testtab values (1,2,'test1','test2');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (2,2,'test2','test3');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (3,2,'test2','test3');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (4,2,'test4','tesrt3');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (4,2,'test4','tesrt3');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (4,2,'test4','tesrt3');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (4,2,'test4','tesrt3');
 OUTPUT
--------
      1
(1 row)

vtest=> commit;
COMMIT
vtest=>


Create a projection on 2 columns.

Superprojection exists already:

vtest=> select anchor_table_name,projection_name,is_super_projection from projections;
 anchor_table_name | projection_name | is_super_projection
-------------------+-----------------+---------------------
 testtab           | testtab_super   | t
(1 row)

vtest=>


vtest=> \d testtab
                                    List of Fields by Tables
 Schema |  Table  | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+---------+--------+-------------+------+---------+----------+-------------+-------------
 mytest | testtab | col1   | int         |    8 |         | f        | f           |
 mytest | testtab | col2   | int         |    8 |         | f        | f           |
 mytest | testtab | col3   | varchar(78) |   78 |         | f        | f           |
 mytest | testtab | col4   | varchar(90) |   90 |         | f        | f           |
(4 rows)

vtest=>
vtest=> create projection ptest (col1,col2) as select col1,col2 from testtab;
WARNING 4468:  Projection is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION
vtest=>


vtest=> select anchor_table_name,projection_name,is_super_projection from projections;
 anchor_table_name | projection_name | is_super_projection
-------------------+-----------------+---------------------
 testtab           | testtab_super   | t
 testtab           | ptest           | f
(2 rows)


vtest=> select * from ptest;
ERROR 3586:  Insufficient projections to answer query
DETAIL:  No projections eligible to answer query
HINT:  Projection ptest not used in the plan because the projection is not up to date.
vtest=>

vtest=> select start_refresh();
             start_refresh
----------------------------------------
 Starting refresh background process.

(1 row)

vtest=> select * from ptest;
 col1 | col2
------+------
    1 |    2
    2 |    2
    3 |    2
    4 |    2
    4 |    2
    4 |    2
    4 |    2
(7 rows)

vtest=>


 projection_basename | USED/UNUSED |           last_used
---------------------+-------------+-------------------------------
 testtab             | UNUSED      | 1970-01-01 00:00:00-05
 ptest               | USED        | 2015-08-28 07:14:49.877814-04
(2 rows)

vtest=> select * from testtab;
 col1 | col2 | col3  |  col4
------+------+-------+--------
    1 |    2 | test1 | test2
    3 |    2 | test2 | test3
    2 |    2 | test2 | test3
    4 |    2 | test4 | tesrt3
    4 |    2 | test4 | tesrt3
    4 |    2 | test4 | tesrt3
    4 |    2 | test4 | tesrt3
(7 rows)

projection_basename | USED/UNUSED |           last_used
---------------------+-------------+-------------------------------
 ptest               | USED        | 2015-08-28 07:14:49.877814-04
 testtab             | USED        | 2015-08-28 07:16:10.155434-04
(2 rows)
Categories: DBA Blogs

Mongostat ; A Nifty Tool for Mongo DBA

Thu, 2015-08-06 20:56
One of the main Mongodb DBA's task is to monitor the usage of Mongodb system and it's load distribution. This could be needed for proactive monitoring, troubleshooting during performance degradation, root cause analysis, or capacity planning.

Mongostat is a nifty tool which comes out of the box with Mongodb which provides wealth of information in a nicely and familiar formatted way. If you have used vmstat, iostat etc on Linux; Mongostat should seem very familiar.


Mongostat dishes out statistics like counts of database operations by type (e.g. insert, query, update, delete, getmore). The vsize column  in Mongostat output shows the amount of virtual memory in megabytes used by the process. There are other very useful columns regarding network traffic, connections, queuing etc.


Following are some of the examples of running mongostat.

[mongo@mongotest data]$ mongostat

insert query update delete getmore command flushes mapped  vsize    res faults qr|qw ar|aw netIn netOut conn     time
*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:29
*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:30
*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:31
*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:32
*0    *0     *0     *0       0     2|0       0 160.0M 646.0M 131.0M      0   0|0   0|0  133b    10k    1 12:47:33

*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:34
*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:35
*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:36
*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:37
*0    *0     *0     *0       0     2|0       0 160.0M 646.0M 131.0M      0   0|0   0|0  133b    10k    1 12:47:38

Following displayes just 5 rows of output.

[mongo@mongotest data]$ mongostat -n 5
insert query update delete getmore command flushes mapped  vsize    res faults qr|qw ar|aw netIn netOut conn     time

*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:45
*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:46
*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:47
*0    *0     *0     *0       0     1|0       0 160.0M 646.0M 131.0M      0   0|0   0|0   79b    10k    1 12:47:48
*0    *0     *0     *0       0     2|0       0 160.0M 646.0M 131.0M      0   0|0   0|0  133b    10k    1 12:47:49

In order to see full list of options:

[mongo@mongotest data]$ mongostat --help

Usage:
mongostat

Monitor basic MongoDB server statistics.

See http://docs.mongodb.org/manual/reference/program/mongostat/ for more information.

general options:

--help                     print usage
--version                  print the tool version and exit
verbosity options:
-v, --verbose                  more detailed log output (include multiple times for more verbosity, e.g. -vvvvv)
--quiet                    hide all log output
connection options:
-h, --host=                    mongodb host to connect to (setname/host1,host2 for replica sets)
--port=                    server port (can also use --host hostname:port)
authentication options:
-u, --username=                username for authentication
-p, --password=                password for authentication
--authenticationDatabase=  database that holds the user's credentials
--authenticationMechanism= authentication mechanism to use
stat options:
--noheaders                don't output column names
-n, --rowcount=                number of stats lines to print (0 for indefinite)
--discover                 discover nodes and display stats for all
--http                     use HTTP instead of raw db connection
--all                      all optional fields
--json                     output as JSON rather than a formatted table
Categories: DBA Blogs

Shift Command in Shell Script in AIX and Linux

Tue, 2015-07-14 21:42
Shell in Unix never ceases to surprise. Stumbled upon 'shift 2' command in AIX few hours ago and it's very useful.

'Shift n' command shifts the parameters passed to a shell script by 'n' numbers to the left.

For example:

if you have a shell script which takes 3 parameters like:

./mytest.sh arg1 arg2 arg3

and you use shift 2 in your shell script, then the values of arg1 and arg2 will be lost and the value of arg3 will get assigned to arg1.

For example:

if you have a shell script which takes 2 parameters like:

./mytest arg1 and arg2

and you use shift 2, then values of both arg1 and arg2 will be lost.

Following is a working example of shift command in AIX:

testsrv>touch shifttest.sh

testsrv>chmod a+x shifttest.sh

testsrv>vi shifttest.sh

testsrv>cat shifttest.sh
#!/bin/ksh
SID=$1
BACKUP_TYPE=$2
echo "Before Shift: $1 and $2 => SID=$SID and BACKUPTYPE=$BACKUP_TYPE"
shift 2
echo "After Shift: $1 and $2 => SID=$SID and BACKUPTYPE=$BACKUP_TYPE"


testsrv>./shifttest.sh orc daily

Before Shift: orc and daily => SID=orc and BACKUPTYPE=daily
After Shift:  and  => SID=orc and BACKUPTYPE=daily


Note that the values of arguments passed has been shifted to left, but the values of variables has remained intact.
Categories: DBA Blogs

Recover Oracle Undo Tablespace without Backup

Sun, 2015-05-24 20:10
Woke up with an issue regarding a Oracle 10.2.0 database on Linux complaining about an Undo file on startup.


sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 22 20:11:07 2015

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile='init.ora'
ORACLE instance started.

Total System Global Area 2801795072 bytes
Fixed Size                  2075504 bytes
Variable Size            1275069584 bytes
Database Buffers         1509949440 bytes
Redo Buffers               14700544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 244 - see DBWR trace file
ORA-01110: data file 244: '/test/ORADATATEST/test/test_undo2a.dbf'


SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     21600
undo_tablespace                      string      test_UNDO02
SQL>



SQL> drop tablespace test_UNDO02  including contents and datafiles;
drop tablespace test_UNDO02  including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU4$' found, terminate dropping tablespace

 Check for active rollback segments:

 select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE');



Set the following parameter in the pfile.



*._offline_rollback_segments=(_SYSSMU4$)


And now try dropping UNDO tablespace again.

drop tablespace test_UNDO02  including contents and datafiles;

Tablespace dropped.

Now create a new UNDO tablespace:

create UNDO tablespace test_UNDO05 datafile '/test/oradata18/test/test_undo05_file1.dbf' size 500m autoextend on next 1m maxsize 1500m;


Tablespace created.



SQL> create UNDO tablespace test_UNDO05 datafile '/test/oradata18/test/test_undo05_file1.dbf' size 500m autoextend on next 1m maxsize 1500m;


Tablespace created.



SQL> startup pfile='inittest.ora'
ORACLE instance started.

Total System Global Area 2801795072 bytes
Fixed Size                  2075504 bytes
Variable Size            1392510096 bytes
Database Buffers         1392508928 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

All good now.
Categories: DBA Blogs