Configuring auditing

How to check if auditing is enabled:

1. connect as sysdba
2. run

   SHOW PARAMETER AUDIT_TRAIL;
   

if auditing_trail parameter is set to None, enable auditing by performing the following steps:

1. Update parameter file:

   ALTER SYSTEM SET AUDIT_TRAIL='DB' SCOPE=SPFILE; 
   

2. Shutdown the database

   SHUTDOWN IMMEDIATE;
   

3. Re-start and open database

   STARTUP OPEN;
   

4. Check the parameter value:

   SHOW PARAMETER AUDIT_TRAIL;
   

When AUDIT_TRAIL parameter is set to DB, audit data can be sound in SYS.AUD$ table, or one of the views based on this tabel: DBA_AUDIT_TRAIL, DBA_COMMON_AUDIT_TRAIL or DBA_COMMON_AUDIT_TRAIL. In order to capture the actual SQL command issued by the user, change AUDIT_TRAIL to DB_EXTENDED. In SYS.AUD$ the sql command will show up in SQL_TEXT field. For example:

SELECT ACTION, SQL_TEXT FROM DBA_AUDIT_TRAIL;

To view what is audited for each user, run:

SELECT USER_NAME, AUDIT_OPTION FROM DBA_STMT_AUDIT_OPTS WHERE NOT USER_NAME IS NULL;

To retrieve all the sql statements ran by a current user, run (AUDIT_TRAIL should be set to DB_EXTENDED in order to auddit actual sql statements):

SELECT SQL_TEXT FROM USER_AUDIT_OBJECT;

Moving datafile to a different location

Moving datafile involves 4 steps:

1. Taking datafile offline
2. Moving the file to a new location
3. Recovering the datafile
4. Bringing datafile online

In order to perform the steps above, the database has to be in ARCHIVELOG mode. In order to check, run the following:

ARCHIVE LOG LIST;

The output will be something like that:

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     56
Current log sequence           58

Enable ARCHIVELOG mode and peform the following:

ALTER DATABASE DATAFILE '/u01/oradata/tbs01.dbf' OFFLINE;
ALTER DATABASE RENAME FILE '/u01/oradata/tbs01.dbf' TO '/u40/oradata/Northwind/tbs01.dbf';
RECOVER DATAFILE '/u01/oradata/Northwind/tbs01.dbf';
ALTER DATABASE DATAFILE '/u01/oradata/Northwind/tbs01.dbf' ONLINE;

before you rename the file, make sure you run the following command on the UNIX side to ensure the file on the OS has been moved:

cp /u01/oradata/tbs01.dbf /u01/oradata/Northwind/tbs01.dbf

If you do not move OS file first, you will get an error in SQL Plus while attempting to run ALTER DATABASE RENAME statement

ORA-01511: error in renaming log/data files 
ORA-01516: nonexistent log file, datafile, or tempfile "/u01/oradata/Northwind/tbs01.dbf"

When done, run the statement below to ensure that all your datafiles are in the correct place:

SELECT NAME FROM v$DATAFILE;

or

SELECT * FROM V$DBFILE; (for older versions)

How do you know you are a DBA

You know you are a DBA when being asked "Why are you going to see a dentist?" and you reply "For basic maintenance and performance monitoring"

How to enable ARCHIVELOG mode for a database

ARCHIVELOG mode has a number of benefits:

• Database can be backed up online
• Backup policy can be managed through RMAN
• Database can be recovered point-in-time

By default a database is created in NOARCHIVELOG mode. Here is a sequence of steps to perform in order to enable ARCHIVELOG mode for a database:

1. Connect as sysdba

sqlplus / as sysdba

2. Shut down the database

SHUTDOWN IMMEDIATE;

3. Modify pfile by setting LOG_ARCHIVE_START parameter to true

LOG_ARCHIVE_START=TRUE

4. Startup and mount your database using your parameter file or create spfile from your pfile and then mount

STARTUP MOUNT PFILE='/u01/app/oracle/product/11.2.0.3/dbs/init.ora';

or

CREATE SPFILE='/u01/app/oracle/product/11.2.0.3/dbs/spfile.ora' FROM PFILE='/u01/app/oracle/product/11.2.0.3/dbs/init.ora';
STARTUP MOUNT;

5. Enable ARCHIVELOG mode

ALTER DATABASE ARCHIVELOG;

6. Open your database

ALTER DATABASE OPEN;

After you ran all the above, check the status of your database with the following command:

ARCHIVE LOG LIST;

The output should look something like that:

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     56
Next log sequence to archive   58
Current log sequence           58

ORA-09925: Unable to create audit trail file

Got an infamous "ORA-09925: Unable to create audit trail file" error while trying to log in to sqlplus as sysdba via command line:

sqlplus / as sysdba

I did some research and 90% of cases where this error came up while creating a database via command line, while the user tried to log in to sqlplus in order to startup the database from pfile, the problem was permissions issue - not sufficient permissions to write to the adump directory. It happened to be something different in my case.

Cause: An instance for that ORACLE_SID was alredy started. I ran ps -ef | grep pmon

and saw that the instance was already running and therefore I was getting the error

Solution: Run ps -ef | grep pmon, copy the process id and kill the process using kill processid command.

Creating an Oracle database from command line

#Export all environmental variables

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3
export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$LIBPATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ LD_LIBRARY_PATH
export ORACLE_SID=pubs
export PATH=$ORACLE_HOME/bin:$PATH

#run the commands above to verify environmental variables

env | grep ORA
env | grep LIB

#create a pfile - initpubs.ora $ORACLE_HOME/dbs/inipubs.ora

db_name='pubs'
memory_target=2G
processes = 150
audit_file_dest='/u01/app/oracle/admin/pubs/adump'
audit_trail ='db'
db_block_size=8192
sga_max_size=1073741824
sga_target=1073741824
db_domain=''
db_recovery_file_dest='/u02/flash_recovery_area'
db_recovery_file_dest_size=2G
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_management=AUTO
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/oradata/pubs/control01.ctl, /u01/oradata/pubs/control02.ctl)
compatible ='11.2.0'

#create password file

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwdpubs.ora password=oracle entries=5

#create an entry in /etc/oratab file

pubs:/u01/app/oracle/product/11.2.0.3:N

#log into sqlplus as sysdba by running the following command:

sqlplus / as sysdba

# start the instance with nomount option, specifying your pfile

startup nomount pfile='/u01/app/oracle/product/11.2.0.3/dbs/initpubs.ora';

# run actual create database statement

create database pubs
MAXLOGFILES 5
MAXLOGMEMBERS 2
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 100
logfile group 1 ('/u01/oradata/pubs/redo01.log') size 100M,
        group 2 ('/u02/oradata/pubs/redo02.log') size 100M
character set WE8ISO8859P1
national character set utf8
datafile '/u01/oradata/pubs/system01.dbf' size 500M autoextend on next 10M maxsize unlimited extent management local
user datafile '/u01/app/oracle/oradata/newdata/user01.dbf' size 10m
sysaux datafile '/u01/oradata/pubs/sysaux01.dbf' size 100M autoextend on next 10M maxsize unlimited
undo tablespace undotbs1 datafile '/u01/oradata/pubs/undotbs01.dbf' size 100M
default temporary tablespace temp tempfile '/u40/oradata/pubs/temp01.dbf' size 100M;
 

# run the following scripts to complete database creation

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql
 

# set passwords for sys and system

alter user sys identified by whatever;

alter user system identified by whatever;

Using Oracle Data Pump for import/export

In order to get Data Pump directory location, execute

SELECT directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR'; 

Typically it is $ORACLE_BASE/admin/DBName/dpdump/

Then check priviges that are assigned to the directory by executing;

SELECT grantee, privilege FROM dba_tab_privs dtp WHERE table_name = 'DATA_PUMP_DIR';

The grantee could be a user or a role.

Use the following to create a directory:

CREATE OR REPLACE DIRECTORY dpump_dir1 AS '.../admin/DBName/dpdump';

After a directory is created, you need to grant READ and WRITE permission on the directory to other users. For example, to allow the Oracle database to read and to write to files on behalf of user usr1 in the directory named by dpump_dir1, you must execute the following command:

GRANT READ,WRITE ON DIRECTORY dpump_dir1 TO usr1;

Once the directory access is granted, the user can export his database objects with command arguments from a command line:

expdp usr1/pwd DIRECTORY=dpump_dir1 dumpfile=usr1.dmp

The above will produce the following output:

Export: Release 11.2.0.3.0 - Production on Tue Jan 8 12:48:31 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "USR1"."SYS_EXPORT_SCHEMA_01":  usr1/******** DIRECTORY=dpump_dir1 dumpfile=usr1.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USR1"."TABLE1"                             0 KB       0 rows
. . exported "USR1"."TABLE2"                             0 KB       0 rows
. . exported "USR1"."TABLE3"                                0 KB       0 rows
Master table "USR1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USR1.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/DBName/dpdump/usr1.dmp
Job "USR1"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:49:08

The above will also be written to a default log file in the same directory - export.log or in the log you specify in the command line in LOGFILE parameter:

expdp usr1/pwd DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log

You could also specify particular tables you want to export in a TABLES parameter of your command, in case you do not need all user tables. You can add more than one table separated by comma:

expdp usr1/pwd TABLES=TABLE1,TABLE2 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log

Import command would look something like this:

impdp usr1/pwd TABLES=TABLE1,TABLE2 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=import_usr1.log

To import a schema, you will need to add a SCHEMAS parameter to your command:

expdp usr1/pwd SCHEMAS=USR1 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log



impdp usr1/pwd SCHEMAS=USR1 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=import_usr1.log

And for a full database import/export, you will need to add FULL parameter and set it to y:

expdp usr1/pwd FULL=y DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log



impdp usr1/pwd FULL=y DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=import_usr1.log

To append data to existing tables, add parameter TABLE_EXISTS_ACTION=APPEND to your import statement