Automatic startup and shutdown script for Oracle databases on the Linux Platform

On most installations of the Oracle database, it is desirable to add a script for automatic startup and shutdown.
This is how to do it.
The instructions holds true for both 11g and 12c.

1. Edit the /etc/oratab file, so that the entry for your database has a Y at the end:

testdb01:/u01/oracle/product/db/112:Y

2. cd /etc/init.d

3. create a file called dbora, and add the following lines to it. Note that you should enter the name of your ORACLE_HOME path and the oracle software installation owner as values for ORA_HOME and ORA_OWNER, respectively:

#! /bin/sh 
# description: Oracle auto start-stop script.
#
# Set ORACLE_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORACLE_HOME.

ORA_HOME=/u01/oracle/product/db/112
ORA_OWNER=oracle

case "$1" in
'start')
    # Start the Oracle databases:
    # The following command assumes that the oracle login
    # will not prompt the user for any values
    # Remove "&" if you don't want startup as a background process.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
    touch /var/lock/subsys/dbora
    ;;

'stop')
    # Stop the Oracle databases:
    # The following command assumes that the oracle login
    # will not prompt the user for any values
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME" &
    rm -f /var/lock/subsys/dbora
    ;;
esac

4. Set permissions:

# chgrp dba dbora
# chmod 750 dbora

5. Create symlinks to the different run level script directories:

ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

Verify the symlinks. They should all point back to /etc/init.d/dbora

ls -la /etc/rc.d/rc0.d/K01dbora
ls -la /etc/rc.d/rc3.d/S99dbora
ls -la /etc/rc.d/rc5.d/S99dbora

What about the listener?
According to Oracle, as long as it has the default name of "LISTENER", you don't need to add it to your startup/shutdown script. If you use a different name, you must add the following line to the /etc/init.d/dbora script:

$ORACLE_HOME/bin/lsnrctl {start|stop} listener_name

The original instructions from Oracle can be found here

Possible solution to unix error "X11 connection rejected because of wrong authentication"

I was trying to start an X window Application on my server as user Oracle, but received the error

oracle@myserver:[testdb01]# xclock
X11 connection rejected because of wrong authentication.
Error: Can't open display: localhost:10.0

The DISPLAY variable was set to localhost:10.0.

The solution was to logon as root and execute

root@myserver~]# xauth list $DISPLAY

The result from this was

myserver.mydomain.com/unix:10  MIT-MAGIC-COOKIE-1  83f9f76d03106db6fc0880edfb005607

Then change user back to oracle and add the cookie to oracle's authorization file:

[root@myserver ~]# su - oracle
oracle@myserver:[testdb01]# xauth add :10 MIT-MAGIC-COOKIE-1 83f9f76d03106db6fc0880edfb005607

If you get the error:

xauth:  file /home/oracle/.Xauthority does not exist

create the file first:

touch /home/oracle/.Xauthority

Then retry the "xauth add" command above.
Verify with:

oracle@myserver:[testdb01]# xauth list $DISPLAY
myserver.mydomain.com/unix:10  MIT-MAGIC-COOKIE-1  83f9f76d03106db6fc0880edfb005607
oracle@myserver:[testdb01]# echo $DISPLAY
localhost:10.0

I could now execute X Applications:

oracle@myserver:[testdb01]# xclock

Possible solution to ORA-01450: maximum key length (3800) exceeded when rebuilding an index

As a part of disabling TDE in a test database, I was moving indexes out of the TDE encrypted tablespace to another, similarly created tablespace, but one without encryption.

When trying to execute

ALTER INDEX SCOTT.MYTABLE_U01 REBUILD ONLINE TABLESPACE DATA32K_NOTDE

one of the tables returned an error during the online rebuild:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3800) exceeded

This error is normally worked around by one of the following actions:

1. Rebuild the database with a larger blocksize
2. Add a new tablespace with a larger blocksize (my preferred solution)
3. Make the index smaller, meaning drop and recreate the index and omit one or more of the previously indexed columns


This is documented in Doc ID 747107.1 "ORA-01450 Error on Create Index" at Oracle Support.

For me though, it worked by simply changing

REBUILD ONLINE 

to

REBUILD

and the index rebuild executed without problems.
The Oracle Documentation for 12cR1 has a few restrictions regarding the use of online rebuilds, but none of them seems relevant to the error I observed.

For more information about the ALTER INDEX statement, see the official documentation from Oracle

How to check if Database Vault is enabled or disabled

To check whether or not the Database Vault option is enabled in your database, run the following query as a privileged user:

SELECT parameter, value
FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

The true/false value of the parameter will indicate whether or not it is enabled.

Source: Oracle Documentation>

Create a tablespace using input variables

Here is a script to help set up tablespaces based on the path used for the system tablespace:

SET LINES 200
-- NEW_VALUE in sqlplus specifies a variable to hold a column value
COL tabspace_path FORMAT A50 NEW_VALUE path

SELECT SUBSTR(FILE_NAME, 1, INSTR(FILE_NAME, '/', -1) -1) tabspace_path
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSTEM';

SELECT '&path' variable_value
FROM DUAL;

CREATE BIGFILE TABLESPACE test_tbspc DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

exit

Run the script:

oracle@myserver:[testdb01]# sqlplus / as sysdba @test.sql

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


TABSPACE_PATH
--------------------------------------------------
/u02/oradata/testdb01
old   1: SELECT '&path' variable_value
new   1: SELECT '/u02/oradata/testdb01' variable_value

VARIABLE_VALUE
---------------------
/u02/oradata/testdb01

Enter value for tablespace_name: mytablespace
old   1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
new   1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '/u02/oradata/testdb01/mytablespace.dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED

Tablespace created.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Can I decrypt a tablespace that has previously been encrypted? Can I disable TDE?

Question: Can I decrypt a tablespace that has previously been encrypted? Can I disable TDE?

Answer:
In versions prior to 12.2, you cannot.
From Version 12.2, you can.

In Oracle Versions prior to 12.2, this is what Oracle writes about decrypting a tablespace?

"You cannot decrypt a tablespace that has been created encrypted. You must create an unencrypted tablespace and re-create the database objects in the unencrypted tablespace."

In version 12.2, Oracle has added support to decrypt both online and offline tablespaces.

The syntax for an offline tablespace decryption would be

administer key management set keystore open identified by software_keystore_password; 
alter tablespace TEST_TABSPC offline;
alter tablespace TEST_TABSPC encryption offline decrypt;
alter tablespace TEST_TABSPC online;

An online decryption would executed like this:

alter tablespace TEST_TABSPC encryption online decrypt file_name_convert = ('test_tabspc.dbf', 'test_tabspc_decrypted.dbf');

For the online method to work, compatible should be set to 12.2, and there must be enough storage available for the database server that you can hold a copy of the entire tablespace to be decrypted.

How to check the current kernel settings

sysctl -a 

or, to limit the search to kernel parameters that starts with "sem":

sysctl -a |grep shm

Example output:

[root@myserver ~]# sysctl -a | grep sem
kernel.sem = 250        32000   100     128