Monday, November 21, 2016

How to install and use the character set scanner

Although deprecated since the release of DMU (Database Migration utility for Unicode), there are still cases where you need to run csscan.

One such incident happened to me when migrating a database used for Oracles E-Business Suite.

Some errors were found in DMU, but when clicking on the number indicating the number of rows with errors, the Cleansing Editor showed up empty. To get the rowids of the offending rows, I had to resort to the character scanner.

If not installed do as follows:

1. edit the script $ORACLE_HOME/rdbms/admin/csminst.sql so that it has a valid password, and that its object will be saved in a separate tablespace
create user csmig identified by ***** account lock
/
alter user csmig default tablespace tools quota unlimited on tools
/
2. execute the script as sysdba
sqlplus / as sysdba @csminst.sql

That's it. The csscanner tool is now installed.

A simple example of usage would be when I scanned one - 1 - table.
First, I created a parameter file
userid='sys/**** as sysdba'
table=scott.emp
fromchar=WE8ISO8859P1
tochar=al32utf8
array=4096000
process=4
feedback=1000

Execute it as follows:
csscan parfile=myscan.par

This process creates three files for you:
1. scan.out - Screen log file
2. scan.err - individual exception report. This is where you would typically find your rows that contains errors
3. scan.txt - contains the Database Scan Summary Report.

Here's how my scan.err looked like after I scanned the table mentioned above:
Database Scan Individual Exception Report


[Database Scan Parameters]

Parameter                      Value
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1
Instance Name                  proddb01
Database Version               11.2.0.4.0
Scan type                      Selective tables
Scan CHAR data?                YES
Database character set         WE8ISO8859P1
FROMCHAR                       WE8ISO8859P1
TOCHAR                         al32utf8
Scan NCHAR data?               NO
Array fetch buffer size        4096000
Number of processes            4
Capture convertible data?      NO
------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]


[Application data individual exceptions]

User  : SCOTT
Table : EMP
Column: ATTRIBUTE15
Type  : VARCHAR2(150)
Number of Exceptions         : 2
Max Post Conversion Data Size: 153

ROWID              Exception Type      Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAS/WmAHwAACI5ZAAC exceed column size   153 
AAS/WmAHwAACI5ZAAD exceed column size   153 
------------------ ------------------ ----- ------------------------------
As expected, the offending rows contained Scandinavian characters, which will use 2 bytes after conversion to a Unicode database. Therefore the text will no longer fit, and actions need to be taken by the data owner before the migration.

For more information, look up the official documentation here

Thursday, November 17, 2016

How to create a blackout in Cloud Control using the command-line interface

Source your environment to point to the correct ORACLE_HOME. Put the following in a file called .agentenv
export ORACLE_BASE=/software/oracle
export ORACLE_HOME=/software/oracle/product/agent13c/agent_13.2
export PATH=$ORACLE_HOME/bin:$PATH
echo "\n\nOH is : $ORACLE_HOME"

If you need to create the blackout as a part of a script which needs root privileges, execute the following:
su - oracle -c ". .agent1?c;emctl start blackout  -nodeLevel -d 15;emctl stop agent"

For usage by the oracle software installation owner, skip the first part:
. .agent1?c;emctl start blackout  -nodeLevel -d 15;emctl stop agent

To stop the blackout use
. /home/oracle/.agent1?c;emctl stop blackout 

Monday, November 14, 2016

A short history of the Automated Maintenance Tasks in Oracle

Predefined Automatic System Tasks were introduced in Oracle 10g, and consisted of two jobs:

1. Automated statistics collection
2. The Automated segment space advisor

Two scheduler jobs were set up by default to run these automated tasks: The GATHER_STATS_JOB and the AUTO_SPACE_ADVISOR_JOB.

You could enable or disable the jobs using the procedures enable or disable in the dbms_scheduler package:
EXECUTE DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
EXECUTE DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

In Oracle 11g, Oracle redesigned the the Automatic System Tasks and introduced the concept of Automated Maintenance Tasks.
At the same time, a third automated task was introduced: The Automated SQL Tuning advisor.

Oracle removed the GATHER_STATS_JOB and AUTO_SPACE_ADVISOR_JOB altogether and instead introduced a new package called DBMS_AUTO_TASK_ADMIN.

To enable or disable an Automatic tasks using DBMS_AUTO_TASK_ADMIN, see this short post.

To check the status of your automated maintenance tasks, see this post.

How to check the status of the Automated Maintenance Tasks in Oracle 11g

To see the currently enabled jobs in your database, use the dictionary view DBA_AUTOTASK_OPERATION
which displays all automated maintenance task operations for each client:
select client_name,operation_name,status  
from DBA_AUTOTASK_OPERATION;
Example from one of my 11g databases:
CLIENT_NAME OPERATION_NAME STATUS
auto optimizer stats collection auto optimizer stats job DISABLED
auto space advisor auto space advisor job ENABLED
sql tuning advisor automatic sql tuning task DISABLED

Friday, November 4, 2016

Quick guide to set up Transparent Data Encryption for a tablespace

If not already done, create a folder for the wallet:
# mkdir -p  /u01/oracle/admin/proddb01/wallet

Step 1: configure the software keystore location in the sqlnet.ora file

Edit your $TNS_ADMIN/sqlnet.ora:

SQLNET.WALLET_OVERRIDE = TRUE
SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = FALSE
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/oracle/admin/proddb01/wallet)
    )
  )
SSL_CIPHER_SUITES = (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_DES_CBC_SHA)


Step 2: create the software keystore

There are three different types of software keystores.

You can create a) password-based software keystores, b) auto-login software keystores, and c) local auto-login software keystores.
In this guide, I am setting up password-based software keystore.
Password-based software keystores are protected by using a password that you create.

You must open this type of keystore before the keys can be retrieved or used.


Verify wallet location:
SYS@proddb01 SQL> select wrl_parameter, status,wallet_type from  v$encryption_Wallet;

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        NOT_AVAILABLE                  UNKNOWN

Note that wallet type = UNKNOWN , status=NOT_AVALABLE
Run "ADMINISTER KEY MANAGEMENT" to create the keystore:

SYS@proddb01 SQL> administer key management create keystore '/u01/oracle/admin/proddb01/wallet' identified by "secretpassword";

Keystore altered.


Check the view v$encryption_Wallet now, and you can see that it exists, but the wallet type is still UNKNOWN:

SYS@proddb01 SQL> select wrl_parameter, status,wallet_type from  v$encryption_Wallet;

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        CLOSED                         UNKNOWN


Step 3: Open the Software Keystore

Depending on the type of keystore you create, you must manually open the keystore before you can use it.
You must manually open a password-based software keystore before any TDE master encryption keys can be created or accessed in the keystore.

After you open a keystore, it remains open until you manually close it.
Each time you restart a database instance, you must manually open the password keystore to reenable encryption and decryption operations.

Open the keystore:
SYS@proddb01 SQL> administer key management set keystore open identified by "secretpassword";
 
Keystore altered.

Check the view v$encryption_wallet again:
SYS@proddb01 SQL> select wrl_parameter, status,wallet_type from  v$encryption_Wallet;
 

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        OPEN_NO_MASTER_KEY             PASSWORD


Note that if the keystore is open but you have not created a TDE master encryption key yet, the STATUS column of the V$ENCRYPTION_WALLET view reminds you with a status of OPEN_NO_MASTER_KEY.

Step 4: Set the Software TDE Master Encryption Key


The TDE master encryption key is stored in the keystore.
This key protects the TDE table keys and tablespace encryption keys.
By default, the TDE master encryption key is a key that Transparent Data Encryption (TDE) generates.
Password-based software keystores must be open before you can set the TDE master encryption key.

Create a master key:
SYS@proddb01 SQL> administer key management set key identified by "****" with backup using 'initial_key_backup';

Keystore altered.

Check the view v$encryption_wallet again:
SYS@proddb01 SQL>  select wrl_parameter, status,wallet_type from  v$encryption_Wallet;

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        OPEN                           PASSWORD

Step 5: Encrypt Your Data

After you complete the software keystore configuration, you can begin to encrypt data.
You can encrypt data in individual table columns or in entire tablespaces, like I do below:

create bigfile tablespace SECRET_DATA_TBS datafile '/u02/oradata/proddb01/secret_data_01.dbf'  size 256M autoextend on next 128M maxsize unlimited
encryption using 'AES128'
default storage(encrypt);

Check status of the tablespaces:
SYS@proddb01 SQL> SELECT t.name, e.encryptionalg algorithm
FROM  v$tablespace t, v$encrypted_tablespaces e
WHERE t.ts# = e.ts#;
 
NAME                           ALGORIT
------------------------------ -------
SECRET_DATA_TBS                AES128

When the database is restarted, the wallet will be in status CLOSED.

To access your data, the wallet must be opened first:

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "*****";

You may want to add a startup trigger that opens the wallet every time the database starts. See this post for how I solved this.

Sources:

1. configure the software keystore location in the sqlnet.ora file
2. log in to the database instance to create and open the keystore
3. Open the Software keystore
4. set the TDE master encryption key
5. begin to encrypt data


Wednesday, October 5, 2016

Find column semantics



When migrating from single-byte code to multi-byte code character sets, one way to move your data without data loss is to change the semantics used. In other words, VARCHAR2 and CHAR type columns can be redefined to use character semantics instead of the default byte semantics.

A schema-by-schema overview of the tables and column involved can be extracted by using the query below:

select owner "owner",
                CASE 
                    WHEN char_used = 'B' then 'BYTE'
                    WHEN char_used = 'C' then 'CHAR'
                END "semantics"
,count(*) "antall"
from dba_tab_columns
where owner in ('SCOTT','RYAN')
and data_Type in ('CHAR','VARCHAR2')
group by OWNER, ROLLUP(char_used);

owner semantics antall
SCOTT BYTE
62
SCOTT CHAR
610
SCOTT  
672
RYAN BYTE
486
RYAN  
486

In this example, the SCOTT schema contains columns of both semantics types.
The schema RYAN contains only the default, BYTE.

Monday, October 3, 2016

Use the dbms_flashback to set your session back in time

A convenient way to set your session back in time, is to execute the dbms_flashback.enable_at_time procedure.
This way you can work with your data as they were at a previous point-in-time, given that your UNDO settings support it.

Oracle states

"The DBMS_FLASHBACK package acts as a time machine: you can turn back the clock, perform normal queries as if you were at that earlier time, and then return to the present. Because you can use the DBMS_FLASHBACK package to perform queries on past data without special clauses such as AS OF or VERSIONS BETWEEN, you can reuse existing PL/SQL code to query the database at earlier times."



A simple test of the procedure follows.

First, the user would need execute privileges on the package in which enable_at_time belongs, the dbms_flashback package:
connect sys/passwd as sysdba
grant execute on dbms_flashback to scott;
SQL>connect scott/passwd 
SQL>select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

SQL>

Make a change:

SQL> insert into album (album_id, album_title,artist_id) values(200,'Whiplash Smile', 10);

1 row created.

SQL> commit;

Commit complete.

SQL> select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
       200 Whiplash Smile                                             10
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

To see the content of the table album as it was 5 minutes ago:
SQL> execute dbms_flashback.enable_at_time(sysdate-5/1440);

PL/SQL procedure successfully completed.

SQL> select album_id,album_title,artist_id from album where artist_id=10;

  ALBUM_ID ALBUM_TITLE                                         ARTIST_ID
---------- -------------------------------------------------- ----------
        10 Joshua tree                                                10
        11 Achtung Baby                                               10
        12 Zooropa                                                    10

Disable the flashback setting:

SQL> execute dbms_flashback.disable();

PL/SQL procedure successfully completed.

If you want to use flashback queries against other schemas' tables, you need object privileges to do so:
grant flashback on scott.album to jim;
Alternatively, you can grant jim the system privilege flashback any table:
connect / as sysdba
grant flashback any table to jim;
Sources: Oracle Documentation This document from Oracle Support