DB | DBA Knowledge Base

Configuring Flashback Database

March 28, 2011 Leave a comment

Connect to your database as sysdba.

1. Ensure that your database is in ARCHIVELOG mode.

SQL> select log_mode from v$database;

LOG_MODE
————————
NOARCHIVELOG

1.1 My database is not in ARCHIVELOG mode let’s enable it.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

SQL> select log_mode from v$database;

LOG_MODE
——————-
ARCHIVELOG

2. Setting up flash recovery area.

SQL> show parameter db_recovery_file_dest;

NAME                                        TYPE             VALUE
————————————   ———–     ————-
db_recovery_file_dest           string
db_recovery_file_dest_size big integer 0

First of all, db_recovery_file_dest_size parameter must be set.

SQL> alter system set db_recovery_file_dest_size=2G;

Then db_recovery_file_dest parameter.

SQL> alter system set db_recovery_file_dest=’D:\TEST\FLASHRECOVERY’;

3. Setting up retention period.

Because of flash recovery area is used in circular fashion, after some period of time(indicates db_flashback_retention_target,default is 1day) old data is overwritten. This parameter instructs Oracle to save flashback files for a certain minutes before overwriting.

SQL> alter system set db_flashback_retention_target=720;

In our case flashback data will be retained for 12 hours before overwriting.

4. Enable flashback logging.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;

At this time RVWR process will be started and flashback buffer will be allocated in SGA.

SQL> alter database open;

5. Check if flashbacking is enabled.

SQL> select flashback_on from v$database;

FLASHBACK_ON
———————–
YES

Filed under DB Tagged with flashback database, oracle

SQL Profile has higher priority than Hint!

March 27, 2011 Leave a comment

Hints on SQL statement do not work???
You may have a syntax error, double check it. But if the syntax is OK, then this problem may be caused by the SQL profile, which doesn’t let hints to work.

Here, in this post I will explain the situation when there is applied SQL profile for some SQL statement and any attempt to use hints on that statement fails.

To solve this problem you should drop that SQL profile or disable it.

Note: This behavior has advantage. It assures that the SQL plan of the SQL statement will not be changed for future runs.

Let’s start…

–Create test table

create table test (n1 number
, n2 number );

–Insert some values

begin
for i in 1 .. 100000
loop
insert into test values(i,i+1);
commit;
end loop;
end;

–Create index

create index test_idx1 on test(n1);

–Analyze table

analyze table test estimate statistics;

–Run the following SQL statement

select /*+ no_index(test test_idx1) */ *
from test where n1=2;

–Calculate explain plan

explain plan for select /*+ no_index(test test_idx1) */ *
from test where n1=2;

select * from table(dbms_xplan.display);

–Output

————————————————————————-
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT |      |     1 |     8 |    49   (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |     1 |     8 |    49   (5)| 00:00:01 |
————————————————————————–

–Create SQL tuning task

declare
my_task_name varchar2(30);
my_sqltext   clob;
begin
my_sqltext := ‘select /*+ no_index(test test_idx1) */ * from test where n1=2’;

my_task_name := dbms_sqltune.create_tuning_task(
sql_text    => my_sqltext,
scope       => ‘comprehensive’,
time_limit => 60,
task_name   => ‘my_sql_tuning_task’);
end;

–Execute SQL tuning task

begin
dbms_sqltune.execute_tuning_task( task_name => ‘my_sql_tuning_task’);
end;

–Generate report

select dbms_sqltune.report_tuning_task( ‘my_sql_tuning_task’)
from dual;

–Output

…

Recommendation (estimated benefit: 95.87%)
——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
‘my_sql_tuning_task’, replace => TRUE);
…
—————————————————————————————–
| Id | Operation                   | Name      | Rows | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | SELECT STATEMENT            |           |     1 |     8 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| TEST      |     1 |     8 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
—————————————————————————————–

–Accept SQL profile

begin
dbms_sqltune.accept_sql_profile(task_name =>’my_sql_tuning_task’
,replace => true
,name => ‘my_profile_name’);
end;

–Calculate explain plan again

explain plan for select /*+ no_index(test test_idx1) */ *
from test where n1=2;

select * from table(dbms_xplan.display);

–Output

—————————————————————————————–
| Id | Operation                   | Name      | Rows | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | SELECT STATEMENT            |           |     1 |     8 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| TEST      |     1 |     8 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
—————————————————————————————–

Note: We have indicated not to use index(/*+ NO_INDEX(TEST TEST_IDX1) */ ), but as explain plan shows, Oracle still uses this index.

If we drop sql profile hint will work. Let’s check it:

begin
dbms_sqltune.drop_sql_profile(name => ‘my_profile_name’);
end;

–Calculate explain plan

explain plan for select /*+ no_index(test test_idx1) */ *
from test where n1=2;

select * from table(dbms_xplan.display);

–Output

————————————————————————–
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT |      |     1 |     8 |    49   (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |     1 |     8 |    49   (5)| 00:00:01 |
————————————————————————–

So SQL profile has higher priority than hints!!!<- Important

Filed under DB Tagged with hints, oracle, sql profile

Install New Unicode Character Set in Oracle(Locale Builder)

March 17, 2011 1 Comment

Locale Builder is an utility to view, modify, or define locale-specific data. As well as to create your own formats for language, territory, character set, and linguistic sort.

I will show you how to create .nlb(binary file) from .nlt(text file).

First of all, let’s discuss what NLT file is.

NLT file content is in xml format which can be created by Oracle Locale Builder(located in %ORACLE_HOME%\nls\builders\lbuilder.bat.)

1. Run lbuilder.bat

Choose File->New->Character Set.

As you can see every field is free, for better understanding let me open already created character set

And the last one shows the NLT file content.

As you can see content is in XML format.

As I said previously NLT file is a text file, but Oracle needs NLB, binary file. Oracle Locale Builder gives the ability to create NLB file from NLT file. Let’s do it.

I consider the situation when we already have NLT file.

2. Run Locale Builder.

Click Generate NLB icon.

Choose the path where your NLT files are.

Be careful, after browsing the folder path I’ve noticed that something wrong is in the path:

There, nltfiles folder name is displayed twice. Let’s see what happens if we click OK.

So I decided to delete second nltfiles and click OK.

Note: If you highlight folder where NLT files are, so do not enter inside, and click Open during browsing, everything will be OK.

NLB files are located in the same directory where NLT files are so for me it is D:\Install\Gbank\nltfiles.

Just a little advice: Save old lx0boot.nlb and lx1boot.nlb files and then replace them by new one, for to avoid any incompatibility .

Copy all of them and paste into %ORACLE_HOME%\nls\data.

New unicode character set is already installed.To use it, do the following:

1. Start->Run->regedit->HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_…-> find and modify NLS_LANG value to AMERICAN_AMERICA.newCharacterSetName(this name can be found in .nlt file in a <name> tag for me it is GE8PC866)

2.Open PL/SQL Developer->choose Tools->Preferences…->Fonts->Select-> and then choose Font:.

Filed under DB Tagged with character set, locale builder, oracle

Retrieve Web-Page Content as XML format in Oracle

March 11, 2011 Leave a comment

In Oracle, there exists a package UTL_HTTP, by which you can make HTTP requests directly from database.
You can use this package if you want to read a webpage.

I will write a simple script, which will send a request to google site and retrieve its content.

declare
v_content long;
v_url varchar2(40):=’www.google.com’;
begin
v_content := utl_http.request(v_url);

dbms_output.put_line( v_content );
end;

Note that this code works well if page size is not more than 2GB(Because LONG type saves maximum 2GB).

For larger pages we should use REQUEST_PIECES. By this method you are not limited to 2GB, but you can indicate maximum bytes by yourself. I will use maximum 100000 bytes.

declare
v_content utl_http.html_pieces;
v_url varchar2(25):=’www.google.com’;
begin
v_content := utl_http.request_pieces( v_url,100000);

for i in 1 .. v_content .count
loop
dbms_output.put_line( v_content (i) );
–exit when ( i = 2 );
end loop;
end;

This script will return all content from the web-page. If you want to retrieve just several lines from there, uncomment “exit when ( i = 2 );” and write desired number of lines instead of 2.

Note that the page is returned as an array not as a single variable.

That is all.

Filed under DB Tagged with oracle, UTL_HTTP, xml

LOB datatypes in Oracle

March 9, 2011 Leave a comment

LOBs support storing large, unstructured objects such as audio, video, picture, text, etc.

Before the LOB, there was LONG and LONG RAW datatypes which also were storing large objects. Oracle still supports it, but strongly recommends using LOBs instead of them. LONG and LONG raw have many restrictions and have less opportunity than LOBs. So let’s discuss their differences:

Table can store multiple LOBs, while you are restricted to use only one LONG column per table.
A LOB can store maximum 4GB in Oracle 8, 8TB in Oracle 9i/10g, 128TB in Oracle 11g. While LONG column can store maximum 2GB.
Table containing LOBs can be partitioned, while table with LONG column cannot be partitioned.
When you are using LOBs you are able to access its data randomly, while you must sequentially read LONG type data from beginning to end.
LOBs can be used in user defined data types (except NCLOB), while LONG cannot be used.

LOB datatypes can be stored inline (within a table) or out-of line (within a tablespace, using a LOB locator) or as an external file (BFILE).

BLOB

BLOBs are binary objects, used to store binary, raw data. BLOBs participate into transaction and can be rolled back or committed.

CLOB

CLOBs are character LOBs. Used to store single byte character set (large texts, xml…). They also participate into transactions.

NCLOB

NCLOBs are used to store multi-byte character data, which corresponds to the national character set defined into the database. They also are participating into transactions.

Three of them are types of Internal LOBs; there also exists external LOB called BFILE. It is the single type, which is external.

BFILE

BFILE is short for Binary File, which is stored outside the database. It is stored into the Operating System as a file. It doesn’t participate into transactions. The amount of file is also limited by OS. BFILEs are read only. They only support random reads, means not sequential read. The changes to the BFILEs can be done through OS.

Filed under DB Tagged with datatype, oracle

Moving Tables with LOB columns to a diff. tablespace

March 7, 2011 Leave a comment

In the previous post I discussed how to move table and its indexes to a different tablespace, and I also mentioned that moving tables with LOB columns to a different tablespace is performed by the different way. So here it is how to do this.

First of all, let’s create a test table with LOB column.

CREATE TABLE TST_TABLE(
COL_ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
COL_CLOB CLOB
);

–Insert one row into this table

INSERT INTO TST_TABLE
VALUES(1,’FIRST ROW’);
COMMIT;

–Query it

SELECT * FROM TST_TABLE

–Result

COL_ID | COL_CLOB
——————————-
1 | <CLOB>

Now let’s check table’s current tablespace.

SELECT TABLE_NAME
,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TST_TABLE’

–Result

TABLE_NAME | TABLESPACE_NAME
—————————————————
TST_TABLE | SYSTEM

Assume that we have already created a test tablespace, let’s say it is called TST_TBS.

–Move table to TST_TBS tablespace

ALTER TABLE TST_TABLE MOVE TABLESPACE TST_TBS

–Let’s check the result

SELECT TABLE_NAME
,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TST_TABLE’

–Result

TABLE_NAME | TABLESPACE_NAME
————————————————–
TST_TABLE | TST_TBS

When you create a table with LOB column, oracle automatically creates LOB segment and LOB index. If you do not indicate their(LOB segment and LOB index) names and where they should be kept, oracle names them itself and creates in the same tablespace as the table is.

By default, segment name starts with SYS_LOB… and index name starts with SYS_IL…

Let’s check what indexes are already created for our table.

SELECT INDEX_NAME                 AS INDX_NM
,INDEX_TYPE                 AS INDX_TP
,TABLESPACE_NAME   AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                      | INDX_TP | TBS_NM | STATUS
—————————————————————————————–
SYS_IL0000055912C00002$$| LOB         | SYSTEM | VALID
TEST_PK                                      | NORMAL | SYSTEM | UNUSABLE

Or you can query the following to identify segment name and index name.

SELECT COLUMN_NAME           AS CLN_NM
,SEGMENT_NAME        AS SEGMENT
,TABLESPACE_NAME AS TBS_NM
,INDEX_NAME                AS INDX_NM
FROM USER_LOBS
WHERE TABLE_NAME=’TST_TABLE’

–Result

CLN_NM |SEGMENT | TBS_NM | INDX_NM
———————————————————————————————————————-
COL_CLOB|SYS_LOB0000055912C00002$$| SYSTEM |SYS_IL0000055912C00002$$

Because of, by default LOB column is stored outside of the table it is still located in the SYSTEM tablespace(was not moved).

In order to move LOB column to a different tablespace, you should run the following command.

ALTER TABLE TST_TABLE MOVE LOB(COL_CLOB)
STORE AS (TABLESPACE TST_TBS);

–Check it

SELECT COLUMN_NAME         AS CLN_NM
,SEGMENT_NAME       AS SEGMENT
,TABLESPACE_NAME AS TBS_NM
,INDEX_NAME               AS INDX_NM
FROM USER_LOBS
WHERE TABLE_NAME=’TST_TABLE’

–Result

CLN_NM |SEGMENT |TBS_NM | INDX_NM
———————————————————————————————————————–
COL_CLOB|SYS_LOB0000055912C00002$$|TST_TBS |SYS_IL0000055912C00002$$

Let’s query the following:

SELECT INDEX_NAME AS INDX_NM
,INDEX_TYPE AS INDX_TP
,TABLESPACE_NAME AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                       | INDX_TP | TBS_NM | STATUS
——————————————————————————————
SYS_IL0000055912C00002$$ | LOB         | TST_TBS | VALID
TEST_PK                                       | NORMAL | SYSTEM | UNUSABLE

As you can see, LOB index was automatically moved to the new tablespace, but normal index still is in the same tablespace. To move it to a different tablespace, run the following:

ALTER INDEX TEST_PK REBUILD TABLESPACE TST_TBS

–Check it

SELECT INDEX_NAME AS INDX_NM
,INDEX_TYPE AS INDX_TP
,TABLESPACE_NAME AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                       | INDX_TP | TBS_NM | STATUS
——————————————————————————————
SYS_IL0000055912C00002$$ | LOB         | TST_TBS | VALID
TEST_PK                                       | NORMAL | TST_TBS | VALID

After rebuilding an index, it became valid. As I also said in the previous post , if at least one index of the table is not valid DML operations will fail.

Filed under DB Tagged with objects, oracle

Moving tables and indexes to a different tablespace

March 5, 2011 1 Comment

Moving table to a different tablespace can be done by several ways:

Using Export/Import.
Or by the following clause:

ALTER TABLE schemaName.tableName REBUILD TABLESPACE tablespaceName;

I will discuss the second variant now.

–Let’s create a test table.

CREATE TABLE TESTTABLE(
TESTCOLUMN NUMBER CONSTRAINT TEST_PK PRIMARY KEY
);

–Insert one row into that table

INSERT INTO TESTTABLE
VALUES(1);
COMMIT;

–Let’s see rowid value

SELECT ROWID,TESTCOLUMN
FROM TESTTABLE;

–Result

ROWID | TESTCOLUMN
——————————————————–
AAANoRAABAAAPVSAAA | 1

Before you move table to a different tablespace, you must have created this tablespace before.

–Now let’s create a test tablespace.

CREATE TABLESPACE testTablespace
DATAFILE ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\testTablespace01.DBF’
SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO

–Let’s see table’s current tablespace

SELECT TABLE_NAME,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

TABLE_NAME | TABLESPACE_NAME
—————————————————-
TESTTABLE | SYSTEM

–Move this table to a test tablespace

ALTER TABLE TESTTABLE MOVE TABLESPACE TESTTABLESPACE;

–See again table’s current tablespace

SELECT TABLE_NAME,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

TABLE_NAME | TABLESPACE_NAME
—————————————————-
TESTTABLE | TESTTABLESPACE

–Let’s see again rowid value

SELECT ROWID,TESTCOLUMN
FROM TESTTABLE;

–Result

ROWID | TESTCOLUMN
——————————————————–
AAANoSAAHAAAAAMAAA | 1

As you can see rowid was changed. It makes indexes of this table unusable. To check it, run the following query.

SELECT INDEX_NAME,TABLE_NAME,STATUS,TABLESPACE_NAME
FROM DBA_INDEXES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

INDEX_NAME | TABLE_NAME | STATUS | TABLESPACE_NAME
—————————————————————————————–
TEST_PK | TESTTABLE | UNUSABLE | SYSTEM

Any DML operation on this table will cause an error, because an index is not valid. Let’s check it:

INSERT INTO TESTTABLE
VALUES(2);
COMMIT;

–Error

ORA-01502: index ‘SYS.TEST_PK’ or partition of such index is in unusable state

This happens because you have moved test table to another tablespace, and this operation changed rowid value. The index still refers to the old rowid value. To solve this, you must rebuild the index.

ALTER INDEX TEST_PK REBUILD TABLESPACE TESTTABLESPACE;

This command will rebuild the index and also moves it to TESTTABLESPACE tablespace. If you want to rebuild index without moving it to another tablespace. Simply, run the following command:

ALTER INDEX TEST_PK REBUILD;

–Let’s check its status

SELECT INDEX_NAME,TABLE_NAME,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TESTTABLE’

–Result

INDEX_NAME | TABLE_NAME | STATUS | TABLESPACE_NAME
—————————————————————————————
TEST_PK | TESTTABLE | VALID | TESTTABLESPACE

You must do this action for each index, to make DML operation resumable on this table.

Note that, if table contains LOB columns this variant doesn’t work. Moving tables with LOB columns to another tablespace is discussed here.

Filed under DB Tagged with objects, oracle

Understanding sysobjects table in SQL Server

February 28, 2011 1 Comment

System tables and views are very useful objects that may be queried in many times by database background processes itself or by DBAs. These objects sometimes contain columns that are not so easy to understand what they are for. Here, in this post I will discuss mostly used content of SYSOBJECT table.

Take into the consideration, that updating or deleting rows in these tables are not recommended. Background processes do it for you.

The most useful columns in SYSOBJECT table are name, id, xtype, uid, parent_obj, crdate.

As you can guess name column saves the name of the database object. Id column is the unique number for this object. xtype shows the type of this object, where:

AF = Aggregate function (CLR)
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = In-lined table-function
IT = Internal table
P = Stored procedure
PC = Assembly (CLR) stored-procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = Table function
TR = SQL DML Trigger
TT = Table type
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

I’ve got this list from the internet,most of them are useful to know.

uid                – saves the user id, which is the owner of this object.
parent_obj – Id of the parent object. For example, for constraint parent_obj will be the ID
                        of the table, on which this constraint was defined.
crdate         -Object creation date.
…

There are several other tables, which shows the user objects:

–Shows the table columns owned by the current user.

select *
from information_schema.columns

–Shows the tables owned by the current user.

select *
from information_schema.tables

–Shows the list of all functions and procedures owned by the current user.

select *
from information_schema.routines

Real World Scenario

One day, the person came to me and asked if it was possible to find the table name when you just know the column name. I told him yes Smile and wrote the following simple query:

select a.name as columnName
,b.name as tablename
from SYSCOLUMNS as a,SYSOBJECTS as b
where a.ID=b.ID
and a.name=’column_name’

I hope this post was useful for you…

Filed under DB Tagged with sql server 2005, system tables

Oracle 10g RAC Single Node Installation on Solaris 10

February 21, 2011 Leave a comment

1. Pre-Installation Tasks

Create oracle user and appropriate groups

First of all, you should have already mounted /u0 on some slice of disk. If you don’t know how to mount directory on disk Smile click here.

Go to the folder /u0 and create the following directories.



cd /u0
mkdir –p app/oracle

Create oinstall and dba groups.



groupadd oinstall
groupadd dba

Create oracle user and assign it oinstall and dba groups.



useradd –s /bin/ksh –d /u0/app/oracle –g oinstall –G dba oracle

Where,
-s option specifies the user default shell (i.e. Korn shell) ,
-d option specifies the home directory for oracle user (i.e. /opt/app/oracle ) ,
-g option specifies the primary group (i.e. oinstall)
-G option specifies the secondary group (i.e. dba)

Make oracle user as the owner of app folder.



chown –R oracle:oinstall app

Change the password for oracle user.



passwd oracle

Configuring SSH

Create RSA and DSA keys on each node.



mkdir .ssh

Generating RSA and DSA keys.



/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa

Add keys to an authorized key file, this file should exist in .ssh directory. If it doesn’t exist, create it. Change your direction to .ssh directory and run the following.



ssh charly1 cat /u0/app/oracle/.ssh/id_rsa.pub >> authorized_keys
ssh charly1 cat /u0/app/oracle/.ssh/id_dsa.pub >> authorized_keys

Enabling ssh user equivalency.



/usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add

To prevent Oracle clusterware installation errors caused by stty commands, add the following text in oracle user’s profile (you should change your directory to oracle home directory and open “.profile” for editing).



if [ -t 0 ]; then
stty intr ^C
fi

Network Requirements

We should create second IP interface.
Open or create the file /etc/hostname.e100g1 and enter the following text:



Host1-priv

Add the following entries into /etc/host file:



20.0.0.100           Host1       loghost
192.168.2.117   Host1-priv
20.0.0.105          Host1-vip

Where 192.168.2.117 is a private IP address and 20.0.0.105 is a virtual IP address of the server.

Connect as a root user and create /etc /hosts.equiv file. Then open this file for editing and enter the following text:



Host1 oracle

Configuring Kernel Parameters

To add the system parameters open /etc/system file and add the following entries:



set shmsys:shminfo_shmmax = 4294967295
set shmsys:shminfo_shmmin = 1
set shmsys:shminfo_shmmni = 100
set shmsys:shminfo_shmseg = 10
set semsys:seminfo_semmni = 100
set semsys:seminfo_semmns = 1024
set semsys:seminfo_semmsl = 256
set md:mirrored_root_flag=1
set noexec_user_stack=1

Identifying Required Software Directories

Creating an Oracle Base Directory



mkdir -p /u0/app/oracle
chown -R oracle:oinstall /u0/app/oracle
chmod -R 775 /u0/app/oracle

Creating the Oracle Clusterware Home Directory



mkdir -p /u0/app/oracle/product/10.2.0/crs
chown -R root:oinstall /u0/app/oracle/product/10.2.0/crs
chmod -R 775 /u0/app/oracle/product/10.2.0/crs

2. Installing Oracle Clusterware

First of all, you should download Oracle Clusterware from the oracle site.

Verifying Oracle Clusterware Requirements with CVU

Extract downloaded file and run runcluvfy.sh file, which should be located in clusterware/cluvfy folder.



./runcluvfy.sh comp nodecon -n Host1 –verbose

Installing Oracle Clusterware with OUI

Initialize the devices that you want to use for the Oracle Cluster Registry (OCR) and Voting Disk(VD)

1.Voting file– uses by the cluster synchronization service deamons for monitoring information across the cluster. It’s size around 20MB(use format utility and reserve 200MB on 5^th slice)

2. Oracle cluster registry(OCR file) it maintain information about the high-availability components such as cluster node list, CRS application profiles(Virtual interconnect protocol address, services). It’s size around 200MB(use format utility and reserve on 7^th slice)

For voting disk

dd if=/dev/zero of=/dev/rdsk/c5t200g35A100TK170Ta00ds5 bs=125829120 count=1 chown oracle:dba /dev/rdsk/c5t200g35A100TK170Ta00ds5
chmod 660 /dev/rdsk/c5t200g35A100TK170Ta00ds5

For OCR

dd if=/dev/zero of=/dev/rdsk/c5t200g35A100TK170Ta00ds7 bs=125829120 count=1 chown root:dba /dev/rdsk/c5t200g35A100TK170Ta00ds7
chmod 660 /dev/rdsk/c5t200g35A100TK170Ta00ds7

Connect as the oracle user and run runInstaller.sh, located in CRS installation.



./runInstaller

Run these scripts and then click OK.

Now we should run ./vipca and configure Host1-vip virtual ip address(20.0.0.105).

Go to the installation window, click ok and retry install failed component.

Installing Oracle database files

Run root.sh script and click OK, then click Exit button.

Configure oracle listener by netca.

Configuring ASM

${ORACLE_HOME}/dbs/spfile+ASM error$

Run the following command, for to make oracle user owner for the following disk slice



chown oracle:oinstall /dev/rdsk/c5156D778A145a11d0s6

Creating database using DBCA

Click Next>> and check/uncheck Configure the Database with Enterprise Manager.

Filed under DB, Unix Tagged with ASM, oracle, RAC, Unix

Fine-Grained Auditing

February 16, 2011 Leave a comment

As I discussed in “Oracle Database Auditing” post oracle auditing is a very powerful utility. Using this option you are able to identify users’ activities, object access and so on..

As we know there exist four types of auditing: statement, object, privilege and fine-grained auditing. I will discuss fine-grained auditing, because previous three options are clarified in “Oracle Database Auditing” post. FGA can be defined only for table and optionally on column.

Fine-grained auditing uses PL/SQL package DBMS_FGA, which has the following methods: creating , dropping, enabling and disabling policies. We will identify which policies are created in the database and also see the audit logs.

Creating FGA Policy

DBMS_FGA.ADD_POLICY method is used for creating FGA policy. Which has the following parameters:

[object_schema] -Username, which owns the object that should be audited.Default is NULL, means current schema.

object_name -Name of the object that should be audited.

policy_name -Unique name for the policy.

[audit_condition] -If the condition, indicated here, evaluates to true or null(both of them are default) audit entry will be created. Note that this condition cannot directly use the following functions: USERENV, USER, SYSDATE, UID. It cannot use sequences or subqueries. Also it can not reference LEVEL, PRIOR, or ROWNUM pseudocolumns.

[audit_column] -List of columns, delimited by comma, on which audit option will be identified. The default value is NULL, means that any column.

[handler_schema] -Username, which owns event handler procedure. The default value is NULL, means current schema.

[handler_module] -The procedure name, which handles the event. The default is NULL, means not to use event handler procedure.If the procedure is in package, then you should indicate the whole name, for example, DBMSOBJG.GET_TAB_SPACE.

[enable] -Values are true or false. Indicates if this policy should be enabled or disabled. The default is TRUE.

[statement_types]-Values are SELECT, INSERT, UPDATE, and DELETE(comma delimited list). Indicates which DML statement should be audited.The default is SELECT.

[audit_trail] -Value DBMS_FGA.DB_EXTENDED(default) indicates that database should record sql text and bind variables also. Other available value is and DBMS_FGA.DB indicates that db should not save sql text and bind variables.

[audit_column_ops]– Value DBMS_FGA.ALL_COLUMNS indicates that all columns listed in AUDIT_COLUMN parameter must be referenced in order to create audit record. Other value is DBMS_FGA.ANY_COLUMNS(default) means that if any column will be referenced audit record will be created.

Note that parameters that are enclosed by ‘[‘ and ‘]’ are optional, others are mandatory.

For example:

–Assume we are connected as sys user.

BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD'
        ,audit_column=>'SALARY, COMMISSION_PCT'
        ,enable=>FALSE
        ,statement_types=>'SELECT,DELETE,INSERT');
END;

Enabling FGA Policy

If the policy is already enabled, enabling it once more will not give you an error. DBMS_FGA.ENABLE_POLICY is a method which enables it.

For example:

BEGIN
   DBMS_FGA.ENABLE_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD');
END;

Disabling FGA Policy

DBMS_FGA.DISABLE_POLICY is a method for disabling policy.

For example:

BEGIN
   DBMS_FGA.DISABLE_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD');
END;

Dropping FGA Policy

DBMS_FGA.DROP_POLICY is a method for dropping policy.

For example:

BEGIN
   DBMS_FGA.DROP_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD');
END;

Identifying FGA Policies in the Database

DBA_AUDIT_POLICIES it a view which shows all FGA policies enabled in your database.

–Query this

SELECT object_schema||'.'||object_name as Audited_Object
       ,policy_column
       ,policy_name
       ,enabled
       ,audit_trail
FROM dba_audit_policies;

–Result

AUDITED_OBJECT | POLICY_COLUMN | ENABLED | AUDIT_TRAIL
-------------------------------------------------------
HR.EMPLOYEES   | SALARY        | YES     |DB+EXTENDED

Viewing FGA logs

DBA_FGA_AUDIT_TRAIL is a view which shows audited logs, that have been written to the database.

–Query this

SELECT db_user
       ,timestamp
       ,userhost
       ,sql_text
FROM dba_fga_audit_trail
WHERE policy_name='EMPTABLE_AUD'

–Result

DB_USER|TIMESTAMP            |USERHOST  |SQL_TEXT
-------------------------------------------------------------
SCOTT  |2/16/2011 10:05:16 AM|ADA0\SCOTT|select * from employees

Filed under DB Tagged with audit, oracle

← Older posts

Newer posts →

DBA Knowledge Base

Configuring Flashback Database

SQL Profile has higher priority than Hint!

Install New Unicode Character Set in Oracle(Locale Builder)

Retrieve Web-Page Content as XML format in Oracle

LOB datatypes in Oracle

Moving Tables with LOB columns to a diff. tablespace

Moving tables and indexes to a different tablespace

Understanding sysobjects table in SQL Server

Oracle 10g RAC Single Node Installation on Solaris 10

Fine-Grained Auditing

Database Administrator Jobs

Recent Posts

Archives

Categories

Meta

Subscribe to Blog via Email