tag:blogger.com,1999:blog-71518726412883207742024-02-20T04:51:04.231-08:00Gustavs Tech BlogGustav Palssonhttp://www.blogger.com/profile/04493737402274767093noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-7151872641288320774.post-14809924993141247842013-07-18T01:47:00.000-07:002013-07-18T01:48:54.567-07:00Filewatcher with oracle and Centos 6 linuxSo, I recently tried to get the builtin filewatcher in Oracle to work with in my system.<br />
It turns out that it is crap.<br />
<br />
That meant that I had to find some other solution.<br />
Luckely someone made a small ruby program that pretty much does the same, only in a much better and more flexible way.<br />
I found it here: <a href="https://github.com/thomasfl/filewatcher">https://github.com/thomasfl/filewatcher</a><br />
<br />
First you need to install ruby if you dont have it already.<br />
If you have oracle installed it might have done strange stuff to your LD_LIBRARY_PATH.<br />
You will see it if yum starts acting up.<br />
Just do an echo $LD_LIBRARY_PATH - then take the part without an oracle dir and do<br />
export LD_LIBRARY_PATH=<new-path.-without-oracle><br />
<br />
I always run a minimal installation, so I didnt have it.<br />
as root:<br />
# <span style="color: #333333; font-family: Consolas, Liberation Mono, Courier, monospace; font-size: x-small;"><span style="line-height: 19px;">yum install ruby </span></span><span style="color: #333333; font-family: Consolas, 'Liberation Mono', Courier, monospace; font-size: x-small; line-height: 19px;">rubygems</span><br />
# <span style="background-color: #f8f8f8; color: #333333; font-family: Consolas, 'Liberation Mono', Courier, monospace; font-size: 13px; line-height: 19px;">gem install filewatcher</span><br />
<br />
So, now you have the filewatcher.<br />
<br />
Now create an accesspoint for the filewatcher to contact the oracle db.<br />
I made a small sh script that looked like this:<br />
#vi /home/oracle/file_changed.sh<br />
<br />
#!/bin/bash<br />
<br />
LogDirectory='/var/tmp/logs'<br />
<br />
DBUSER='xdb'<br />
DBUSERPASSWORD='my_super_secret_password'<br />
DIR=${1}<br />
FILE_NAME=${2##*/}<br />
FILE_PATH=${2}<br />
BYTES=$( stat -c %s $FILE_PATH)<br />
<br />
echo "XDB.FILE_UPDATED(\'${DIR}\',${FILE_NAME},${BYTES});"<br />
<br />
sqlplus -s <<EOF > ${LogDirectory}/query.log ${DBUSER}/${DBUSERPASSWORD}<br />
set linesize 32767<br />
set feedback off<br />
set heading off<br />
EXEC XDB.FILE_UPDATED('${DIR}','${FILE_NAME}',${BYTES});<br />
EOF<br />
<br />
In the Db i have a procedure called FILE_UPDATED, it looks like this:<br />
<br />
CREATE OR REPLACE PROCEDURE XDB.FILE_UPDATED<br />
(<br />
v_dir IN VARCHAR2 <br />
, v_filename IN VARCHAR2 <br />
, n_bytes IN NUMBER<br />
) AS<br />
BEGIN<br />
update XDB.file_watch set staged = 0, filedate = current_timestamp, bytes = n_bytes where dir = v_dir and filename = v_filename;<br />
commit;<br />
END FILE_UPDATED;<br />
<br />
So now we need to hook the filewatcher into the shell script. lets do that:<br />
# filewatcher -l /mnt/network/transfer.receive.dk/enfinity/products/*.xml '/home/oracle/file_changed.sh $FILEDIR $FILEPATH'<br />
<br />
$FILEDIR and $FILEPATH are keyword variables that filewatcher sets when a file is observed to have changed.<br />
<div>
<br />
use -i <time in sec> with filewatcher to poll less often that the default 0.5 seconds.<br />
<br /></div>
errors with the db can be seen in /var/tmp/logs/query.log<br />
<br />
in my case, i dont do anything with the files before 5 minutes after the last change, but when the file is still unstaged, because filewatcher will also observe halfwritten files (eg they are still begin uploaded).<br />
<br />
the database can now poll for new candidates to to stage.Gustav Palssonhttp://www.blogger.com/profile/04493737402274767093noreply@blogger.com0tag:blogger.com,1999:blog-7151872641288320774.post-70647947157405333342012-05-03T01:55:00.002-07:002012-05-03T01:55:12.659-07:00Oracle Virtual Private DatabasePurpose:<br />
create fine grained access control for an Oracle database based on the usernames of an application.<br />
<br />
There are many steps.<br />
First we must set the client_id in application. In this tutorial I am using a partial context in a -net entity framework, so every time the user requests data, he will get the client ID attached. This is important whenever one is using connection pools.<br />
<br />
.net partial class (don't worry if you don't use .net, the important SQL stuff is shown below):<br />
<br />
<br />
using System;<br />
using System.Collections.Generic;<br />
using System.Data.Objects;<br />
using System.Data.EntityClient;<br />
using System.Data;<br />
using System.Linq;<br />
using System.Web;<br />
<br />
using Devart.Data.Oracle;<br />
<br />
namespace MynameSpace.Models<br />
{<br />
public partial class DataContext<br />
{<br />
partial void OnContextCreated()<br />
{<br />
if ( null == this.Connection ) return;<br />
<br />
this.Connection.StateChange += Connection_StateChange;<br />
}<br />
private EntityConnection EntityConnection<br />
{<br />
get { return this.Connection as EntityConnection; }<br />
}<br />
private OracleConnection OracleConnection<br />
{<br />
get { return this.EntityConnection.StoreConnection as OracleConnection; }<br />
}<br />
private void Connection_StateChange( object sender, StateChangeEventArgs e )<br />
{<br />
if ( e.CurrentState != ConnectionState.Open ) return;<br />
<br />
OracleConnection conn = this.OracleConnection;<br />
if ( null == conn ) return;<br />
<br />
//closes connection on DataContext (bug?), and passes closed/broken connection<br />
//conn.ClientId = HttpContext.Current == null ? "Anonymous" : HttpContext.Current.Profile.UserName;<br />
<br />
//working solution<br />
string identity = HttpContext.Current == null ? "Anonymous" : HttpContext.Current.Profile.UserName;<br />
OracleCommand cmd = conn.CreateCommand();<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
cmd.CommandText = "DBMS_SESSION.SET_IDENTIFIER";<br />
cmd.Parameters.Add( new OracleParameter { ParameterName = "client_id", Value = identity } );<br />
cmd.ExecuteNonQuery();<br />
cmd.Dispose();<br />
<br />
return;<br />
}<br />
<br />
protected override void Dispose( bool disposing )<br />
{<br />
if ( null != this.Connection )<br />
this.Connection.StateChange -= Connection_StateChange;<br />
<br />
base.Dispose( disposing );<br />
}<br />
}<br />
}<br />
.<br />
<br />
<br />
Once you have made sure that the clientId is set every time you load something, you can proceed.<br />
<br />
First we create a test user:<br />
<br />
<br />
-- USER SQL<br />
CREATE USER
"VPD_TEST" IDENTIFIED BY test<br />
DEFAULT TABLESPACE "USERS"<br />
TEMPORARY TABLESPACE "TEMP";<br />
<br />
<br />
<br />
-- ROLES<br />
ALTER USER "VPD_TEST" DEFAULT ROLE "RESOURCE","CONNECT";<br />
<br />
-- SYSTEM PRIVILEGES<br />
<br />
-- QUOTAS<br />
<div>
<br /></div>
<br />
Then we login as the user and create 4 tables.<br />
<br />
We have 6 users with different access right.<br />
<br />
<br />
<br />
--------------------------------------------------------<br />
-- DDL for Table DEPARTMENT<br />
--------------------------------------------------------<br />
<br />
CREATE TABLE "VPD_TEST"."DEPARTMENT"<br />
(<span class="Apple-tab-span" style="white-space: pre;"> </span>"DEP_ID" NUMBER(*,0),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>"NAME" VARCHAR2(30 BYTE)<br />
) SEGMENT CREATION IMMEDIATE<br />
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255<br />
NOCOMPRESS LOGGING<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ;<br />
--------------------------------------------------------<br />
-- DDL for Table DEPARTMENT_SECRETS<br />
--------------------------------------------------------<br />
<br />
CREATE TABLE "VPD_TEST"."DEPARTMENT_SECRETS"<br />
(<span class="Apple-tab-span" style="white-space: pre;"> </span>"DEP_ID" NUMBER(*,0),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>"SECRET" VARCHAR2(30 BYTE),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>"ID" NUMBER(9,0)<br />
) SEGMENT CREATION IMMEDIATE<br />
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255<br />
NOCOMPRESS LOGGING<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ;<br />
--------------------------------------------------------<br />
-- DDL for Table USER_DEP<br />
--------------------------------------------------------<br />
<br />
CREATE TABLE "VPD_TEST"."USER_DEP"<br />
(<span class="Apple-tab-span" style="white-space: pre;"> </span>"ID" NUMBER(9,0),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>"USER_ID" NUMBER(9,0),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>"DEP_ID" NUMBER(9,0),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>"SEL" NUMBER(1,0),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>"UPD" NUMBER(1,0),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>"DEL" NUMBER(1,0)<br />
) SEGMENT CREATION IMMEDIATE<br />
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255<br />
NOCOMPRESS LOGGING<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ;<br />
--------------------------------------------------------<br />
-- DDL for Table USERS<br />
--------------------------------------------------------<br />
<br />
CREATE TABLE "VPD_TEST"."USERS"<br />
(<span class="Apple-tab-span" style="white-space: pre;"> </span>"ID" NUMBER(9,0),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>"USERNAME" VARCHAR2(50 BYTE),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>"TITLE" VARCHAR2(50 BYTE)<br />
) SEGMENT CREATION IMMEDIATE<br />
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255<br />
NOCOMPRESS LOGGING<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ;<br />
REM INSERTING into VPD_TEST.DEPARTMENT<br />
Insert into VPD_TEST.DEPARTMENT (DEP_ID,NAME) values (1,'Research and Development');<br />
Insert into VPD_TEST.DEPARTMENT (DEP_ID,NAME) values (2,'Sales');<br />
Insert into VPD_TEST.DEPARTMENT (DEP_ID,NAME) values (3,'Human Resources');<br />
REM INSERTING into VPD_TEST.DEPARTMENT_SECRETS<br />
Insert into VPD_TEST.DEPARTMENT_SECRETS (DEP_ID,SECRET,ID) values (1,'R+D Secret #3',1);<br />
Insert into VPD_TEST.DEPARTMENT_SECRETS (DEP_ID,SECRET,ID) values (1,'R+D Secret #2',2);<br />
Insert into VPD_TEST.DEPARTMENT_SECRETS (DEP_ID,SECRET,ID) values (2,'Sales Secret #4',3);<br />
Insert into VPD_TEST.DEPARTMENT_SECRETS (DEP_ID,SECRET,ID) values (2,'Sales Secret #2',4);<br />
REM INSERTING into VPD_TEST.USER_DEP<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (1,1,1,1,1,1);<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (2,1,2,1,0,0);<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (3,2,2,1,1,1);<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (4,3,3,1,1,1);<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (5,3,2,1,0,0);<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (6,4,1,1,1,1);<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (7,4,2,1,1,1);<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (8,4,3,1,1,1);<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (9,5,1,1,0,0);<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (10,5,2,1,0,0);<br />
Insert into VPD_TEST.USER_DEP (ID,USER_ID,DEP_ID,SEL,UPD,DEL) values (11,5,3,1,0,0);<br />
REM INSERTING into VPD_TEST.USERS<br />
Insert into VPD_TEST.USERS (ID,USERNAME,TITLE) values (1,'Gustav','Head of R&D');<br />
Insert into VPD_TEST.USERS (ID,USERNAME,TITLE) values (2,'Peter','Head of Sales');<br />
Insert into VPD_TEST.USERS (ID,USERNAME,TITLE) values (3,'Glenn','Head of HR');<br />
Insert into VPD_TEST.USERS (ID,USERNAME,TITLE) values (4,'Jesper','CEO');<br />
Insert into VPD_TEST.USERS (ID,USERNAME,TITLE) values (5,'Casper','Clerk');<br />
Insert into VPD_TEST.USERS (ID,USERNAME,TITLE) values (6,'Guest','Guest');<br />
--------------------------------------------------------<br />
-- DDL for Index SYS_C0018035<br />
--------------------------------------------------------<br />
<br />
CREATE UNIQUE INDEX "VPD_TEST"."SYS_C0018035" ON "VPD_TEST"."DEPARTMENT" ("DEP_ID")<br />
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ;<br />
--------------------------------------------------------<br />
-- DDL for Index DEPARTMENT_SECRETS_PK<br />
--------------------------------------------------------<br />
<br />
CREATE UNIQUE INDEX "VPD_TEST"."DEPARTMENT_SECRETS_PK" ON "VPD_TEST"."DEPARTMENT_SECRETS" ("ID")<br />
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ;<br />
--------------------------------------------------------<br />
-- DDL for Index USER_DEP_PK<br />
--------------------------------------------------------<br />
<br />
CREATE UNIQUE INDEX "VPD_TEST"."USER_DEP_PK" ON "VPD_TEST"."USER_DEP" ("ID")<br />
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ;<br />
--------------------------------------------------------<br />
-- DDL for Index USER_DEP_UK1<br />
--------------------------------------------------------<br />
<br />
CREATE UNIQUE INDEX "VPD_TEST"."USER_DEP_UK1" ON "VPD_TEST"."USER_DEP" ("USER_ID", "DEP_ID")<br />
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ;<br />
--------------------------------------------------------<br />
-- DDL for Index USERS_PK<br />
--------------------------------------------------------<br />
<br />
CREATE UNIQUE INDEX "VPD_TEST"."USERS_PK" ON "VPD_TEST"."USERS" ("ID")<br />
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ;<br />
--------------------------------------------------------<br />
-- DDL for Index USERS_INDEX1<br />
--------------------------------------------------------<br />
<br />
CREATE INDEX "VPD_TEST"."USERS_INDEX1" ON "VPD_TEST"."USERS" (UPPER("USERNAME"))<br />
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ;<br />
--------------------------------------------------------<br />
-- Constraints for Table DEPARTMENT<br />
--------------------------------------------------------<br />
<br />
ALTER TABLE "VPD_TEST"."DEPARTMENT" ADD PRIMARY KEY ("DEP_ID")<br />
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ENABLE;<br />
--------------------------------------------------------<br />
-- Constraints for Table DEPARTMENT_SECRETS<br />
--------------------------------------------------------<br />
<br />
ALTER TABLE "VPD_TEST"."DEPARTMENT_SECRETS" ADD CONSTRAINT "DEPARTMENT_SECRETS_PK" PRIMARY KEY ("ID")<br />
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ENABLE;<br />
ALTER TABLE "VPD_TEST"."DEPARTMENT_SECRETS" MODIFY ("ID" NOT NULL ENABLE);<br />
--------------------------------------------------------<br />
-- Constraints for Table USER_DEP<br />
--------------------------------------------------------<br />
<br />
ALTER TABLE "VPD_TEST"."USER_DEP" ADD CONSTRAINT "USER_DEP_UK1" UNIQUE ("USER_ID", "DEP_ID")<br />
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ENABLE;<br />
ALTER TABLE "VPD_TEST"."USER_DEP" ADD CONSTRAINT "USER_DEP_PK" PRIMARY KEY ("ID")<br />
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ENABLE;<br />
ALTER TABLE "VPD_TEST"."USER_DEP" MODIFY ("DEL" NOT NULL ENABLE);<br />
ALTER TABLE "VPD_TEST"."USER_DEP" MODIFY ("UPD" NOT NULL ENABLE);<br />
ALTER TABLE "VPD_TEST"."USER_DEP" MODIFY ("SEL" NOT NULL ENABLE);<br />
ALTER TABLE "VPD_TEST"."USER_DEP" MODIFY ("DEP_ID" NOT NULL ENABLE);<br />
ALTER TABLE "VPD_TEST"."USER_DEP" MODIFY ("USER_ID" NOT NULL ENABLE);<br />
ALTER TABLE "VPD_TEST"."USER_DEP" MODIFY ("ID" NOT NULL ENABLE);<br />
--------------------------------------------------------<br />
-- Constraints for Table USERS<br />
--------------------------------------------------------<br />
<br />
ALTER TABLE "VPD_TEST"."USERS" ADD CONSTRAINT "USERS_PK" PRIMARY KEY ("ID")<br />
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS<br />
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645<br />
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1<br />
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)<br />
TABLESPACE "USERS" ENABLE;<br />
ALTER TABLE "VPD_TEST"."USERS" MODIFY ("USERNAME" NOT NULL ENABLE);<br />
ALTER TABLE "VPD_TEST"."USERS" MODIFY ("ID" NOT NULL ENABLE);<br />
--------------------------------------------------------<br />
-- Ref Constraints for Table DEPARTMENT_SECRETS<br />
--------------------------------------------------------<br />
<br />
ALTER TABLE "VPD_TEST"."DEPARTMENT_SECRETS" ADD FOREIGN KEY ("DEP_ID")<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> REFERENCES "VPD_TEST"."DEPARTMENT" ("DEP_ID") ENABLE;<br />
--------------------------------------------------------<br />
-- Ref Constraints for Table USER_DEP<br />
--------------------------------------------------------<br />
<br />
ALTER TABLE "VPD_TEST"."USER_DEP" ADD CONSTRAINT "USER_DEP_DEPARTMENT_FK1" FOREIGN KEY ("DEP_ID")<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> REFERENCES "VPD_TEST"."DEPARTMENT" ("DEP_ID") ENABLE;<br />
ALTER TABLE "VPD_TEST"."USER_DEP" ADD CONSTRAINT "USER_DEP_USERS_FK1" FOREIGN KEY ("USER_ID")<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> REFERENCES "VPD_TEST"."USERS" ("ID") ENABLE;<br />
--------------------------------------------------------<br />
-- DDL for Trigger USER_DEP_TRG<br />
--------------------------------------------------------<br />
<br />
CREATE OR REPLACE TRIGGER "VPD_TEST"."USER_DEP_TRG" BEFORE INSERT ON USER_DEP<br />
FOR EACH ROW<br />
BEGIN<br />
<<COLUMN_SEQUENCES>><br />
BEGIN<br />
IF :NEW.ID IS NULL THEN<br />
SELECT USER_DEP_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;<br />
END IF;<br />
END COLUMN_SEQUENCES;<br />
END;<br />
/<br />
ALTER TRIGGER "VPD_TEST"."USER_DEP_TRG" ENABLE;<br />
--------------------------------------------------------<br />
-- DDL for Trigger USERS_TRG<br />
--------------------------------------------------------<br />
<br />
CREATE OR REPLACE TRIGGER "VPD_TEST"."USERS_TRG" BEFORE INSERT ON USERS<br />
FOR EACH ROW<br />
BEGIN<br />
<<COLUMN_SEQUENCES>><br />
BEGIN<br />
IF :NEW.ID IS NULL THEN<br />
SELECT USERS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;<br />
END IF;<br />
END COLUMN_SEQUENCES;<br />
END;<br />
/<br />
ALTER TRIGGER "VPD_TEST"."USERS_TRG" ENABLE;<br />
<br />
<br />
But how do we enforce these access rights? We don't want to have to remember to enforce these rights every time we access the tables.<br />
This is where VPD comes into play.<br />
<br />
First log in as sys and grant execute to a package:<br />
grant execute on dbms_rls to vpd_test;<br />
<br />
Next get a proper delimited access right list, so we need to create a function for that. Thanks to Tom Kyte for this.<br />
<br />
<br />
create or replace type string_agg_type as object<br />
(<br />
total varchar2(4000),<br />
<br />
static function<br />
ODCIAggregateInitialize(sctx IN OUT string_agg_type )<br />
return number,<br />
<br />
member function<br />
ODCIAggregateIterate(self IN OUT string_agg_type ,<br />
value IN varchar2 )<br />
return number,<br />
<br />
member function<br />
ODCIAggregateTerminate(self IN string_agg_type,<br />
returnValue OUT varchar2,<br />
flags IN number)<br />
return number,<br />
<br />
member function<br />
ODCIAggregateMerge(self IN OUT string_agg_type,<br />
ctx2 IN string_agg_type)<br />
return number<br />
);<br />
<br />
<br />
<br />
<br />
create or replace type body string_agg_type<br />
is<br />
<br />
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)<br />
return number<br />
is<br />
begin<br />
sctx := string_agg_type( null );<br />
return ODCIConst.Success;<br />
end;<br />
<br />
member function ODCIAggregateIterate(self IN OUT string_agg_type,<br />
value IN varchar2 )<br />
return number<br />
is<br />
begin<br />
self.total := self.total || ',' || value;<br />
return ODCIConst.Success;<br />
end;<br />
<br />
member function ODCIAggregateTerminate(self IN string_agg_type,<br />
returnValue OUT varchar2,<br />
flags IN number)<br />
return number<br />
is<br />
begin<br />
returnValue := ltrim(self.total,',');<br />
return ODCIConst.Success;<br />
end;<br />
<br />
member function ODCIAggregateMerge(self IN OUT string_agg_type,<br />
ctx2 IN string_agg_type)<br />
return number<br />
is<br />
begin<br />
self.total := self.total || ctx2.total;<br />
return ODCIConst.Success;<br />
end;<br />
<br />
<br />
end;<br />
<br />
<br />
CREATE or replace<br />
FUNCTION stragg(input varchar2 )<br />
RETURN varchar2<br />
PARALLEL_ENABLE AGGREGATE USING string_agg_type;<br />
<br />
<br />
Ok...now we need to create a package comtaining the predicate, that we need to create a filter.<br />
<br />
create or replace<br />
package pck_vpd<br />
as<br />
<br />
function select_predicate (obj_schema varchar2, obj_name varchar2) return varchar2;<br />
<br />
function update_predicate (obj_schema varchar2, obj_name varchar2) return varchar2;<br />
<br />
function delete_predicate (obj_schema varchar2, obj_name varchar2) return varchar2;<br />
<br />
procedure set_user( v_username varchar2 );<br />
<br />
function get_user return varchar2;<br />
<br />
end pck_vpd;<br />
<br />
<br />
create or replace<br />
package body pck_vpd as<br />
<br />
function get_user return varchar2 is<br />
v_userid varchar(255);<br />
begin<br />
SELECT upper(sys_context('USERENV', 'CLIENT_IDENTIFIER') ) into v_userid FROM DUAL;<br />
return v_userid;<br />
end get_user;<br />
<br />
function select_predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is<br />
v_select_dep_id varchar2(255);<br />
v_userid varchar(255);<br />
begin<br />
<br />
select pck_vpd.get_user into v_userid from dual;<br />
<br />
if (v_userid is null)<br />
then return null;<br />
end if;<br />
<br />
select stragg(ud.dep_id) into v_select_dep_id<br />
from user_dep ud<br />
join users u on (u.id = ud.user_id)<br />
where upper(username) = v_userid<br />
and ud.sel = 1;<br />
return 'dep_id in (' || v_select_dep_id || ')';<br />
end select_predicate;<br />
<br />
<br />
<br />
function update_predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is<br />
v_update_dep_id varchar2(255);<br />
v_userid varchar(255);<br />
begin<br />
<br />
select pck_vpd.get_user into v_userid from dual;<br />
<br />
if (v_userid is null)<br />
then return null;<br />
end if;<br />
<br />
select stragg(ud.dep_id) into v_update_dep_id<br />
from user_dep ud<br />
join users u on (u.id = ud.user_id)<br />
where upper(username) = v_userid<br />
and ud.upd = 1;<br />
return 'dep_id in (' || v_update_dep_id || ')';<br />
end update_predicate;<br />
<br />
<br />
<br />
function delete_predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is<br />
v_delete_dep_id varchar2(255);<br />
v_userid varchar(255);<br />
begin<br />
<br />
select pck_vpd.get_user into v_userid from dual;<br />
<br />
if (v_userid is null)<br />
then return null;<br />
end if;<br />
<br />
select stragg(ud.dep_id) into v_delete_dep_id<br />
from user_dep ud<br />
join users u on (u.id = ud.user_id)<br />
where upper(username) = v_userid<br />
and ud.del = 1;<br />
return 'dep_id in (' || v_delete_dep_id || ')';<br />
end delete_predicate;<br />
<br />
procedure set_user( v_username varchar2 ) is<br />
v_dep_id number;<br />
begin<br />
dbms_session.set_identifier(v_username); <br />
end set_user;<br />
<br />
end pck_vpd;<br />
<br />
<br />
This package will return the proper predicate if the user is set and valid, and give access to anything, if no user is set. You can easily change it to NO access if the user is not set, by changing the returning string to something like this:<br />
<br />
<br />
if (v_userid is null)<br />
then return '1=0';<br />
end if;<br />
<br />
This will prevent the user from getting any rows (I havn't tested it, but it should work).<br />
<br />
<br />
<br />
Next, we need to create policies.<br />
execute the following:<br />
<br />
begin<br />
dbms_rls.add_policy(object_schema => 'vpd_test',<br />
object_name => 'department_secrets',<br />
policy_name => 'vpd_test_select',<br />
function_schema =>'vpd_test',<br />
policy_function => 'pck_vpd.select_predicate',<br />
statement_types =>'select',<br />
policy_type => dbms_rls.CONTEXT_SENSITIVE);<br />
end;<br />
<br />
begin<br />
dbms_rls.add_policy(object_schema => 'vpd_test',<br />
object_name => 'department_secrets',<br />
policy_name => 'vpd_test_update',<br />
function_schema =>'vpd_test',<br />
policy_function => 'pck_vpd.update_predicate',<br />
statement_types =>'update',<br />
policy_type => dbms_rls.CONTEXT_SENSITIVE);<br />
end;<br />
<br />
begin<br />
dbms_rls.add_policy(object_schema => 'vpd_test',<br />
object_name => 'department_secrets',<br />
policy_name => 'vpd_test_delete',<br />
function_schema =>'vpd_test',<br />
policy_function => 'pck_vpd.delete_predicate',<br />
statement_types =>'delete',<br />
policy_type => dbms_rls.CONTEXT_SENSITIVE);<br />
end;<br />
<br />
Test it out!<br />
<br />
Execute the following:<br />
<br />
<br />
<br />
begin<br />
pck_vpd.set_user('gustav');<br />
end;<br />
<br />
select * from department_secrets;<br />
/4 rows returned.<br />
update department_secrets set secret = 'Sales Secret #4' where id = 3;<br />
//see...no rows updated, its because gustav doesn't have update rights for that table row.<br />
<br />
<br />
begin<br />
pck_vpd.set_user('jesper');<br />
end;<br />
<br />
select * from department_secrets;
<br />
/6 rows returned<br />
update department_secrets set secret = 'Sales Secret #4' where id = 3;
<br />
// 1 row updated.<br />
<br />
<br />
<br />
Now, use your new knowledge to implement VPD into your own system!<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div>
<br /></div>
<br />
<div>
<br /></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />Gustav Palssonhttp://www.blogger.com/profile/04493737402274767093noreply@blogger.com0tag:blogger.com,1999:blog-7151872641288320774.post-58520409314391253962012-04-11T07:32:00.000-07:002013-07-18T01:21:39.191-07:00Update Oracle from 11.2.0.1 to 11.2.0.3<span style="font-family: inherit;">First you need to download the software. Do this via Oracle support (formerly metalink).</span><br />
<a href="https://support.oracle.com/CSP/ui/flash.html#"><span style="font-family: inherit;">https://support.oracle.com/CSP/ui/flash.html</span></a><br />
<span style="font-family: inherit;">search for patch ID 10404530</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">or go directly to</span><br />
<span style="font-family: inherit;"><a href="https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=10404530">https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=10404530</a>
</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">You need a valid support license to download it.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">A rather long installation guide is located at</span><br />
<span style="font-family: inherit;"><a href="http://www.oracle.com/pls/db112/portal.portal_db?selected=11&frame=">http://www.oracle.com/pls/db112/portal.portal_db?selected=11&frame=</a>
</span><br />
<span style="font-family: inherit;"><br /></span>
<div style="text-align: left;">
<span style="font-family: inherit;">If you don't want to read it all, start the installer (remember to do almost all the things you did before installing 11.2.0.1 - do the appropriate preinstalliation steps) Guide: </span></div>
<div style="text-align: left;">
<a href="http://g-tech-blog.blogspot.dk/2012/03/centos-62-and-oracle-11r2.html">http://g-tech-blog.blogspot.dk/2012/03/centos-62-and-oracle-11r2.html</a></div>
<div style="text-align: left;">
<span style="font-family: inherit;">Once you have an Xming window running with the installation, choose to UPGRADE the database.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">Install the new version to a NEW home.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">When you are done, remember to run the root script as usual after installing oracle, and let the installer guide you though the process of upgrading your DB.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">It will also update Enterprise Manager for you.</span></div>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Post installation steps:</span><br />
<span style="font-family: inherit;">rename $ORACLE_HOME to the NEW destination.</span><br />
<span style="font-family: inherit;">The important files are:</span><br />
<span style="font-family: inherit;">/etc/init.d/oracle</span><br />
<span style="font-family: inherit;">/home/oracle/.bash_profile</span><br />
<span style="font-family: inherit;">/etc/profile</span><br />
<span style="font-family: inherit;">If you for some reason after a reboot can't start the EM or DB, look for an entry with the wrong $ORACLE_HOME destination.</span><br />
<span style="font-family: inherit;">If you want the DB to start "automagically" when booting (using the dbstart command) remember to change</span><br />
<b><span style="font-family: inherit;">/etc/oratab</span></b><br />
<span style="font-family: inherit;">change the :N to :Y</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">When you have successfully installed oracle end EM, its time for the critical patch, updated jan 2012.</span><br />
<span style="font-family: inherit;">Download OPach utility first.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">goto https://support.oracle.com<br />search for 6880880 or try to go directly to: <a href="https://updates.oracle.com/download/6880880.html">6880880</a> .</span><br />
<span style="font-family: inherit;">download and follow readme (put it in the $ORACLE_HOME dir).</span><br />
<span style="font-family: inherit;"><br />Download the patch:<br />again, on oracle support, search for 13466801.<br />The file is called something like: p13466801_112030_<OS>-<Architecture>.zip<br />run the opatch util from the unzipped dir of the patch.<br />Follow the instractions.<br />Done! :-)</span>Gustav Palssonhttp://www.blogger.com/profile/04493737402274767093noreply@blogger.com2tag:blogger.com,1999:blog-7151872641288320774.post-58501612163385176442012-03-23T07:24:00.001-07:002012-03-23T07:24:54.360-07:00linux abbreviations<span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/bin: where binary programs live.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/boot: the special programs that start the system when you turn the machine on.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/dev: aaaaaalll the "device" listings for hardware of every concievable flavor, along with some "imaginary" devices, </span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">lile /dev/null and /dev/zero. When you get a random number it usually comes from /dev/random.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/etc: configuration files.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/home: the user's directories live here.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/lib: the library files that programs need to run, if they are compiled to link against these libraries. </span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/lost+found: look here after a system crash/kernel panic or other extreme scenario to find "orphaned" files.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/misc: kinda reminds you of /etc, doesn't it? Every time I see this folder on a system, it's empty, but I ascertain that </span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">it stands for "miscellaneous", and I think it may be a place for users to keep their own "/etc" type files?</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/mnt: "mount", the directory that acts as a gateway to every other disk or file system. Floppies are accessed from </span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/mnt/floppy, your second hard drive might be /mnt/hd here, and so on.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/opt: "optional", where programs that are installed after the initial system install sometimes end up.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/proc: running "processes". The "everything is a file" paradigm carried to the Linux degree! This folder hosts files</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">which keep tabs on boring stuff like what daemons are running and the process ID of Emacs while you have</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">Emacs open.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/root: root's home!</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/sbin: the "system-administrator's bin file", which hosts programs that would be in /bin if they didn't have "root-only" </span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">access permissions.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/tmp: "temporary" files, of use by programs which need to throw together a data file on-the-fly in preperation for</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">executing some other task. This folder gets flushed on reboot.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/usr: "user". Practically a whole other system in here, /usr/bin is still more binaries, /usr/doc and /usr/share/doc are</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">documentation folders, /usr/share/games/fortune is where the funny quotes come from when you log on (if</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">that feature is enabled). The idea is that this should be where files/programs accessible by all users should</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">be, and thus your desktop backgrounds come from /usr/share/wallpapers, or such. The other case is newly</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">installed programs sometimes end up in /usr/local/bin.</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">/var: "variable". System logs in here, recording such things as the last foo commands typed at the prompt, start-up</span><br style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;" /><span style="background-color: #e6e6e6; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">boot messages, and such.</span>Gustav Palssonhttp://www.blogger.com/profile/04493737402274767093noreply@blogger.com0tag:blogger.com,1999:blog-7151872641288320774.post-8256026374268390452012-03-08T03:19:00.013-08:002012-04-11T07:09:52.404-07:00XML-DB and Oracle 11gR2<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"><span style="font-size: 100%;">Importing large quantities of XML-data into a RDBMS can be a tricky </span>endeavour. I will write a post about how I solved our need for a importing XML into our db, and merge it with the rest of the data in a daily schedule.</span><br />
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
This is how I did it.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span style="font-size: 100%;"> </span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span style="font-size: 100%;">First we need to unlock the XDB user.</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
With a dba account unlock the user and set a password.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Linux:</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Mounting with fstab (ip and share_folder should be replaced with whatever you need, likewhise with the folder we are mounting to):</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br />
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#Mounting NFS share</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
ip:/share_folder /oracle/data/oradata/xml_db/xml/enfinity/ nfs rsize=8192,wsize=8192,noexec,nosuid 0 0</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
ip:/share_folder /oracle/data/oradata/xml_db/xml/omniture nfs rsize=8192,wsize=8192,noexec,nosuid 0 0</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Sqlplus:</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
As sys user, create the directory in the database.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
something like:</div>
<div>
>create or replace directory ENFINITY_PRODUCTS as '/oracle/data/oradata/xml_db/xml/enfinity/products';</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Now grant access to the directory to the XDB user.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
>grant READ, WRITE on directory "ENFINITY_PRODUCTS" to XDB ;</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
I created a table so that i can see what file belongs to what.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br />
<div>
--------------------------------------------------------</div>
<div>
-- DDL for Table SITE_XML_PATH</div>
<div>
--------------------------------------------------------</div>
<div>
CREATE TABLE "XDB"."SITE_XML_PATH" </div>
<div>
(<span class="Apple-tab-span" style="white-space: pre;"> </span>"ID" NUMBER(9,0), </div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>"SITE_ID" NUMBER(9,0), </div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>"XML_PATH" VARCHAR2(255 BYTE), </div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>"XML_FILE_NAME" VARCHAR2(255 BYTE), </div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>"CATALOG_NAME" VARCHAR2(255 BYTE)</div>
<div>
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING</div>
<div>
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645</div>
<div>
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)</div>
<div>
TABLESPACE "PS4" ;</div>
<div>
--------------------------------------------------------</div>
<div>
-- DDL for Index SITE_XML_PATH_PK</div>
<div>
--------------------------------------------------------</div>
<div>
CREATE UNIQUE INDEX "XDB"."SITE_XML_PATH_PK" ON "XDB"."SITE_XML_PATH" ("ID") </div>
<div>
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS </div>
<div>
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645</div>
<div>
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)</div>
<div>
TABLESPACE "PS4" ;</div>
<div>
--------------------------------------------------------</div>
<div>
-- DDL for Index SITE_XML_PATH_UK1</div>
<div>
--------------------------------------------------------</div>
<div>
CREATE UNIQUE INDEX "XDB"."SITE_XML_PATH_UK1" ON "XDB"."SITE_XML_PATH" ("SITE_ID") </div>
<div>
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS </div>
<div>
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645</div>
<div>
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)</div>
<div>
TABLESPACE "PS4" ;</div>
<div>
--------------------------------------------------------</div>
<div>
-- DDL for Index SITE_XML_PATH_UK2</div>
<div>
--------------------------------------------------------</div>
<div>
CREATE UNIQUE INDEX "XDB"."SITE_XML_PATH_UK2" ON "XDB"."SITE_XML_PATH" ("XML_PATH") </div>
<div>
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS </div>
<div>
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645</div>
<div>
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)</div>
<div>
TABLESPACE "PS4" ;</div>
<div>
--------------------------------------------------------</div>
<div>
-- Constraints for Table SITE_XML_PATH</div>
<div>
--------------------------------------------------------</div>
<div>
ALTER TABLE "XDB"."SITE_XML_PATH" MODIFY ("CATALOG_NAME" NOT NULL ENABLE);</div>
<div>
ALTER TABLE "XDB"."SITE_XML_PATH" MODIFY ("XML_FILE_NAME" NOT NULL ENABLE);</div>
<div>
ALTER TABLE "XDB"."SITE_XML_PATH" ADD CONSTRAINT "SITE_XML_PATH_UK2" UNIQUE ("XML_PATH")</div>
<div>
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS </div>
<div>
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645</div>
<div>
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)</div>
<div>
TABLESPACE "PS4" ENABLE;</div>
<div>
ALTER TABLE "XDB"."SITE_XML_PATH" ADD CONSTRAINT "SITE_XML_PATH_UK1" UNIQUE ("SITE_ID")</div>
<div>
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS </div>
<div>
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645</div>
<div>
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)</div>
<div>
TABLESPACE "PS4" ENABLE;</div>
<div>
ALTER TABLE "XDB"."SITE_XML_PATH" ADD CONSTRAINT "SITE_XML_PATH_PK" PRIMARY KEY ("ID")</div>
<div>
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS </div>
<div>
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645</div>
<div>
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)</div>
<div>
TABLESPACE "PS4" ENABLE;</div>
<div>
ALTER TABLE "XDB"."SITE_XML_PATH" MODIFY ("XML_PATH" NOT NULL ENABLE);</div>
<div>
ALTER TABLE "XDB"."SITE_XML_PATH" MODIFY ("SITE_ID" NOT NULL ENABLE);</div>
<div>
ALTER TABLE "XDB"."SITE_XML_PATH" MODIFY ("ID" NOT NULL ENABLE);</div>
<div>
<br /></div>
<div>
<div>
<br />
--------------------------------------------------------</div>
<div>
-- DDL for Sequence SITE_XML_PATH_SEQ</div>
<div>
--------------------------------------------------------</div>
<div>
CREATE SEQUENCE "XDB"."SITE_XML_PATH_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER NOCYCLE ;</div>
</div>
<div>
<br /></div>
<div>
--------------------------------------------------------</div>
<div>
-- DDL for Trigger SITE_XML_PATH_TRG</div>
<div>
--------------------------------------------------------</div>
<div>
CREATE OR REPLACE TRIGGER "XDB"."SITE_XML_PATH_TRG" BEFORE INSERT ON XDB.SITE_XML_PATH </div>
<div>
FOR EACH ROW </div>
<div>
BEGIN</div>
<div>
<<column_sequences>></column_sequences></div>
<div>
BEGIN</div>
<div>
IF :NEW.ID IS NULL THEN</div>
<div>
SELECT SITE_XML_PATH_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;</div>
<div>
END IF;</div>
<div>
END COLUMN_SEQUENCES;</div>
<div>
END;</div>
<div>
/</div>
<div>
ALTER TRIGGER "XDB"."SITE_XML_PATH_TRG" ENABLE;</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Create a package containing something like this:</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<div>
create or replace</div>
<div>
PACKAGE BODY XML_DB AS</div>
<div>
<br /></div>
<div>
PROCEDURE refresh_xml_files AS</div>
<div>
CURSOR c_files IS SELECT xml_path, xml_file_name, catalog_name FROM site_xml_path;</div>
<div>
v_filepath varchar2(255);</div>
<div>
BEGIN</div>
<div>
LOCK TABLE site_xml_path IN EXCLUSIVE MODE;</div>
<div>
FOR r_file IN c_files</div>
<div>
LOOP</div>
<div>
v_filepath := r_file.xml_path;</div>
<div>
refresh_xml_file(r_file.xml_path, r_file.xml_file_name, r_file.catalog_name);</div>
<div>
END LOOP;</div>
<div>
COMMIT;</div>
<div>
EXCEPTION</div>
<div>
WHEN OTHERS THEN </div>
<div>
rollback;</div>
<div>
raise_application_error(-20001, 'Could not refresh XML. '||'Filepath: '||v_filepath||chr(10)||SQLERRM||chr(10)||dbms_utility.format_error_backtrace);</div>
<div>
END refresh_xml_files;</div>
<div>
<br /></div>
<div>
PROCEDURE refresh_xml_file(v_xml_path in varchar2, v_xml_file_name in varchar2, v_catalog_name in varchar2) AS</div>
<div>
BEGIN</div>
<div>
delete_xml_resource(v_xml_path, v_xml_file_name, v_catalog_name);</div>
<div>
add_xml_resource(v_xml_path, v_xml_file_name, v_catalog_name);</div>
<div>
EXCEPTION</div>
<div>
WHEN OTHERS THEN </div>
<div>
rollback;</div>
<div>
raise_application_error(-20001, 'Could not refresh XML. '||'Filepath: '||v_xml_path||chr(10)||SQLERRM||chr(10)||dbms_utility.format_error_backtrace);</div>
<div>
END refresh_xml_file;</div>
<div>
<br /></div>
<div>
PROCEDURE delete_xml_resource(v_xml_path in varchar2, v_xml_file_name in varchar2, v_catalog_name in varchar2) AS</div>
<div>
BEGIN</div>
<div>
DBMS_XDB.DELETERESOURCE(</div>
<div>
abspath => v_xml_path</div>
<div>
);</div>
<div>
EXCEPTION</div>
<div>
WHEN OTHERS THEN </div>
<div>
rollback;</div>
<div>
raise_application_error(-20001, 'Could not delete XML resource. '||'Filepath: '||v_xml_path||chr(10)||SQLERRM||chr(10)||dbms_utility.format_error_backtrace);</div>
<div>
END delete_xml_resource;</div>
<div>
<br /></div>
<div>
PROCEDURE add_xml_resource(v_xml_path in varchar2, v_xml_file_name in varchar2, v_catalog_name in varchar2) AS</div>
<div>
success boolean;</div>
<div>
BEGIN</div>
<div>
success := DBMS_XDB.CREATERESOURCE(</div>
<div>
abspath => v_xml_path,</div>
<div>
data => BFILENAME(v_catalog_name, v_xml_file_name)</div>
<div>
); </div>
<div>
EXCEPTION</div>
<div>
WHEN OTHERS THEN </div>
<div>
rollback;</div>
<div>
raise_application_error(-20001, 'Could not add XML resource. '||'Filepath: '||v_xml_path||chr(10)||SQLERRM||chr(10)||dbms_utility.format_error_backtrace);</div>
<div>
END add_xml_resource;</div>
<div>
</div>
<div>
FUNCTION char_to_bool(v_data in varchar2, v_true_val in varchar2, v_false_val in varchar2) return number AS</div>
<div>
BEGIN</div>
<div>
RETURN char_to_bool(v_data, v_true_val, v_false_val, 1);</div>
<div>
END char_to_bool;</div>
<div>
</div>
<div>
FUNCTION char_to_bool(v_data in varchar2, v_true_val in varchar2, v_false_val in varchar2, b_nullable in number) return number AS</div>
<div>
v_retval number;</div>
<div>
invalid_value EXCEPTION;</div>
<div>
BEGIN</div>
<div>
SELECT</div>
<div>
CASE </div>
<div>
WHEN v_data = v_true_val THEN 1</div>
<div>
WHEN v_data = v_false_val THEN 0</div>
<div>
ELSE NULL</div>
<div>
END INTO v_retval</div>
<div>
FROM DUAL;</div>
<div>
IF v_retval IS NULL AND b_nullable != 1</div>
<div>
THEN RAISE invalid_value;</div>
<div>
END IF;</div>
<div>
RETURN v_retval;</div>
<div>
EXCEPTION </div>
<div>
WHEN invalid_value THEN raise_application_error(-20001, 'Invalid value: '||v_data||' data has to be: '||v_true_val|| ' or '||v_false_val);</div>
<div>
WHEN OTHERS THEN RAISE;</div>
<div>
END char_to_bool;</div>
<div>
<br /></div>
<div>
END XML_DB;</div>
</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Use </div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
><a class="x8" href="http://www.blogger.com/blogger.g?blogID=7151872641288320774" id="sqlHistoryUIDataSource:0:22" name="sqlHistoryUIDataSource:0:22" style="background-color: #f7f7e7; font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 13px; text-align: right;" title="SQL">DBMS_XDB.CreateResource(...</a></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
to create the resource. Register it. <br />
Or you can do it with OEM.<br />
<br />
If using OEM:<br />
Once you have a valid XSD schema, upload it via Oracle Enterprise Manager->Scema->XML Schema. Remember to create the table (enable checkbox). Enter the local URL you want to register it to, and put that URL into your XML file.
</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<a class="x8" href="http://www.blogger.com/blogger.g?blogID=7151872641288320774" name="sqlHistoryUIDataSource:0:22" style="background-color: #f7f7e7; font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 13px; text-align: right;" title="SQL"><br /></a></div>
<div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
I can't post my xsd resource here (blogger won't allow it), but I used quite some tie creating it with xmlspy first, but I ended up redoing everything by hand.<br />
<br />
A valid alternative is Trang. get it from <a href="http://www.thaiopensource.com/download/" style="font-size: 100%;">http://www.thaiopensource.com/download/</a><br />
Follow this guide to set it up:<br />
<a href="http://www.dotkam.com/2008/05/28/generate-xsd-from-xml/">http://www.dotkam.com/2008/05/28/generate-xsd-from-xml/</a>
<br />
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Once you have uploaded the file, you can put data into the table (if you chose to create a table from the xsd file).</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
run the </div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span style="font-size: 100%;">BEGIN</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
success := DBMS_XDB.CREATERESOURCE(</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
abspath => v_xml_path,</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
data => BFILENAME(v_catalog_name, v_xml_file_name)</div>
<div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
);</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
END;</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Your data should be in the table now, ready for a query.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
One of my queries to link other data to my XML document is:</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<br />
<span style="font-family: Georgia, serif;">SELECT distinct </span><br />
<span style="font-family: Georgia, serif;"> EXTRACTVALUE(xseq.column_value, '/product/sku') product_identifier</span><br />
<span style="font-family: Georgia, serif;"> FROM products e</span><br />
<span style="font-family: Georgia, serif;"> JOIN RESOURCE_VIEW r ON (extractValue(r.RES, '/Resource/XMLRef') = ref(e))</span><br />
<span style="font-family: Georgia, serif;"> JOIN site_xml_path xp ON (lower(r.any_path) = lower(xp.xml_path))</span><br />
<span style="font-family: Georgia, serif;"> CROSS JOIN TABLE(XMLSEQUENCE(EXTRACT(VALUE(e), '/root/product'))) xseq</span><br />
<span style="font-family: Georgia, serif;"><br /></span><br />
<span style="font-family: Georgia, serif;">The CROSS JOIN is important when you want to have different ID's for each XML file, because you can't control the content.</span><br />
<br />
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
</div>
</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-style: normal; font-variant: normal; font-weight: normal; line-height: normal; text-align: right;">
<span style="font-family: Arial, Helvetica, Geneva, sans-serif; font-size: x-small;"><br /></span></div>
</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
</div>
</div>Gustav Palssonhttp://www.blogger.com/profile/04493737402274767093noreply@blogger.com0tag:blogger.com,1999:blog-7151872641288320774.post-68881805975198457232012-03-06T05:17:00.015-08:002012-06-21T08:03:04.340-07:00CentOs 6.2 and Oracle 11R2 + HugePages Installation step by step<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
CentOs 6.2 minimal install</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Oracle 11gR2 install</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
6GB SGA </div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
2GB PGA</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
9GB locked mem virtual host</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
12GB total available virtual host</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#df -k</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Filesystem <span class="Apple-tab-span" style="white-space: pre;"> </span>1K-blocks <span class="Apple-tab-span" style="white-space: pre;"> </span>Mounted on</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
/dev/mapper/vg_orcl11g-lv_root <span class="Apple-tab-span" style="white-space: pre;"> </span><span style="font-size: 100%;">76383296 <span class="Apple-tab-span" style="white-space: pre;"> </span>/</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
tmpfs <span class="Apple-tab-span" style="white-space: pre;"> </span>6099268 <span class="Apple-tab-span" style="white-space: pre;"> </span>/dev/shm</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
/dev/sda1 <span class="Apple-tab-span" style="white-space: pre;"> </span>495844 <span class="Apple-tab-span" style="white-space: pre;"> </span>/boot</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
/dev/mapper/vg_orcl11g-lv_home<span class="Apple-tab-span" style="white-space: pre;"> </span><span class="Apple-tab-span" style="font-size: 100%; white-space: pre;"> </span><span style="font-size: 100%;">50395844 <span class="Apple-tab-span" style="white-space: pre;"> </span>/home</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
/dev/mapper/vg_orcl11g-lv_ora_app <span class="Apple-tab-span" style="white-space: pre;"> </span><span style="font-size: 100%;">50395844<span class="Apple-tab-span" style="white-space: pre;"> </span>/oracle/app</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
/dev/mapper/vg_orcl11g-lv_ora_backup <span class="Apple-tab-span" style="white-space: pre;"> </span><span style="font-size: 100%;">806346400<span class="Apple-tab-span" style="white-space: pre;"> </span>/oracle/backup</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
/dev/mapper/vg_u02-lv_ora02 <span class="Apple-tab-span" style="white-space: pre;"> </span><span style="font-size: 100%;">103208224<span class="Apple-tab-span" style="white-space: pre;"> </span>/oracle/data</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
/dev/mapper/vg_u03-lv_ora03 <span class="Apple-tab-span" style="white-space: pre;"> </span><span style="font-size: 100%;">51602044<span class="Apple-tab-span" style="white-space: pre;"> </span>/oracle/log</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
/dev/mapper/vg_orcl11g-lv_tmp <span class="Apple-tab-span" style="white-space: pre;"> </span><span style="font-size: 100%;">35277560<span class="Apple-tab-span" style="white-space: pre;"> </span>/tmp</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
/dev/mapper/vg_orcl11g-lv_var <span class="Apple-tab-span" style="white-space: pre;"> </span><span style="font-size: 100%;">25197676 <span class="Apple-tab-span" style="white-space: pre;"> </span>/var</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
1) disable SELinux and firewall and set startup params and update kernel, install vmtools</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#setup</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>remove x in firewall</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>services: remove kdump</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>services add ntpd</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#vi /etc/selinux/config</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>SELINUX=disabled</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#vi /etc/grub.conf</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>remove rhgb from line</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#yum update</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#mount /dev/cdrom /mnt/cdrom/</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#cd /tmp</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#tar -zxf /mnt/cdrom/VMwareTools-8.6.0-515842.tar.gz</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#cd vmware-tools-distrib/</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#yum install gcc</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#./vmware-install.pl</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#vi /etc/hosts</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>#name <span class="Apple-tab-span" style="white-space: pre;"> </span>ip</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>orcl1<span class="Apple-tab-span" style="white-space: pre;"> </span>192.168.103.80</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span> </div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
2) user and groups</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#gruopadd oinstall</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#groupadd dba</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#useradd -g oinstall -G dba oracle</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
3) kernel configuration</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#vi /etc/sysctl.conf</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span># Controls the maximum size of a message, in bytes</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>kernel.msgmnb = 65536</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span># Controls the default maxmimum size of a mesage queue</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>kernel.msgmax = 65536</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span># Controls the maximum shared segment size, in bytes</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>kernel.shmmax = 68719476736</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span># Controls the maximum number of shared memory segments, in pages</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>kernel.shmall = 4294967296</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>kernel.shmmni = 4096</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>#Hugepages 2MB chucks, with oracles hugepages.sh script, calculate how many pages you need.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>vm.nr_hugepages=770</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>#SEM</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>kernel.sem = 250 32000 100 128</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>#file max</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span style="white-space: pre;">fs.file-max = 6815744 </span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span style="white-space: pre;">fs.aio-max-nr = 1048576</span></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>#port ranges</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>net.ipv4.ip_local_port_range = 9000 65500</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>#rmem</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>net.core.rmem_default = 262144</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>net.core.rmem_max = 4194304</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>net.core.wmem_default = 262144</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>net.core.wmem_max = 1048576</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
4) shell limits</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#vi /etc/security/limits.conf </div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>#8gb memlock (1024*8*1000)</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>oracle soft memlock 8192000</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>oracle hard memlock 8192000</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>oracle soft nproc 2047</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>oracle hard nproc 16384</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>oracle soft nofile 1024</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span>oracle hard nofile 65536</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
5) permissions</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#chown -R oracle:oinstall /oracle/</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
6) X11 and paths. You might need other x11 packages. use "#yum search x11". remember to install fonts for x11 </div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#yum install xorg-x11-utils.x86_64 xorg-x11-xauth.x86_64 xorg-x11-xdm.x86_64 xorg-x11-xinit.x86_64 xorg-x11-xinit-session.x86_64 xorg-x11-xkb-extras.x86_64 xorg-x11-xtrans-devel.noarch</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#su - oracle</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
$vi /home/oracle/.bash_profile</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span class="Apple-tab-span" style="white-space: pre;"> </span></div>
<div>
ORACLE_BASE=/oracle/app</div>
<div>
ORACLE_SID=ps4</div>
<div>
SID=$ORACLE_SID</div>
<div>
ORACLE_HOME=$ORACLE_BASE/base/product/11.2.0/dbhome_1</div>
<div>
ORACLE_HOSTNAME=orcl1</div>
<div>
ORACLE_UNQNAME=orcl</div>
<div>
ORACLE_HOME_LISTNER=$ORACLE_HOME/</div>
<div>
ORACLE_TERM=xterm</div>
<div>
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin</div>
<div>
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib</div>
<div>
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib</div>
<div>
<br /></div>
<div>
export PATH ORACLE_BASE ORACLE_SID ORACLE_HOME ORACLE_HOSTNAME ORACLE_UNQNAME ORACLE_HOME_LISTNER ORACLE_TERM LD_LIBRARY_PATH CLASSPATH</div>
<div>
<br /></div>
<div>
if [ $USER = "oracle" ]; then</div>
<div>
if [ $SHELL = "/bin/ksh" ]; then</div>
<div>
ulimit -p 16384</div>
<div>
ulimit -n 65536</div>
<div>
else</div>
<div>
ulimit -u 16384 -n 65536</div>
<div>
fi</div>
<div>
fi</div>
<div>
umask 022</div>
<div>
<br /></div>
<div>
You might also have to set this in /etc/profile</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
7) insert oracle install disk and mount it (mount as root)</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#mount /dev/cdrom /mnt/cdrom/</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
8) create and unzip if dvd contains zipped files. I chose to inflate the zip in oracle home dir.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
$mkdir /home/oracle/oracle11g</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
$unzip -XK linux.x64_11gR2_database_1of2.zip -d /home/oracle/oracle11g/</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
$unzip -XK linux.x64_11gR2_database_2of2.zip -d /home/oracle/oracle11g/</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
9)</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
setup an Xming server and follow instructions for putty on <a href="http://www.straightrunning.com/XmingNotes/">Xming Homepage</a></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
$cd /home/oracle/oracle11g/database/</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
$./runInstaller</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Install Oracle and resolve prereqs with yum search / yum install</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
ignore i386 errors if you have a i686 version. </div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Remember to choose unicode if you need it. It is VERY difficult to change later on.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
I installed </div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<span style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">#</span> yum install libaio-devel.x86_64 libaio-devel.i686 elfutils-libelf-devel.x86_64 compat-libstdc++-33.x86_64 compat-libstdc++-33.i686 glibc.i686 make.x86_64 libaio.i686 gcc-c++.x86_64 libstdc++.i686 unixODBC-devel.i686 unixODBC-devel.x86_64 unixODBC.i686 unixODBC.x86_64 mksh.x86_64 ksh.x86_64</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
Ignore the dbksh error. we installed original ksh, and the mksh. Dunno if mksh is used, but I installed it anyway.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
REMEMBER to disable AMM (Automatic Memory Management) during install if you use hugepages. Don't confuse this with ASMM. ASMM can be enabled.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
POST installation:</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
First we need to create a service that so oracle start with the system and shuts down as well.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
#vi /etc/init.d/oracle</div>
<div>
<div>
#!/bin/bash</div>
<div>
# chkconfig: 2345 99 10</div>
<div>
#</div>
<div>
# /etc/rc.d/init.d/oracle</div>
<div>
# Description: Starts and stops the Oracle database and listeners</div>
<div>
# See how we were called.</div>
<div>
<br /></div>
<div>
ORA_USR=oracle</div>
<div>
ORA_PATH=/oracle/app/base/product/11.2.0/dbhome_1/</div>
<div>
<br /></div>
<div>
case "$1" in</div>
<div>
start)</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
echo -n "Starting Oracle Databases and listeners: "</div>
<div>
su - $ORA_USR -c "$ORA_PATH/bin/dbstart" >> /var/log/oracle</div>
<div>
echo "Done."</div>
<div>
echo -n "Starting Oracle Enterprise Manager Console: "</div>
<div>
su - $ORA_USR -c "$ORA_PATH/bin/emctl start dbconsole" >> /var/log/oracle</div>
<div>
echo "Done."</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
date +"! %T %a %D : Finished." >> /var/log/oracle</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
touch /var/lock/subsys/oracle</div>
<div>
;;</div>
<div>
stop)</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
rm -f /var/lock/subsys/oracle</div>
<div>
echo -n "Shutting Down Oracle Databases and listeners: "</div>
<div>
su - $ORA_USR -c "$ORA_PATH/bin/dbshut" >> /var/log/oracle</div>
<div>
echo "Done."</div>
<div>
echo -n "Stopping Oracle Enterprise Manager Console: "</div>
<div>
su - $ORA_USR -c "$ORA_PATH/bin/emctl stop dbconsole" >> /var/log/oracle</div>
<div>
echo "Done."</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
date +"! %T %a %D : Finished." >> /var/log/oracle</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
;;</div>
<div>
restart)</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
date +"! %T %a %D : Restarting Oracle Databases." >> /var/log/oracle</div>
</div>
<div>
<br />
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
rm -f /var/lock/subsys/oracle</div>
<div>
echo -n "Restarting Oracle Databases... "</div>
<div>
echo -n "Shutting database and listeners down: "</div>
<div>
su - $ORA_USR -c "$ORA_PATH/bin/dbshut" >> /var/log/oracle</div>
<div>
echo "Done"</div>
<div>
echo -n "Starting database and listerners up: "</div>
<div>
su - $ORA_USR -c "$ORA_PATH/bin/dbstart" >> /var/log/oracle</div>
<div>
echo "Done."</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
date +"! %T %a %D : Finished." >> /var/log/oracle</div>
<div>
echo "----------------------------------------------------" >> /var/log/oracle</div>
<div>
touch /var/lock/subsys/oracle</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
echo "" >> /var/log/oracle</div>
<div>
;;</div>
<div>
*)</div>
<div>
echo "Usage: oracle {start|stop|restart}"</div>
<div>
exit 1</div>
<div>
esac</div>
<div>
exit 0</div>
<div>
<br /></div>
<div>
#chkconfig --add oracle</div>
<div>
<br /></div>
<div>
If you like me, have a bug in chkconfig you need to copy start symlinks (S99oracle) to K10oracle for runlevel 2,3,4 and 5.</div>
<div>
<br /></div>
<div>
To do this use </div>
<div>
# cp -l /etc/rc.d/rc2.d/S99oracle
/etc/rc.d/rc2.d/K10oracle</div>
<div>
Do this for rc 3,4 and 5 aswell.</div>
</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
To switch redo logs to another disk, with a new size (i set mine to 512MB):</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
iIn enterprice manager -> server -> Redo Log Groups</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
*) Use force checkpoint if the log you want to delete is archived but not inactive</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
*) Use switch redo log group if you want to delete a log (so you can recreate it with correct size and placement later)</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
*) At any time there must be at least 2 log groups.</div>
<div style="font-family: Georgia, serif; font-size: 100%; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
<br /></div>Gustav Palssonhttp://www.blogger.com/profile/04493737402274767093noreply@blogger.com0