Pakistan's First Oracle Blog

With numerous new themes and new versions of the wordpress, when you try to publish some post or page or try to send email or try to use contact form, you might get following error:


Fatal error: Call to undefined method PHPMailer::getCustomHeaders()





This error normally occurs if you are using incompatible wordpress and theme and plugin versions. The best course of action is to update your wordpress, your theme, and all the plugin and make sure that contact form 7 plugin isn't complaining about the wordpress version.

If you cannot upgrade your versions or if the error is occuring even after the upgrade, then you can do the following workaround:

Workaround:

Make the following change in your mail.php file in the directory wordpress/wp-content/plugins/contact-form-7/includes

Go to the line 171 and change:
 
add_action( ‘phpmailer_init’, ‘wpcf7_phpmailer_init’ );

to
 
//add_action( ‘phpmailer_init’, ‘wpcf7_phpmailer_init’ ); 

Hope that helps.

While applying PSU on Grid Home in 12c, due to the patch conflict, you might have to rollback few patches before you could apply the PSU.

After rolling back the patches from grid home, when you try to run the opatch analyze command again, you might encounter following error:





 

Following step by step action plan is for single instance database stored on ASM in 12.1.0.2 on Linux (OEL 6 64 bit in this case.)

There might be a situation where executing some DDL in pluggable database may cause the following error:

ORA-65040: operation not allowed from within a pluggable database

This error could occur if a tablespace is being dropped from within PDB and this tablespace is a former default tablespace having some of the system objects. Even system objects cannot be moved with simple alter statements from within PDBs.

So in order to move these objects from within PDBs, you should be using procedure dbms_pdb.exec_as_oracle_script which is undocumented so far.

For example:

exec dbms_pdb.exec_as_oracle_script('alter table . move tablespace ');

From My Oracle Support, Doc ID 1943303.1 lists:

--   This procedure enables execution of certain restricted statements (most DDLs) on metadata-linked objects, from within a PDB.

AUSOUG's Foresight Online Spring 2015 Edition is the premier publication by Australian Oracle User Group.

Following are highlights of this edition:

• President's Message
• DBA Article: Automated Testing of Oracle BPM Suite 12c Processes with SOAP UI - Peter Kemp, State Revenue Office, Victoria
• DBA Article: Best Practices for Oracle on Pure Storage
• Apps Article: Performance Review Data Capture - Brad Sayer, More4Apps
• DBA / Dev Article: Database Developers – Feeling Left Out of Agile? - D Nowrood, Dell Software
• Apps Article:  Cost-effective alternative to Oracle Discoverer and BI Suite - Wilhelm Hamman, Excel4apps
• DBA Article: DBA101 - characterset corruption - Paul Guerin, HP
• Quick Tips 1: Five Reasons to Upgrade to APEX 5.0 - Scott Wesley, Sage Computing Services
• Quick Tips 2: Last Successful login time in 12c - Fahd Mirza Chughtai, The Pythian Group

Sharding for Oracle DBAs is still pretty much an alien or pretty new concept. In the realms of big data, this term is being used quite extensively though.

What is Sharding in simple words:

Sharding is partitioning. Horizontal partitioning to be exact.

Sharding means partitioning a table rows on basis of some criteria and storing that partitioned rows of table (i.e. a shard) on different database servers. These database servers are cheap low commodity servers.

The benefits include smaller data to manage, smaller backups, faster reads, and faster response time for the queries.

Just like existing partitioning option in the Oracle database, there are generally three kinds of sharding:

Range Sharding
List Sharding
Hash Sharding

The news out there on social media is that Oracle 12c next version is coming up with Sharding option. That is pretty exciting and let's see what they come up in this regard.

I manually created a container database (CDB) 12.1.0.2.0 in my Linux 6.4 based virutal machine. After creating it, I tried to create a pluggable database but got following error:




SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
cdbtest          OPEN

1 row selected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select * from cdb_pdbs;

    PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
      DBID    CON_UID GUID                             STATUS    CREATION_SCN
---------- ---------- -------------------------------- --------- ------------
       VSN LOGGING   FOR FOR     CON_ID
---------- --------- --- --- ----------
         2
PDB$SEED
3972600152 3972600152 201432B4FDA10CEBE0530900000AFE92 NORMAL             213
 202375680 LOGGING   NO  NO           2

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
cdbtest          OPEN

1 row selected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select * from cdb_pdbs;

    PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
      DBID    CON_UID GUID                             STATUS    CREATION_SCN
---------- ---------- -------------------------------- --------- ------------
       VSN LOGGING   FOR FOR     CON_ID
---------- --------- --- --- ----------
         2
PDB$SEED
3972600152 3972600152 201432B4FDA10CEBE0530900000AFE92 NORMAL             213
 202375680 LOGGING   NO  NO           2

So I tried with different options, but it always gave that error about PDB_DBA role. Searching the MOS, presented document.

This Document states that if we create a CDB manually we need to get from DBCA because we might have omit some options. CDB and PDB needs to be installed with all the options. I created CDB manually in first place because I had low disk space in my virtual machine, and I was intending to use all the options.

So if you get that error, the solution is to either create database through DBCA.

Falling in love with Kate Upton is easy but more easier is to be swept off your feet by information lifecycle management (ILM) in the Amazon Web Services (AWS). Simple, easily-configurable, fast, reliable, cost effective and proven are the words which describe it.

Pythian has been involved with ILM for a long time. With various flavors of databases and systems, Pythian has been overseeing creation, alteration, and flow of data for a long time until it becomes obsolete. That is why AWS's ILM resonates perfectly well with Pythian's expertise.

Amazon S3 is an object store for short term storage, whereas Amazon Glacier is their cloud archiving offering or storage for long term. Rules can be defined on the information to specify and automate its lifecycle.

Following screenshot shows the rules being configured on objects from S3 bucket to Glacier and then permanent deletion. 90 days after creation if an object, it will be moved to Glacier, and then after 1 year, it will be permanently deleted. Look at the graphical representation of lifecycle as how intuitive it is.

One cool small yet valuable feature in Oracle 12c is the display of 'Last Successful login time'. If authentication is from the OS level, then it isn't shown. A small demo is as follows:




[oracle@targettest ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 6 18:22:00 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@targettest ~]$ sqlplus 'hr/hr'

SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 6 18:22:07 2015

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

Last Successful login time: Sun Sep 06 2015 18:21:56 +10:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@targettest ~]$
[oracle@targettest ~]$ sqlplus 'hr/hr' as sysbackup

SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 6 18:22:12 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@targettest ~]$

A quick neat way to list down important and oft-needed information like names of databases, schemas, users, tables, projections etc. We can also use patterns with the '\d' to narrow down the results. Let's see it in action:



Connect with Vertica vsql:

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=>
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 |
mytest       | mytest  |
(8 rows)
vtest=> \dn mytest

List of schemas
Name  | Owner  | Comment
--------+--------+---------
mytest | mytest |
(1 row)
vtest=> \dn my*

List of schemas
Name  | Owner  | Comment
--------+--------+---------
mytest | mytest |
(1 row)
vtest=> \dn v

List of schemas
Name | Owner | Comment
------+-------+---------
(0 rows)
vtest=> \dn *v*

List of schemas
Name    |  Owner  | Comment
------------+---------+---------
v_internal | dbadmin |
v_catalog  | dbadmin |
v_monitor  | dbadmin |
(3 rows)

Likewise you can list down other information like :
vtest=> \dj

List of projections
Schema    |            Name             |  Owner  |       Node       | Comment
--------------+-----------------------------+---------+------------------+---------
mytest       | ptest                       | mytest  | v_vtest_node0002 |
mytest       | testtab_super               | mytest  |                  |

To list down views:
vtest=> \dv
No relations found.

If you connect with the mytest user and run:
vtest=> \dt

List of tables
Schema |  Name   | Kind  | Owner  | Comment
--------+---------+-------+--------+---------
mytest | testtab | table | mytest |
(1 row)

Following are more '\d' options from help:
Informational

\d [PATTERN]   describe tables (list tables if no argument is supplied)
PATTERN may include system schema name, e.g. v_catalog.*
\df [PATTERN]  list functions
\dj [PATTERN]  list projections
\dn [PATTERN]  list schemas
\dp [PATTERN]  list table access privileges
\ds [PATTERN]  list sequences
\dS [PATTERN]  list system tables. PATTERN may include system schema name
such as v_catalog, v_monitor, or v_internal.
Example: v_catalog.a*
\dt [PATTERN]  list tables
\dtv [PATTERN] list tables and views
\dT [PATTERN]  list data types
\du [PATTERN]  list users
\dv [PATTERN]  list views

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)

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

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.

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.

What is LPAR?

LPAR stands for Logical Partitioning and it's a feature of IBM's operating system AIX (Also available in Linux). By abstracting all the physical devices in a system, LPAR creates a virtualized computing environment.

In a server; the processor, memory, and storage are divided into multiple sets. Each set in a server consist of resources like processor, memory and storage. Each set is called as LPAR.

One server can have many LPARs operating at the same time. These LPARs communicate with each other as if they are on separate machines.

What is DLPAR?

DLPAR stands for Dynamic Logical Partitioning and with DLPAR the LPARs can be configured dynamically without restart. With DLPAR, memory, CPU and storage can be moved between LPARs on the fly.

What is HMC?

HMC stands for Hardware Management Console. The Hardware Management Console (HMC) is interface which is used to manage the LPARs. Its Java based and can be used to manage many systems.

If LPAR is in shared processor mode, without the following fix, LPAR may see excessive CPu usage: 


APARs for WAITPROC IDLE LOOPING CONSUMES CPU:
IV01111 AIX 6.1 TL05 if before SP08 (fixed in SP08)
IV06197 AIX 6.1 TL06 if before SP07 (fixed in SP07)
IV10172 AIX 6.1 TL07 if before SP02 (fixed in SP02)
IV09133 AIX 7.1 TL00 if before SP05 (fixed in SP05)
IV10484 AIX 7.1 TL01 if before SP02 (fixed in SP02)

This problem can effect POWER7 systems running any level of Ax720 firmware prior to Ax720_101. But it is recommended to update to the latest available firmware. If required, AIX and Firmware fixes can be obtained from IBM Support Fix Central:
http://www-933.ibm.com/support/fixcentral/main/System+p/AIX

-- If you want to display the system generated partitions as part of the CREATE TABLE DDL, then set the EXPORT parameter of the dbms_metadata to true.

-- The default behavior of "DBMS_METADATA.GET_DDL" is that it does not show Interval Partitions created by the system for interval partitioned tables and indexes.

-- In the case of Interval Partitioning, New Partitions are created automatically when corresponding row is inserted.  This newly created partition information will be displayed in "DBA_TAB_PARTITIONS" dictionary view. However when the DDL is queried using function "DBMS_METADATA.GET_DDL", then this information is not shown.


Demo:  (Following was tested on the Oracle 12c, and it should be valid for Oracle 11g too.)

-- Create table with interval partition.

CREATE TABLE mytabwithInterval
(mydate DATE,
 mynum NUMBER)
PARTITION BY RANGE (mydate) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION P_20150301  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));


-- Insert some data to generate interval partitions.

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-01-01', 'YYYY-MM-DD'),1);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-02-01', 'YYYY-MM-DD'),2);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-03-01', 'YYYY-MM-DD'),3);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-04-01', 'YYYY-MM-DD'),3);
COMMIT;

-- check partition information in dictionary table

col partition_name format a20
select partition_name from user_tab_partitions where table_name='MYTABWITHINTERVAL';


-- To see default behavior of dbms_metadata:


set long 100000
set pagesize 50
col DDL format a120

SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;


-- To see it with export option:


exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);
SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;


OUTPUT:


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set lines 181
SQL> set pages 100
SQL> CREATE TABLE mytabwithInterval
(mydate DATE,
 mynum NUMBER)
PARTITION BY RANGE (mydate) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION P_20150301  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
  2    3    4    5 
Table created.

SQL> INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-01-01', 'YYYY-MM-DD'),1);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-02-01', 'YYYY-MM-DD'),2);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-03-01', 'YYYY-MM-DD'),3);

INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-04-01', 'YYYY-MM-DD'),3);
COMMIT;
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL>

Commit complete.

SQL> col partition_name format a20
select partition_name from user_tab_partitions where table_name='MYTABWITHINTERVAL';SQL>

PARTITION_NAME
--------------------
P_20150301
SYS_P561
SYS_P562

SQL>


SQL>
SQL>
SQL> set long 100000
set pagesize 50
col DDL format a120

SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;
SQL> SQL> SQL> SQL>

DBMS_METADATA.GET_DDL('TABLE','MYTABWITHINTERVAL','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."MYTABWITHINTERVAL"
   (    "MYDATE" DATE,
    "MYNUM" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
  PARTITION BY RANGE ("MYDATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 (PARTITION "P_20150301"  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYY
YY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" )


SQL> SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);
SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;

PL/SQL procedure successfully completed.

SQL>

DBMS_METADATA.GET_DDL('TABLE','MYTABWITHINTERVAL','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."MYTABWITHINTERVAL"
   (    "MYDATE" DATE,
    "MYNUM" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
  PARTITION BY RANGE ("MYDATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) TRANSITION
 ("P_20150301")
 (PARTITION "P_20150301"  VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYY
YY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ,
 PARTITION "SYS_P561"  VALUES LESS THAN (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ,
 PARTITION "SYS_P562"  VALUES LESS THAN (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" )

Enjoy!!!

Starting from Oracle 12c, from within the SQL patching information can be obtained. A new package DBMS_QOPATCH offers some really nifty procedures to get the patch information. Some of that information is shared below:




To get patch information from the inventory:


SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) from dual;


Oracle Home      : /u01/app/oracle/product/12.1.0/db_1
Inventory      : /u01/app/oraInventory

The following is an equivalent of opatch lsinventory command at the OS level:

SQL> select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) from dual;


Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------
Oracle Home      : /u01/app/oracle/product/12.1.0/db_1
Inventory      : /u01/app/oraInventory
--------------------------------------------------------------------------------Installed Top-level Products (1):
Oracle Database 12c                       12.1.0.1.0
Installed Products ( 131)

Oracle Database 12c                        12.1.0.1.0
Sun JDK                             1.6.0.37.0
oracle.swd.oui.core.min                     12.1.0.1.0
Installer SDK Component                     12.1.0.1.0
Oracle One-Off Patch Installer                    12.1.0.1.0
Oracle Universal Installer                    12.1.0.1.0
Oracle USM Deconfiguration                    12.1.0.1.0
Oracle Configuration Manager Deconfiguration            10.3.1.0.0
Oracle RAC Deconfiguration                    12.1.0.1.0
Oracle DBCA Deconfiguration                    12.1.0.1.0
Oracle Database Plugin for Oracle Virtual Assembly Builder  12.1.0.1.0
Oracle Configuration Manager Client                10.3.2.1.0
Oracle Net Services                        12.1.0.1.0
Oracle Database 12c                        12.1.0.1.0
Oracle OLAP                            12.1.0.1.0
Oracle Spatial and Graph                    12.1.0.1.0
Oracle Partitioning                        12.1.0.1.0
Enterprise Edition Options                    12.1.0.1.0


Interim patches:

1) Set the environment to the cloud control agent. You can agent name from /etc/oratab file.

myserver: $ . oraenv
ORACLE_SID = [ORCL] ? agent12c

2) Check which targets are being monitored by the cloud control agent on this server:

myserver: $ emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
[MYSERVER, host]
[MYSERVER:3872, oracle_emd]
[ORCL, oracle_database]

3) Check if there is any existing blackout of agent on this server:

myserver: $ emctl status blackout
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
No Blackout registered.

4) Start the blackout:

myserver: $ emctl start blackout orcl_down_20150204 ORCL:oracle_database
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Blackout orcl_down_20150204 added successfully
EMD reload completed successfully

5) Again check the status of the blackout:

myserver: $ emctl status blackout
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Blackoutname = orcl_down_20150204
Targets = (ORCL:oracle_database,)
Time = ({2015-02-04|16:51:37,|} )
Expired = False

6) Stop the blackout:

myserver: $ emctl stop blackout orcl_down_20150204
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Blackout orcl_down_20150204 stopped successfully
EMD reload completed successfully

7) Again check the status of blackout:

myserver: $ emctl status blackout
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
No Blackout registered.

This Exadata vulnerability is related to glibc vulnerability. A heap-based buffer overflow was found in glibc's __nss_hostname_digits_dots() function, which is used by the gethostbyname() and gethostbyname2() glibc function calls.

A remote attacker able to make an application call either of these functions could use this flaw to execute arbitrary code with the permissions of the user running the application.

In order to check if your Exadata system suffers from this vulnerability, use:

[root@server ~]# ./ghostest-rhn-cf.sh
vulnerable

The solution and action plan for this vulnerability is available by My Oracle Support in the following document:

glibc vulnerability (CVE-2015-0235) patch availability for Oracle Exadata Database Machine (Doc ID 1965525.1)

Indexing depends upon the queries in the application.

There is no one-size-fits-all break-even point for indexed versus table scan access. If only a few rows are being accessed, the index will be preferred.

If almost all the rows are being accessed, the full table scan will be preferred. In between these two extremes, your “mileage” will vary.

A concatenated index is more useful if it also supports queries where not all columns are specified. For instance SURNAME, FIRSTNAME is more useful than FIRSTNAME, SURNAME because queries against SURNAME only are more likely to occur than queries against FIRSTNAME only.

Global indexes provide better performance for queries that must span all partitions.