Monday, December 29, 2014

How to move LOB segments in a partitioned table to a new tablespace


During an attempt to move a partition that contained a LOB segment from one tablespace to another, I hit the infamous ORA-14511 "Cannot perform operation on a partitioned object" more than once. Neither is the Oracle documentation very clear in the matter.

Here is how I relocated LOB segments in a partitioned table from tablespace USERS to the correct tablespace DATA1.

The original DDL for the was as follows:
CREATE TABLE SCOTT.MYTABLE
(
  BQID                    VARCHAR2(36 BYTE)     NOT NULL,
  KLM_ID                  VARCHAR2(36 BYTE),
  PANUM                   NUMBER(18),
  IAA                     NUMBER(16),
  OPPGVTYP                VARCHAR2(50 BYTE),
  BSTAT                   VARCHAR2(60 BYTE),
  LEVREF                  VARCHAR2(50 BYTE)     NOT NULL,
  KSYSTEM                 VARCHAR2(255 BYTE),
  ANT_OPPG                NUMBER(18),
  TOTALER                 CLOB,
  OPPR                    TIMESTAMP(6)          NOT NULL,
  OPP_KI                  VARCHAR2(30 BYTE)     NOT NULL,
  SIST_ENDRET             TIMESTAMP(6)          NOT NULL,
  XML_INNHOLD             CLOB,
  OPPSUMMERING            CLOB,
  ERSTATTERREFERANSE      VARCHAR2(150 CHAR),
  LEVERANSETIDSPUNKT      TIMESTAMP(6),
  LEVERANSETYPE           VARCHAR2(150 BYTE),
  TMP_MD5_OPPSUMMERING    VARCHAR2(32 BYTE)
)
LOB (TOTALER) STORE AS (
  TABLESPACE  DATA1
 )
LOB (XML_INNHOLD) STORE AS (
  TABLESPACE  DATA1
 )
LOB (OPPSUMMERING) STORE AS (
  TABLESPACE  DATA1
 )
TABLESPACE DATA1
PARTITION BY LIST (TASKTYPE)
(  
  PARTITION P_MESSAGE VALUES ('EZAP')
    TABLESPACE DATA1
    LOB (TOTALER) STORE AS (
      TABLESPACE  USERS
    )
    LOB (XML_INNHOLD) STORE AS (
      TABLESPACE  USERS
    )
    LOB (OPPSUMMERING) STORE AS (
      TABLESPACE  USERS
    )
    ,  
  PARTITION P_DEFAULT VALUES (DEFAULT)
    TABLESPACE DATA1
    LOB (TOTALER) STORE AS (
      TABLESPACE  USERS
    )
    LOB (XML_INNHOLD) STORE AS (
      TABLESPACE  USERS
    )
    LOB (OPPSUMMERING) STORE AS (
      TABLESPACE  USERS
    )
)
;

A query against DBA_LOB_PARTITIONS shows that they are indeed placed in tablespace USERS:
SELECT TABLE_OWNER,TABLE_NAME,COLUMN_NAME,LOB_NAME,PARTITION_NAME,LOB_PARTITION_NAME,SECUREFILE,TABLESPACE_NAME
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'SCOTT' 
AND TABLESPACE_NAME = 'USERS'
AND PARTITION_NAME = 'P_AMELDING';

TABLE_OWNER TABLE_NAME COLUMN_NAME LOB_NAME PARTITION_NAME LOB_PARTITION_NAME SECUREFILE TABLESPACE_NAME
SCOTT MYTABLE TOTALER SYS_LOB0000093789C00010$$ P_AMELDING SYS_LOB_P701 NO USERS
SCOTT MYTABLE XML_INNHOLD SYS_LOB0000093789C00020$$ P_AMELDING SYS_LOB_P703 NO USERS
SCOTT MYTABLE OPPSUMMERING SYS_LOB0000093789C00021$$ P_AMELDING SYS_LOB_P705 NO USERS

The correct syntax to move these LOB segments is:
ALTER TABLE SCOTT.MYTABLE
MOVE PARTITION P_AMELDING
LOB (TOTALER) STORE AS BASICFILE (TABLESPACE DATA1);

ALTER TABLE SCOTT.MYTABLE
MOVE PARTITION P_AMELDING
LOB (OPPSUMMERING) STORE AS BASICFILE (TABLESPACE DATA1);

ALTER TABLE SCOTT.MYTABLE
MOVE PARTITION P_AMELDING
LOB (XML_INNHOLD) STORE AS BASICFILE (TABLESPACE DATA1);

To generate DDL statements:
SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as basicfile (tablespace data1);'
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'SCOTT' 
AND TABLESPACE_NAME = 'USERS'
AND SECUREFILE='NO';

If the table is also sub-partitioned, you may want to check this post.

Wednesday, December 17, 2014

Why are statements executed by SYS not found in the DB audit trail?


Even if Oracles default auditing is set up, it surprised me that audit information did not appear in the audit trail, for statements that should be covered by default auditing.

Below are my findings.


First, let's check which audit privileges that are set already:

-- DBA_PRIV_AUDIT_OPTS: describes current system privileges being audited across the system and by user.
SELECT * 
FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
    GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
    ALTER DATABASE BY ACCESS BY ACCESS
    ALTER USER BY ACCESS BY ACCESS
    CREATE USER BY ACCESS BY ACCESS
    CREATE SESSION BY ACCESS BY ACCESS
    ALTER SYSTEM BY ACCESS BY ACCESS

Let's audit CREATE USER by user SYSTEM:

SQL> connect / as sysdba
SQL> AUDIT CREATE USER BY SYSTEM;

Check again that the audit trail that is set
SELECT * 
FROM DBA_PRIV_AUDIT_OPTS;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
    GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
    ALTER DATABASE BY ACCESS BY ACCESS
    ALTER USER BY ACCESS BY ACCESS
SYSTEM   CREATE USER BY ACCESS BY ACCESS
    CREATE SESSION BY ACCESS BY ACCESS
    ALTER SYSTEM BY ACCESS BY ACCESS


Log in as user SYS, and create a user:
SQL> connect sys/****@proddb01 as sysdba
SQL> connected.
SQL> CREATE USER VKAASA IDENTIFIED BY password 
SQL> DEFAULT TABLESPACE USERS
SQL> TEMPORARY TABLESPACE TEMP;

User created.

Log in as user SYSTEM, and create a user:
SQL> connect system/****
SQL> CREATE USER SCOTT IDENTIFIED BY password 
SQL> DEFAULT TABLESPACE USERS
SQL> TEMPORARY TABLESPACE TEMP;

User created.
To find the audit entry, check the DBA_COMMON_AUDIT_TRAIL. This view displays all standard and fine-grained audit trail entries, mandatory and SYS audit records written in XML format.

SELECT AUDIT_TYPE,EXTENDED_TIMESTAMP, DB_USER, OS_USER,USERHOST,OBJECT_NAME,STATEMENT_TYPE,SCN,SQL_TEXT,PRIV_USED
FROM DBA_COMMON_AUDIT_TRAIL 
WHERE PRIV_USED = 'CREATE USER'
AND TO_CHAR(extended_timestamp AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD HH24:MI:SS TZD') > TO_CHAR((SYSDATE-1),'YYYY-MM-DD HH24:MI:SS');

AUDIT_TYPE EXTENDED_TIMESTAMP DB_USER OS_USER USERHOST OBJECT_NAME STATEMENT_TYPE SCN SQL_TEXT PRIV_USED
Standard Audit 17.12.2014 13:05:06,515954 +01:00 SYSTEM oracle myserver scott CREATE USER
153820329960
create user scott identified by * default tablespace users temporary tablespace temp CREATE USER

The same audit entry can be found in DBA_AUDIT_TRAIL, which displays all standard audit trail entries.

SELECT OS_USERNAME,USERNAME,USERHOST,TIMESTAMP,OBJ_NAME,ACTION_NAME,RETURNCODE,PRIV_USED,SCN,SQL_TEXT
FROM DBA_AUDIT_TRAIL
WHERE PRIV_USED = 'CREATE USER'
AND TO_CHAR(extended_timestamp AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD HH24:MI:SS TZD') > TO_CHAR((SYSDATE-1),'YYYY-MM-DD HH24:MI:SS');

OS_USERNAME USERNAME USERHOST TIMESTAMP OBJ_NAME ACTION_NAME RETURNCODE PRIV_USED SCN SQL_TEXT
oracle SYSTEM myserver 17.12.2014 13:05:06 scott CREATE USER
0
CREATE USER
153820329960
create user scott identified by * default tablespace users temporary tablespace temp


So what happened to the other user, vkaasa, created by SYS? Shouldn't that leave an entry in the audit trail as well?

- You cannot audit actions by sys the same way. Trying to enable audit for user SYS will throw ORA-00983:
SQL> audit create user by sys;
audit create user by sys
*
ERROR at line 1:
ORA-00983: cannot audit or noaudit SYS user actions

Any user creation by sys, which has the sysdba privilege will not be audited. Donald Burleson at Burleson Consulting puts it this way:

"When a user with SYSDBA privileges connects to the database, the action is expected to be for administrative reasons only, such as shutdown, startup, add a service to the listener, etc. Generally, we would not expect these actions to be audited and therefore not picked up by the auditing mechanism.

However, the user who is connected as SYSDBA is a regular user, just like any other user, but with all powerful do anything privileges. This user could potentially alter the data in a table in any schema. Since the action is not audited, the trace of this data manipulation is hidden. In case of an investigation later, the audit trails will have no record of such manipulations ? a huge security hole"

However, any CREATE USER action executed by sys is recorded in the audit trail on disk:
SQL> show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/oracle/admin/proddb01/adump

cd /u01/oracle/admin/proddb01/adump
ls -latr 

-rw-r-----    1 oracle   dba            2754 Dec 17 13:03 proddb01_ora_56099020_20141217130109908038143795.aud
-rw-r-----    1 oracle   dba            1429 Dec 17 13:24 proddb01_ora_56426540_20141217132347629420143795.aud
-rw-r-----    1 oracle   dba           66871 Dec 17 13:27 proddb01_ora_58458244_20141217075116217921143795.aud
drwxrwxr-x    2 oracle   dba           73728 Dec 17 13:33 .
-rw-r-----    1 oracle   dba            2612 Dec 17 13:33 proddb01_ora_57475318_20141217133329412931143795.aud

Open the file proddb01_ora_56426540_20141217132347629420143795.aud:

Wed Dec 17 13:24:37 2014 +01:00
LENGTH : '243'
ACTION :[85] 'create user vkaasa identified by * default tablespace users temporary tablespace temp'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'myuser'
CLIENT TERMINAL:[7] 'myterminal'
STATUS:[1] '0'
DBID:[10] '3707088731'

How can I check which tables that are being audited in my database?


The experiences in this article is applicable to all Oracle versions using classic auditing setup.


To check which tables are currently being audited, use the views DBA_OBJ_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS.

DBA_PRIV_AUDIT_OPTS describes current system privileges being audited across the system and by user. USERNAME can hold three different values: 

  1. The actual user name if by user auditing
  2. ANY CLIENT if access by a proxy on behalf of a client is being audited; 
  3. NULL for system-wide auditing

DBA_OBJ_AUDIT_OPTS describes auditing options on all objects. 

If no objects or system privileges is being audited, the queries will return zero rows as in the examples below:
SELECT *
FROM DBA_PRIV_AUDIT_OPTS;

no rows selected

SELECT *
FROM DBA_OBJ_AUDIT_OPTS;

no rows selected

Let's start auditing an object:

AUDIT ALL ON SCOTT.BASELINE_STG_TBL;

Audit succeeded.

You can also be more specific and only update certain actions:
AUDIT UPDATE,DELETE,INSERT ON SCOTT.BASELINE_STG_TBL;

Audit succeeded.

Verify that audit is enabled:
SELECT *
FROM DBA_OBJ_AUDIT_OPTS
WHERE OWNER = 'SCOTT'
AND object_name='BASELINE_STG_TBL';

OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
SCOTT BASELINE_STG_TBL TABLE S/S S/S S/S S/S S/S S/S S/S S/S S/S S/S S/S -/- -/- -/- -/- -/- S/S


The notation 'S/S' in the different columns of the DBA_OBJ_AUDIT_OPTS is "frequency" where frequency is either "By Session" (S) or "By Access" (A).

The placement of the hyphen, the "S" or the "A" indicates under what circumstances the auditing should be performed, as outlined below:
-/-: no auditing
S/-: auditing whenever successful
-/S: auditing whenever not successful

As can be seen by the result above, by default, auditing is done by SESSION, both for successful and unsuccessful attempts.
Let's turn off auditing for SELECTs
NOAUDIT SELECT ON SCOTT.BASELINE_STG_TBL;

Noaudit succeeded.

Verify that queries on the table are now not being audited:
SELECT *
 FROM DBA_OBJ_AUDIT_OPTS
 WHERE OWNER = 'SCOTT'
 AND object_name='BASELINE_STG_TBL';
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
SCOTT BASELINE_STG_TBL TABLE S/S S/S S/S S/S S/S S/S S/S S/S S/S -/- S/S -/- -/- -/- -/- -/- S/S

Notice that the SEL column now shows a hyphen (-) since the privilege is not audited anymore.

The following statement
AUDIT SELECT ON SCOTT.BASELINE_STG_TBL BY ACCESS WHENEVER NOT SUCCESSFUL;
Would result in
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
SCOTT BASELINE_STG_TBL TABLE S/S S/S S/S S/S S/S S/S S/S S/S S/S -/A S/S -/- -/- -/- -/- -/- S/S

and
AUDIT SELECT ON SCOTT.COUNTRY_TABLE BY ACCESS WHENEVER SUCCESSFUL;
would result in
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
SCOTT BASELINE_STG_TBL TABLE S/S S/S S/S S/S S/S S/S S/S S/S S/S A/- S/S -/- -/- -/- -/- -/- S/S


Keep in mind that mandatory auditing is always turned on. 
Oracle 12.2 Documentation for DBA_OBJ_AUDIT_OPTS can be found here

Monday, December 15, 2014

How to solve "ORA-06502: PL/SQL: numeric or value error" when cloning a database

While attempting to clone an Oracle 11gR2 production database, Oracle returned "ORA-06502: PL/SQL: numeric or value error".

The clone proceeded nicely up until RMAN attempted to set the db_unique_name. After this point, the clone failed, see error message output below:
Oracle instance started

Total System Global Area    8551575552 bytes

Fixed Size                     2263488 bytes
Variable Size               1107297856 bytes
Database Buffers            7415529472 bytes
Redo Buffers                  26484736 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
   ''testdb01'' comment=
   ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
}

executing Memory Script

sql statement: alter system set  db_name =  ''testdb01'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/11/2014 13:20:45
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error
The trace file also adds the following line to the previous lines:
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5567

The culprit seems to be that my production database and the auxiliary database had at some point in the past been part of a Data Guard setup.
By looking at the registered databases in the recovery catalog, this could indeed be verified:

rman target / catalog uid/pwd@rmancat 

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 15 11:33:55 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: proddb01 (DBID=3046916437)
connected to recovery catalog database

RMAN> set echo on;
2> list db_unique_name of database;
4> exit
echo set on
List of Databases

DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
133048143 proddb01     3046916437       PRIMARY          proddb01
133048143 proddb01     3046916437       STANDBY          testdb01

Solution:
In this case, this old association to an obsolete standby database could be removed altogether.
I therefore unregistered the unique database from the recovery catalog:
RMAN> unregister db_unique_name 'testdb01';

database db_unique_name is "testdb01", db_name is "proddb01" and DBID is 3046916437

Want to unregister the database with target db_unique_name (enter YES or NO)? yes
database with db_unique_name testdb01 unregistered from the recovery catalog

After this was done, I restarted the clone, and the error was no longer thrown and the script continued as expected.

If you are in a slightly different position, and have several primary databases with the same unique name, you need a different approach. Consider the following example:
RMAN> list db_unique_name of database;

List of Databases

DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
376254740 PDB01    3895530761       PRIMARY          PDB01
380374509 PDB01    3902464629       PRIMARY          PDB01
383100641 PDB01    3906391689       PRIMARY          PDB01

RMAN> SET DBID 3895530761;

executing command: SET DBID
database name is "PDB01" and DBID is 3895530761

RMAN> UNREGISTER DB_UNIQUE_NAME PDB01;

database db_unique_name is "PDB01", db_name is "PDB01" and DBID is 3895530761

Want to unregister the database with target db_unique_name (enter YES or NO)? yes

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20244: can not change currently connected database db_unique_name
RMAN> exit

Solution is to still explicitly set DBID, but then execute "unregister database" instead, like this:

RMAN> unregister database;

database name is "PDB01" and DBID is 3895530761

Do you really want to unregister the database (enter YES or NO)? yes

database unregistered from the recovery catalog

RMAN> SET DBID 3902464629;
executing command: SET DBID
database name is "PDB01" and DBID is 3902464629 

RMAN> unregister database;
database name is "PDB01" and DBID is 3902464629

Do you really want to unregister the database (enter YES or NO)? YES

RMAN> list db_unique_name of database;

List of Databases

DB Key  DB Name  DB ID            Database Role    Db_unique_name

------- ------- ----------------- ---------------  ------------------
383100641 PDB01    3906391689       PRIMARY          PDB01

We are left with only one unique db name, which is what we want.

Thursday, December 4, 2014

How to work around ORA-00845: MEMORY_TARGET not supported on this system during startup

On Linux platforms, you need to adjust the size of the shared memory file system (/dev/shm) to match the size of your memory_target or memory_max_target. If not you will see the following error:

SQL> startup mount

ORA-00845: MEMORY_TARGET not supported on this system

To adjust the size, open the file /etc/fstab in an editor:

vi /etc/fstab

At the line where you see the tmpfs file system specified, add the size after the keyword "defaults". In the example below, I would like to use a memory_target of 10g, so I add "10g". Do not use a capital letter G, but simply a "g":
tmpfs                   /dev/shm                tmpfs   defaults,size=10g        0 0

Then, remount the file system:
mount –o remount /dev/shm



Monday, December 1, 2014

How to find and change the default temporary tablespace and default tablespace for a database instance


set lines 200
col description format a50
col property_value format a30
col property_name format a30
SELECT * 
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE '%TABLESPACE%';

To change the default temporary tablespace, use
alter database default temporary tablespace tmp;

To change the default tablespace, use
alter database default tablespace user_data;

How to setup Oracle Net Encryption for non-JDBC or OCI (thick) JDBC client

Since June 2013, SQL Net Encryption is a part of the normal Oracle EE license, as opposed to the Advanced Security Option.

Add the following to your server’s sqlnet.ora file:
sqlnet.encryption_server=required
sqlnet.encryption_types_server=AES128

Add the following to your client’s sqlnet.ora file:

SQLNET.ENCRYPTION_CLIENT = accepted

Note:
A list of encryption algorithms can be stated on each side, and the first one common to both client and server in the list will be chosen.

Verify that SQL Net Encryption works

In client’s sqlnet.ora:
ADR_BASE = C:\app\k90387
TRACE_LEVEL_CLIENT = ADMIN

This above will create an ADR directory structure on the client, in my case trace files are written to
 c:\app\vegard\oradiag_vegard\diag\clients\user_vegard\host_3450395457_76\trace

Create a session against the database:

SQL> connect vegard/vegard@oras
Connected.
SQL> exit

Open your windows explorer and look in the folder
c:\app\vegard\oradiag_vegard\diag\clients\user_vegard\host_3450395457_76\trace
You'll now see two files: one .trc file and one .trm file
Open the .trc file in a text editor, and search for the following:
2014-11-28 14:11:13.374358 :  -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
2014-11-28 14:11:13.374375 :   SQLNET.ENCRYPTION_CLIENT = accepted
2014-11-28 14:11:13.374392 :   ADR_BASE = C:\app\vegard
.
.
.
2014-11-28 13:48:28.033764 : na_tns:  authentication is not active
2014-11-28 13:48:28.033781 : na_tns:  encryption is active, using AES128
2014-11-28 13:48:28.033797 : na_tns:  crypto-checksumming is not active
Note that neither authentication nor crypto-checksumming is active, but encryption is. As expected.

Sources:

  • Orafaq
  • Oracle Documentation