Oracle Wallet: secure external password storage

Some times we need database connection from shell script stored on file system. This can be a security issue, if the script contains database connection credential. To nullify this problem oracle provide a solution called wallet. Oracle wallet is a client-side secure external password container where DB login credentials are stored. Using this shell scripts can connect to DB using the "/@db_alias" syntax.

Step 1 : Set location for wallet

we Like to put the wallet files in $ORACLE_HOME/network/admin. Thus the location will be '/oracle/product/11.2.0/dbhome_1/network/admin'. Add following lines to sqlnet.ora

WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/oracle/product/11.2.0/dbhome_1/network/admin)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0


Step 2: Set DB alias

Add the following lines to listerer.ora

ora_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA.DB1.ORACLE.COM) ) )


Step 3: Create Wallet

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -create

You will ask to enter password. The password length must be 8+ containing alpha-numeric characters.


Step 4: Add database login credentials into wallet

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -createCredential ora_db scott tiger

This will ask for password conformation and you shout give the same password which you gave when creating wallet.


Step 5: Listing credentials present in wallet 

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -listCredential


Step 6: Connect Db using wallet

sqlplus /@ora_db

Transportable Tablespace (TTS)

'Transportable Tablespace' (TTS) first introduce in Oracle 8i and it become matured as time goes by.  You can use this  feature to copy a set of tablespaces from one Oracle Database to another.


For a DBA, transport data from one database to another is a very common task and you can do it in different ways

Method           Hardness     Best Suitable For    Availability

Database Link        Easy           Less than 50 GB           High
Data Pump           Moderate     Over 50 GB                 High
TTS                 Moderate     Terabytes of Data        Low
RMAN Duplicate    Hard           Full Database              High




Requirements:

• A tablespace must be totally self contained to be transportable.
• The source and target database must use the same character set and national character set.
• You cannot transport a tablespace to a target database in which a tablespace with the same name already exists.
• Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
• There are some limitations in encryption tables.

Lets see how it works.................
 
IN SOURCE DATABASE


1. Create two tablespaces and one user

sqlplus / as sysdba;

CREATE TABLESPACE TT01 DATAFILE '/oradata/tt_01.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED BLOCKSIZE 8K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ONLINE ;

CREATE TABLESPACE TT02 DATAFILE '/oradata/tt_02.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED BLOCKSIZE 16K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ONLINE ;


CREATE USER test identified by test default tablespace tt01;
GRANT connect,resource to test;

2. Create a table with some data

conn test/test;

CREATE TABLE test_data(
id number (10),
alphabate varchar2 (10)
) TABLESPACE tt01;

ALTER TABLE test_data ADD CONSTRAINT PK_TEST_DATA_ID PRIMARY KEY (ID) using index tablespace tt02;

INSERT INTO test_data VALUES (1,'A');
INSERT INTO test_data VALUES (2,'B');
INSERT INTO test_data VALUES (3,'C');
INSERT INTO test_data VALUES (4,'D');
INSERT INTO test_data VALUES (5,'E');

commit;

SELECT segment_name, tablespace_name from user_segments;

SEGMENT_NAME       TABLESPACE_NAME
------------------ ------------------
TEST_DATA          TT01
PK_TEST_DATA_ID    TT02

So we have a table in one tablespace and an unique index (creat for primary key) in another tablespace.



3. tablespace's 'self contained' Test

conn / as sysdba;

EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TT01', incl_constraints => TRUE);

SELECT * FROM sys.transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index TEST.PK_TEST_DATA_ID in tablespace TT02 enforces primary constraints  of table TEST.TEST_DATA in tablespace TT01.

we got this error because TT01 tablespace contain a table which has an index stored in another tablespace.

EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TT01,TT02', incl_constraints => TRUE);

SQL> SELECT * FROM sys.transport_set_violations;

no rows selected

This time no Error because all two tablespaces are included. Now make them 'Read Only'.


ALTER TABLESPACE tt01 READ ONLY;
ALTER TABLESPACE tt02 READ ONLY;


4. Take metadata of those 2 tablespaces by using datapump

expdp system directory=dump_dir transport_tablespaces=tt01,tt02 dumpfile=trans_tablespaces.dmp logfile=trans_tablespaces.log

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/ ******** directory=dump_dir transport_tablespaces=tt01,tt02 dumpfile=trans_tablespaces.dmp logfile=trans_tablespaces.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /d01/dump_dir/trans_tablespaces.dmp
******************************************************************************
Datafiles required for transportable tablespace TT01:
  /oradata/tt_01.dbf
Datafiles required for transportable tablespace TT02:
  /oradata/tt_02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 02:27:17


5. Copy the datafiles to destination database

scp tt_01.dbf oracle@db2:/oradata/
scp tt_02.dbf oracle@db2:/oradata/

6. Make the read_only tablespaces to read write mode

sqlplus / as sysdba

ALTER TABLESPACE tt01 READ WRITE;
ALTER TABLESPACE tt02 READ WRITE;


IN DESTINATION DB

1. Create a user same as source DB

sqlplus / as sysdba;

CREATE USER test identified by test ;
GRANT connect,resource to test;


2. Import the metadata

impdp system directory=dump_dir dumpfile=trans_tablespaces.dmp transport_datafiles='/oradata/tt_01.dbf','/oradata/tt_02.dbf' logfile=imp_trans_tablespaces.log


3. Verify Tablespace and Object Movement

conn test/test

SELECT segment_name, tablespace_name from user_segments;

SEGMENT_NAME         TABLESPACE_NAME
-------------------- --------------------
TEST_DATA            TT01
PK_TEST_DATA_ID      TT02

SELECT * FROM TEST_DATA;

        ID ALPHABATE
---------- ----------
         1 A
         2 B
         3 C
         4 D
         5 E

Well we are done! To know more about TTS please go through the Oracle Doc http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm

Multiplex online Redo Logfile

It is important to have multiple (at list two) copy of each online redo logfile. To avoid the disaster of disk failure, it is recommended to place each member of a specific online redo logfile group in different disk.

To view to current online redo logfile group members

SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILES;

Suppose we have disk01 (mount point /u01 ) that contains current online redolog files .To add member in redo logfile group 1,2 and 3 in disk02 ( mount point /u02 )

ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo01.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo02.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo03.log' TO GROUP 3;

Track Database Growth

Some time it become very important to monitor your database growth specially in test database machines. Usually these machines have very limited disk space.
Thus, tracking the consumption of disk space is one of the frequent tasks in administrative checklist. Here is a SQL script that gives you the current size of database and average disk space consumed in each day.


SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
SELECT a.days,a.tsname
, used_size_mb
, used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
FROM (
SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
,ts.tsname
,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days
) a
) b GROUP BY b.tsname ORDER BY b.tsname;

Moving Oracle Text Index

Moving an index from one tablespace to another tablespace is very easy task. It can be accomplished by using rebuild option:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE;

You can even do it online for most indexes:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE ONLINE;

But trying to move a domain index (such as Oracle Text Index) is not so simple. If you follow thw above way it will cause error:

ALTER INDEX my_text_index REBUILD TABLESPACE NEW_TABLESPACE;
ORA-29871: invalid alter option for a domain index

You may ask What is the reson behind that error and how to resolve it? In fact Domain index is a set of other objects. Oracle Text CONTEXT index is set of tables:

* DR$[index_name]$I
* DR$[index_name]$K
* DR$[index_name]$N
* DR$[index_name]$R

Unfortunately to move context index you have to drop and recreate that text index. But first you need to specify storage parameters:

begin
ctx_ddl.create_preference('TEXT_INDEX_STORE', 'BASIC_STORAGE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'K_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'R_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'N_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_INDEX_CLAUSE',
'tablespace NEW_TABLESPACE COMPRESS 2');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'P_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
end;
/

and then just drop the previously created index and recreate that index with changed parameters

create index MY_TEXT_I on MY_TAB(text_column)
indextype is ctxsys.context parameters('storage TEXT_INDEX_STORE');

Multiplexing Control File

Oracle consist of three major physical files, they are :

• Controlfiles
• Datafiles
• Online Redo log files
  

Among them control files are the most impotent one. Controlfile contains Database name, database creation date, Tablespace names, Physical location of datafiles and Recovery information.

With default installation, Oracle has 3 control files placed in same physical location. According to database availability, It is safe to place the 3 controlfiles in disk.


To see the current physical location of control file

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------
/ua1/control01.ctl
/ua1/control02.ctl
/ua1/control03.ctl

Suppose we have two HD and those two are mount as /ua1 and /ua2. So we need to move at list one controlfile in /ua1 and we move the 3rd contronlife. there are several way to do the Control File Multiplexing:

1. Using SPFILE:

The steps to multiplex control files using an SPFILE are describe bellow:

Login as SYSDBA

1. Alter the SPFILE: Using the ALTER SYSTEM SET command, alter the SPFILE to include a list of all control files to be used.

SQL> ALTER SYSTEM SET control_files='/ua1/control01.ctl'
,'/ua1/control01.ctl', '/ua1/control01.ctl' scope=spfile;


2. Shut down the database: Shut down the database in order to create the additional/ relocate control files on the operating system.

SQL> SHUTDOWN IMMEDIATE;

3. Create additional control files: Using the operating system copy command, create/move the additional control files as required and verify that the files have been created in the appropriate directories.

mv /ua1/control01.ct /ua2

4. Start the database: When the database is started the SPFILE will be read and the Oracle server will maintain all the control files listed in the CONTROL_FILES parameter.

SQL>STARTUP;


To see the changed physical location of control file

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------
/ua1/control01.ctl
/ua1/control02.ctl
/ua2/control03.ctl

Resize the Online Redo Log files

When you are working on large data movement in Oracle, you must concern about some performance overhead and online redo log switch is one of them. Recently i am working on a migration project where i need to migrate a 19.5GB MySQL 5.0 database to ORACLE 10gR2 database. I used Oracle SQL developer to do the job and the job takes about 5 hour to complete. To do the job faster i perform some tuning stuff on ORACLE and increasing the Online Redo Log files is the most fruitful one.

Now i am going to describe how i have done this. The procedure was learned from a release note of metalink .



1. First see the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 52428800 INACTIVE
2 52428800 CURRENT
3 52428800 INACTIVE

Logs are 50MB from above which is default in oracle 10g, let's size them to 100MB.


2. Retrieve all the log member names for the groups:

SQL> select group#, member from v$logfile;

GROUP# MEMBER
--------------- ----------------------------------------
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf


3. Now drop the log group 1 and recreate it with increased size

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1
'/usr/oracle/dbs/log1PROD.dbf' size 100M reuse;

4. Check the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 52428800 INACTIVE
5. Do the same for log group 3

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 3
'/usr/oracle/dbs/log3PROD.dbf' size 100M reuse;


SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 CURRENT
3 10485760 INACTIVE
6. Now we go for group 2 but it is now used by oracle, so first switch the log

SQL> alter system switch logfile;

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 52428800 INACTIVE
3 10485760 CURRENT
SQL> alter database drop logfile group 2;


SQL> alter database add logfile group 2
'/usr/oracle/dbs/log2PROD.dbf' size 100M reuse;

7. Check the size of the current logs:

SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 10485760 UNUSED
2 10485760 UNUSED
3 10485760 CURRENT

** some times you may find that a log group status is ACTIVE, in that case you should
make a database checkpoint like this

SQL> ALTER SYSTEM CHECKPOINT;

** FOR safety reason please take a full backup of your database before performing this.

Automatic Undo Management

In earlier versions of ORACLE, transactions undo information was stored into undo segment until a commit or rollback command was issued. When the commit or rollback command was issued, it purge the undo segment which contains corresponding transaction undo information. This system is known as manual undo management.

In version 9i ORACLE introduce automatic undo management along with manual management. So one can use either automatic or manual management but not both at a time. The new method freed DBA from periodical undo management and tuning. In also facilitate the DBA to specify how long undo information is stored after a commit occur. This feature eliminate the "snapshot too old" error of long running queries and also support ORACLE flashback queries.

Create Undo Tablespace

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u01/undotbs01_01.dbf'
SIZE 1024M BLOCKSIZE 16K;

alter system set undo_tablespace='UNDOTBS' scope=both;

Enabling Automatic Undo Management

UNDO_MANAGEMENT = AUTO -- Default is MANUAL
UNDO_TABLESPACE = undotbs_01 -- The name of the undo tablespace.
UNDO_RETENTION = 900 -- The time undo is retained. Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE -- Suppress errors when MANUAL undo admin SQL statements are issued.

Please set the following parameters

-- Dynamic Parameters.
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS_02;
ALTER SYSTEM SET UNDO_RETENTION=1800;
ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;

-- Static Parameters.
ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

Maintenance of Undo Tablespace

-- Add a datafile.

ALTER TABLESPACE undotbs_01 ADD DATAFILE '/u0/undo0102.dbf'
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

-- Resize an undo datafile.

ALTER DATABASE DATAFILE '/u0/undo0102.dbf' RESIZE 10M;

-- Perform backup operations

ALTER TABLESPACE undotbs_01 BEGIN BACKUP;
ALTER TABLESPACE undotbs_01 END BACKUP;

-- Drop an undo tablespace.
DROP TABLESPACE undotbs_01;

Sometimes the undo tablespace become too big to manage. In such case you can resize the datafiles or create a new undo tablespace in another disk location

- Resize an undo datafile.

ALTER DATABASE DATAFILE '/u0/undo0102.dbf' RESIZE 10M;

-- create new undo tablespace and drop the old one

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u0/undotbs01_01.dbf' SIZE 1024M BLOCKSIZE 16K;

ALTER SYSTEM SET undo_tablespace='UNDOTBS' scope=both;

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Monitoring Undo Tablespace

You may use the following dictionary viewers:

V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
DBA_UNDO_EXTENTS

Resizing Temporary Tablespace

In many database configurations, the DBA will choose to allow their temporary tablespace (actually the tempfile(s) for the temporary tablespace) to autoextend. A runaway query or sort can easily chew up valuable space on the disk as the tempfiles(s) extends to accommodate the request for space. If the increase in size of the temporary tablespace (the tempfiles) gets exceedingly large because of a particular anomaly, the DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space. The obvious action would be to resize the tempfiles using the following statement:

SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;
alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Ouch. You next bounce the database and attempt the same statement only to be greeted with the same error!

Several methods exist to reclaim the used space used for a larger than normal temporary tablespace depending on which release of Oracle you are running. The method that exists for all releases of Oracle is to simply drop and recreate the temporary tablespace back to its original (or another reasonable) size. If you are using Oracle9i or higher, you can apply another method which is to drop the large tempfile (which will drop the tempfile from the data dictionary AND the O/S file system) using the alter database tempfile '' drop including datafiles; command. Each method is explained below.

Dropping / Recreating Temporary Tablespace Method

Keep in mind that the procedures documented here for dropping and recreating your temporary tablespace should be performed during off hours with no users logged on performing work.

If you are working with a temporary tablespace in Oracle8i or a temporary tablespace in Oracle9i that is NOT the default temporary tablespace for the database, this process is straight forward. Simply drop and recreate the temporary tablespace:

SQL> DROP TABLESPACE temp;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

Oracle9i OR Above Default Temporary Tablespace

The procedures above document how to drop a temporary tablespace that is not the default temporary tablespace for the database. You will know fairly quickly if the tablespace is a default temporary tablespace when you are greeted with the following exception:

SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below. The first step you need to perform is create another temporary tablespace (lets call it TEMP2). Next step is making TEMP2 the default temporary tablespace for the database. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below:

SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.


SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.


Related Topic :

1. Move Datafiles
   

Data Encryption Decryption

For security purpose, some times we need to hide data in database level. Only selected person can see the actual data. In Oracle there are some different ways to accomplish the objective.

Among those ways, Encoding and Decoding technique is describe in this article :

The idea is quite simple, we first generate a random key which is used to encode a column's data of a table. Store the key in a different table. Finally using the stored key we decode the encoded column and view actual data.

In real word, keys are stored in table and table that contains sensitive data are placed in two different schemas. But for simplicity we place both table in one schema.

Here the following steps :

1. Create an schema with necessary privilege :
SQL> Create user test2 identified by test default tablespace user temporary tablespace temp;

User Created.

SQL> Grant connect,resource to test2;
Grant succeeded.

SQL> Grant execute on dbms_crypto to test2;
Grant succeeded.

SQL> Grant execute on UTL_I18N to test2;
Grant succeeded.

Here dbms_crypto is the package used for encryption decryption and UTL_I18N is used for row to string conversion (vice versa).

2. EMP is the table which we want to protect. we will encrypt the NAME column.

CREATE TABLE EMP ( ID NUMBER (10), NAME VARCHAR2 (40) );

ALTER TABLE EMP ADD CONSTRAINT pk_emp_id PRIMARY KEY (ID);

INSERT INTO EMP VALUES (1,'HASAN');

COMMIT;

3. ENC_INFO is the table where we store the key.

CREATE TABLE ENC_INFO ( TABLE_NAME VARCHAR2 (40),
COLUMN_NAME VARCHAR2 (40), ENC_KEY raw (200) );

ALTER TABLE ENC_INFO ADD CONSTRAINT pk_enc_info_tname_colname
PRIMARY KEY (TABLE_NAME,COLUMN_NAME);

INSERT INTO ENC_INFO (TABLE_NAME,COLUMN_NAME,ENC_KEY)
VALUES ('EMP','NAME',dbms_crypto.randombytes (56));

COMMIT;

Here dbms_crypto.randombytes generate 56 random bytes which we use as encryption decryption key.

4. A simple encryption function

create or replace function enc_val
(
content in varchar2,
enc_key raw
)
return varchar2 is
l_enc_val varchar2 (2000);
l_enc_val_raw raw(2000);
l_mod number := dbms_crypto.ENCRYPT_DES
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin

l_enc_val_raw := dbms_crypto.encrypt
(
UTL_I18N.STRING_TO_RAW
(content,'WE8ISO8859P1'),
l_mod,
enc_key
);
l_enc_val:= UTL_I18N.RAW_TO_CHAR
(l_enc_val_raw,'WE8ISO8859P1');
return l_enc_val;
end;

5. A simple decryption function

create or replace function dec_val
(
content in varchar2,
enc_key in raw
)
return varchar2
is
l_ret varchar2 (2000);
l_dec_val raw (2000);
content_raw raw (2000);
l_mod number := dbms_crypto.ENCRYPT_DES
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin

content_raw := UTL_I18N.STRING_TO_RAW
(content,'WE8ISO8859P1');
l_dec_val := dbms_crypto.decrypt
(
content_raw,
l_mod,
enc_key
);
l_ret:= UTL_I18N.RAW_TO_CHAR
(l_dec_val,'WE8ISO8859P1');
return l_ret;
end;

6. The following procedure encrypt EMP table (NAME column)

DECLARE

l_key raw(200);

begin

select enc_key into l_key from ENC_INFO where table_name='EMP' and column_name='NAME';

dbms_output.put_line(l_key);

dbms_output.put_line( enc_val('hasan',l_key)) ;

UPDATE EMP set name=enc_val(name,l_key) where id=1;
commit;

end;
/

7. Now view the table (decrypt using stored key)

select y.id, dec_val(y.name,x.enc_key) name from ENC_INFO x, EMP y;


NB: In above article i just try to explain the mechanism so some important security measure are not shown (actually every one plan his own security measure so you need to plan it your self !)

Oracle Invalid objects : identify and repair

Oracle support complex and interconnected object structure and changing one object can cause some other database objects to become "invalid".

The following sql query will display a list of Oracle invalid objects:

SELECT owner ,object_type ,object_name FROM dba_objects
WHERE status != 'VALID' order by owner, object_type ;

You can also recompile Oracle invalid objects to make them valid. Oracle invalid objects sometimes have dependencies, so it may be necessary to run the recompile process according to the dependencies.

SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM dba_objects where status = 'INVALID' and owner='SCOTT';

Force logging/nologging mode

You can create tables and indexes specifying that the database create them with the NOLOGGING option. When you create a table or index as NOLOGGING, the database does not generate redo log records for the operation. Thus, you cannot recover objects created with NOLOGGING, even if you are running in ARCHIVELOG mode. With respect to the NOLOGGING option, you can get three benefits:

1. Space is saved in the redo log files
2. The time it takes to create the table is decreased
3. Performance improves for parallel creation of large tables


Note: NOLOGGING can be overriden at tablespace level using alter tablespace ... force logging. NOLOGGING has no effect if the database is in force logging mode which can be controlled with (alter database force [no] logging mode).

SQL> set timing on;
SQL> create table sales_logging as select * from sales;
Table created.
Elapsed: 00:00:25.24
SQL> create table sales_nologging NOLOGGING as select * from sales;
Table created.
Elapsed: 00:00:06.59

For just over 900,000 rows, the time difference is around 18 seconds.


Let's suppose a table is created using the NOLOGGING option, regardless of how NOLOGGING is being invoked (in a CREATE statement using NOLOGGING, or in a tablespace with NOLOGGING set). What is the end result of creating a table, inserting data, committing, followed by a delete statement and a rollback statement? Does NOLOGGING mean the DML is not recorded and that you cannot rollback because there was nothing logged in the redo logs?

SQL> create table test (id number) nologging;
Table created.
SQL> insert into test values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> delete from test;
1 row deleted.
SQL> rollback;
Rollback complete.
SQL> select * from test;
ID
----------
1

The answer is no, that is not what NOLOGGING means.

NOLOGGING Means

"The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo."


*** As demonstrated, using the NOLOGGING option can be a time saver, but it can also put you at risk if you do not use it wisely. If you create a table with NOLOGGING, but cannot afford to lose the data, the first step after the data load is complete is to take a backup. If a good part of your loading data into a database work revolves around using SQL*Loader loading data into stage tables, make the tables (or tablespace) NOLOGGING and save yourself some time

Changing Archive Log Destination

In this article you will learn how to change the destination for archived redo log files. Sometime the location where archive redo log is full and you can not access the database.there are two way to this:

1.Temporarily Changing the Destination Using SQL*Plus

If you are automatically archiving, you can use the following command to override the destination specified by the LOG_ARCHIVE_DEST. This command does not change the value in the initialization parameter file. This change is only valid until you restart the instance.

>sqlplus / as sysdba

see current location

sql> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 9285
Next log sequence to archive 9287
Current log sequence 9287

To change the location

sql>ARCHIVE LOG START '/oracle2/arch';

To Verify your changes:

sql> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle2/arch
Oldest online log sequence 9285
Next log sequence to archive 9287
Current log sequence 9287


Permanently Changing the Destination Using SQL*Plus


To permanently change the destination, you must change the initialization parameter. You can change it dynamically with the ALTER SYSTEM command as shown below:

Note: LOG_ARCHIVE_DEST has been deprecated in favor of LOG_ARCHIVE_DEST_n for Enterprise Edition users. If you do not have Enterprise Edition or you have not specified any LOG_ARCHIVE_DEST_n parameters, LOG_ARCHIVE_DEST is valid.


> sqlplus / as sysdba

Issue the ALTER SYSTEM command to update the value of the LOG_ARCHIVE_DEST_n parameter in memory and in your SPFILE:

sql> ALTER SYSTEM SET log_archive_dest ='/oradata2/arch' scope=both;

To Verify your changes:

sql> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle2/arch
Oldest online log sequence 9285
Next log sequence to archive 9287
Current log sequence 9287

IN ORACLE 10g

To see archive log status
>sqlplus / as sysdba

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 132
Next log sequence to archive 134
Current log sequence 134

To see the physical archive location

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/oracle/oradata/flash_r
ecovery_area/
db_recovery_file_dest_size big integer 2G


To change the size of archive log

SQL> alter system SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='orca';

System altered.

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/oracle/oradata/flash_r
ecovery_area/
db_recovery_file_dest_size big integer 10G
SQL>

To change the Physical Location:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='/backup/oracle/flash_recovery_area/' SCOPE=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size 2020448 bytes
Variable Size 218106784 bytes
Database Buffers 1023410176 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/oracle/flash_recovery_
area/
db_recovery_file_dest_size big integer 10G

Enabling ARCHIVELOG Mode

OVERVIEW

Most of the High Availability features of Oracle require you to enable ARCHIVELOG mode for your database.When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system. Archive logs are utilized by RMAN, Data Guard, Flashback and many others.

If you are going to enable archivelog mode on a production database, I recommend shutting down the database and take a cold backup (Keeping a "final noarchivelog mode backup" which is to be a good and excepted practice).

Enabling Archive Mode

Enabling archive mode is simple, set the parameter LOG_ARCHIVE_DEST then connect to your database in mounted but closed mode (startup mount) and alter the database.

Lets start by checking the current archive mode of your DB.

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

So we're in NOARCHIVELOG mode and we need to change.

SQL> CONN / AS SYSDBA;
SQL>ALTER SYSTEM set LOG_ARCHIVE_DEST = "c:\oracle\admin\test\archive"
scope = both;
System altered.

SQL> Archive Log List;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:\oracle\admin\test\archive
Oldest online log sequence 161
Next log sequence to archive 163
Current log sequence 163

You can specify as many as 10 diffrent archive log destinations by using the paramters log_archive_dest_1 through log_archive_dest_10. Remember, if you run out of space in your archive log destination the database will shut down/Hang!

SQL> shutdown immediate;
ORACLE instance shutdown.
SQL> startup mount
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  1300928 bytes
Variable Size             157820480 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

There are several system views that can provide you with information reguarding archives, such as:

V$DATABASE : Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.

V$ARCHIVED_LOG: Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST: Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES: Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG:Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.

V$LOG:Displays all redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY:Contains log history information such as which logs have been archived and the SCN range for each archived log.

Disable ARCHIVELOG Mode

Disabling archive mode is simple, connect to your database in mounted but closed mode (startup mount) and alter the database.

SQL> shutdown immediate;
ORACLE instance shutdown.
SQL> startup mount
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  1300928 bytes
Variable Size             157820480 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> alter database noarchivelog; 
Database altered.
SQL> alter database open; Database altered. 

Changing Character Sets Of Database

When computer systems process characters, they use numeric codes instead of the graphical representation of the character. For example, when the database stores the letter A, it actually stores a numeric code that is interpreted by software as the letter. These numeric codes are especially important in a global environment because of the potential need to convert data between different character sets.

Character set currently used

To see which character set currently used by the database, please do the following:

SQL> conn / as sysdba

SQL> SELECT view_name FROM dba_views WHERE view_name LIKE '%NLS%';

VIEW_NAME
------------------------------
V_$NLS_PARAMETERS
V_$NLS_VALID_VALUES
GV_$NLS_PARAMETERS
GV_$NLS_VALID_VALUES
NLS_SESSION_PARAMETERS
NLS_INSTANCE_PARAMETERS
NLS_DATABASE_PARAMETERS
EXU9NLS

8 rows selected.

To see details

SQL> select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.2.0

20 rows selected.

Changing Character Sets

Note: Depending on the character sets involved this may result in data loss. So please try this in test environments before proceeding in production.

SQL> CONN / AS SYSDBA

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP RESTRICT;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE CHARACTER SET WE8MSWIN1252;

If the above fails:

SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

done ! Now see what we have done:

SQL>SELECT * FROM gv_$nls_parameters;

Renaming / Moving Data Files, Control Files, and Online Redo Logs

Renaming / Moving Data Files, Control Files, and Online Redo Logs

*** Be sure that your database is in archivelog mode.
Overview


Once data files, online redo log files and control files has been created in the
database, it may be necessary to move them in order to better manage, size or I/O requirements. It also necessary when the file system (drive) has reached 85%-95% used. There are several methods used by DBAs for moving datafiles, online redo log files and control files. In all of these methods, operating system commands are used to move the files while the Oracle commands serve primarily to reset the pointers to those files.

There are two methods for moving / renaming physical database files within Oracle. The first is to shut the database down, move (or rename) the file(s) using O/S commands, and finally, use the ALTER DATABASE command to reset the pointers to those files within Oracle.

The second method can be done while the database is running and uses the ALTER TABLESPACE command. The tablespace will need to be taken offline during the time the file(s) are being moved or renamed. Once the files are moved (or renamed), use the ALTER TABLESPACE command to reset the pointers within Oracle and finally, bring the tablespace back online. This method only applies to datafiles whose tablespaces do not include SYSTEM, ROLLBACK or TEMPORARY segments.

Following is an example of how to manipulate datafiles in a tablespace using both the alter database method and the alter tablespace method. All examples will use an Oracle9i databse (9.2.0.5.0) running on Sun Solaris 2.9.

Moving Datafiles while the Instance is Mounted

Moving or renaming a datafile while the database is in the MOUNT stage requires the use of the ALTER DATABASE command. When using the ALTER DATABASE method to move datafiles, the datafile is moved after the instance is shut down. A summary of the steps involved follows:

1. Shutdown the instance
   
2. Use operating system commands to move or rename the files(s).
3. Mount the database and use the ALTER DATABASE to rename the file within the database.
   
4. Opening the Database
   

% sqlplus / as sysdba

SQL> shutdown immediate;

SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf

SQL> startup mount;

SQL> alter database rename file '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';

Do not disconnect after this step. Stay logged in and proceed to open the database!

SQL> alter database open;

SQL> exit;


Moving Datafiles while the Instance is Open

Moving or renaming a datafile while the database is in the 'OPEN' stage requires the use of the ALTER TABLESPACE command. When using the ALTER TABLESPACE method to move datafiles, the datafile is moved while the instance is running. A summary of the steps involved follows:

1. Take the tablespace OFFLINE.
   
2. Use operating system commands to move or rename the file(s).
   
3. Use the ALTER TABLESPACE command to rename the file within the database.
   
4. Bring the tablespace back ONLINE.
   

NOTE: This method can only be used for non-SYSTEM tablespaces. It also cannot be used for tablespaces that contain active ROLLBACK segments or TEMPORARY segments.

% sqlplus "/ as sysdba"

SQL> alter tablespace INDX offline;

SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf

SQL> alter tablespace INDX
2 rename datafile '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';

Do not disconnect after this step. Stay logged in and proceed to bring the tablespace back online!

SQL> alter tablespace INDX online;

SQL> exit


Moving Online Redo Log Files

Online redo log files may be moved while the database is shutdown. Once renamed (or moved) the DBA should use the ALTER DATABASE command to update the data dictionary. A summary of the steps involved follows:

1. Shutdown the instance
2. Use operating system commands to move the datafile.
3. Mount the database and use ALTER DATABASE to rename the log file within the database.
4. Opening the Database
   

% sqlplus "/ as sysdba"

SQL> shutdown immediate;
SQL> !mv /u06/app/oradata/ORA920/redo_g03a.log /u03/app/oradata/ORA920/redo_g03a.log
SQL> !mv /u06/app/oradata/ORA920/redo_g03b.log /u04/app/oradata/ORA920/redo_g03b.log
SQL> !mv /u06/app/oradata/ORA920/redo_g03c.log /u05/app/oradata/ORA920/redo_g03c.log

SQL> startup mount;

SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03a.log' to
2 '/u03/app/oradata/ORA920/redo_g03a.log';
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03b.log' to
2 '/u04/app/oradata/ORA920/redo_g03b.log';
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03c.log' to
2 '/u05/app/oradata/ORA920/redo_g03c.log';

Do not disconnect after this step. Stay logged in and proceed to open the database!

SQL> alter database open;
SQL> exit


Moving Control Files

The following method can be used to move or rename a control file(s). A summary of the steps involved follows:

1. Shutdown the Instance
2. Move the Control File
3. Edit the init.ora
4. Startup the Instance
   

% sqlplus "/ as sysdba"

SQL> shutdown immediate

SQL> !mv /u06/app/oradata/ORA920/control01.ctl /u03/app/oradata/ORA920/control01.ctl
SQL> !mv /u06/app/oradata/ORA920/control02.ctl /u04/app/oradata/ORA920/control02.ctl
SQL> !mv /u06/app/oradata/ORA920/control03.ctl /u05/app/oradata/ORA920/control03.ctl

Within the init.ora file, there will be an entry for the "control_files" parameter. Edit this entry to reflect the change(s) made to the physical control file(s) moved in the previous example.

...
control_files = (/u03/app/oradata/ORA920/control01.ctl,
/u04/app/oradata/ORA920/control02.ctl,
/u05/app/oradata/ORA920/control03.ctl)
...


SQL> startup open
SQL> exit

Start a Database instance

To START a database instance Use the followings:

Syntax: (This is a SQL*Plus command, not part of standard SQL)

STARTUP [FORCE] [RESTRICT] [PFILE=filename] NOMOUNT

STARTUP [FORCE] [RESTRICT] [PFILE=filename] MOUNT [dbname]

STARTUP [FORCE] [RESTRICT] [PFILE=filename] OPEN [Open_options] [dbname]

Open_options: READ {ONLY | WRITE [RECOVER]} | RECOVER

Key:

FORCE -Shut down the current Oracle instance (if it is running) with SHUTDOWN mode ABORT, before restarting it. If the current instance is running and FORCE is not specified,an error results. FORCE is useful while debugging and under abnormal circumstances. It should not normally be used.

RESTRICT -Only allow Oracle users with the RESTRICTED SESSION system privilege to connect to the database. Later,you can use the ALTER SYSTEM command to disable the restricted session feature.

PFILE=filename - The init.ora parameter file to be used while starting up the instance. If PFILE is not specified,then the default STARTUP parameter file is used. The default file used is platform specific. For example, the default file is $ORACLE_HOME/dbs/init$ORACLE_SID.ora on UNIX, and %ORACLE_HOME%\database\initORCL.ora on Windows.

MOUNT dbname -Mount a database but do not open it. dbname is the name of the database to mount or open. If no database name is specified, the database name is taken from the initialization parameter DB_NAME.

OPEN -Mount and open the specified database.

NOMOUNT -Don't mount the database upon instance startup. Cannot be used with MOUNT, or OPEN.

RECOVER -Specifies that media recovery should be performed, if necessary, before starting the instance.

STARTUP RECOVER has the same effect as issuing the RECOVER DATABASE command and starting an instance. Only 'complete recovery' is possible with the RECOVER option. Recovery proceeds, if necessary, as if AUTORECOVERY is set to ON, regardless of whether or not AUTORECOVERY is enabled.

If a redo log file is not found in the expected location, recovery will continue by prompting you with the suggested location and name of the subsequent log files that need to be applied.

Shutdown a Database instance

To Shutdown a database instance please do the followings:

Syntax: (This is a SQL*Plus command, not part of standard SQL)

SHUTDOWN ABORT

SHUTDOWN IMMEDIATE

SHUTDOWN TRANSACTIONAL [LOCAL]

SHUTDOWN NORMAL

key:

ABORT - The fastest possible shutdown of the database without waiting for calls to complete or users to disconnect. Uncommitted transactions are not rolled back. All users currently connected to the database are implicitly disconnected and the next database startup will require instance recovery. You must use this option if a background process terminates abnormally.

IMMEDIATE - Does not wait for current calls to complete or users to disconnect from the database. Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup.

NORMAL - NORMAL is the default option which waits for users to disconnect from the database. Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup.

TRANSACTIONAL [LOCAL] - A planned shutdown of an instance, allowing active transactions to complete first. It prevents clients from losing work without requiring all users to log off. No client can start a new transaction on this instance. Attempting to start a new transaction results in disconnection. After completion of all transactions, any client still connected to the instance is disconnected. Now the instance shuts down (SHUTDOWN IMMEDIATE). The next startup of the database will not require any instance recovery procedures.

The LOCAL mode specifies a transactional shutdown on the local instance only, so that it only waits on local transactions to complete, not all transactions. This is useful, for example, for scheduled outage maintenance.