Increasing the number of log/data files

Today I had a space constraint issue in my ORACLE Database, so I thought of adding a new datafile to the tablespace, but I was unable to add one more datafile to the existing tablespace. The database has already reached its maximum number of datafiles, this number will be specified with the MAXDATAFILES clause during database creation. I was about to recreate the control file as we do for the versions ORACLE 9i and below, but to my surprise we can do the same dynamically in ORACLE 10g when the database is up and running. This option is very handy in those situations where we need to add more datafiles, but this number also depends on the underlying operating system too, since the OS can open a limited set of files at a particular moment. One can apply the same for MAXLOGFILES clause for increasing the number of log files.

The DB_FILES parameter can also be used for applying the same restriction on number of data and log files. This parameter is not a dynamic parameter, so one need to down the database to change the value of this parameter.

Ports used by ORACLE

One can know the list of ports used by various ORACLE components in the portlist.ini file.
You can find the file in the location specified below,

UNIX : $ORACLE_HOME/install
Windows : %ORACLE_HOME/install

SMON_SCN_TIME - SCN

It is a table owned by SYS User and is filled by SMON every 5 minutes with a timestamp and the current SCN. However, it only counts 1440 records (=5 days). This makes it possible to roughly find an SCN for a point in time in the last 5 days.

Restrict user to export its own objects

One of my Senior DBA's - Bhupinder scripted the below trigger...
Prevent the schema owner to take export on its own objects:

SQL> conn / as sysdba

create or replace trigger prevent_exp_imp
after logon on database
begin
if user='USER1' and upper(sys_context('userenv','module')) Like ('%EXP%') then
raise_application_error (-20001, 'You are not allowed to export data.');
end if;
end;
/


From my client machine:

F:\>exp USER1/USER1@ora file=exp.dmp
Export: Release 10.2.0.2.0 - Production on Tue Mar 18 18:02:09 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to export or import data.
ORA-06512: at line 3
Username:
EXP-00030:

Similarly it can be configured for Import activity too.

Mirroring PFILE

To have two copies of PFILE in a ORACLE Database, follow the below steps...

UNIX:
Create a Symbolic link in $ORACLE_HOME/dbs folder that points to the file in $ORACLE_BASE/admin/XYZ/pfile/initXYZ.ora

ln -s $ORACLE_BASE/admin/XYZ/pfile/initXYZ.ora $ORACLE_BASE/dba/initXYZ.ora

where XYZ is the database

WINDOWS:
Create a pfile in ORACLE_BASE\admin\XYZ\pfile and put an entry in the ORACLE_HOME\dbs\initXYZ.ora as given below

ifile=\admin\XYZ\pfile\initXYZ.ora

where XYZ is the database

Tablespace Report

Checking the tablespace is a day today activity. We can use the Enterprise Manager in ORACLE 10g to monitor the tablespace. But sometimes the EM eats up lots of system resources, so I don't use EM to monitor the database, but it is quite useful in many places. I decided to come up with a shell script to generate a tablespace report as shown below,


Script to generate and mail the report : Code.zip

Exclude statistics in data pump

Normally, when I tried to play with Data Pump, the expdp utility will always take less time when compared to impdp. The impdp takes more time in importing the collected statistics into the new schema.
So, yesterday I tried to use the exclude parameter in impdp/expdp to utilize the power of the data pump. Actually what I did was I didn't collect the statistics during expdp,
expdp exclude=statistics...
After this, the entire impdp task was very fast. The same exclude statistics can also be used in the impdp utility as given below,
impdp exclude=statistics...

But you should not use the exclude=statistics parameter if you have already done this during the expdp. It will throw an error "ORA-39168: Object path STATISTICS was not found."
The Data Pump is a good utility.