How to find the page size for a Linux installation

In order to set one of the mandatory kernel parameters in a Red Hat flavored Linux distribution, you need to know the page size.
To find it:

[root@myserver]$ getconf PAGESIZE
4096

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

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 

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

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

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.