PRVF-0002 : Could not retrieve local nodename

Whilst performing a Oracle 12c grid installation on a new OUEL Linux box, I launched the Oracle Universal Installer (OUI) and received the following message:-

PRVF-0002 : Could not retrieve local nodename

So I checked the hostname and everything seemed to be fine:-

[root@o64-12c-n11 ~]# uname -n
o64-12c-n11.localdomain
[root@o64-12c-n11 ~]# hostname
o64-12c-n11.localdomain

So whats is the problem here? Well, it appears that the OUI performs some pretty hefty checks, including a reverse lookup of the hostname.

For me I recieved the error due to the fact that I didn’t complete the network setup properly, as it turns out that there was a typo in the hosts file.

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.56.131 o64-121-n11 o64-121-n11.localdomain node1

I corrected the typo and the installation continued as normal.

ORACLE Transparent Data Encryption

What is ORACLE Transparent Data Encryption(TDE)?

ORACLE TDE is part of Oracle’s Advanced Security Option (ASO) which is a chargable addition to the Enterprise Edition RDBMS. TDE is used to safeguard sensitive data against unauthorized access from outside the database environment by encrypting data within the datafiles. This prevents unauthorised access at the operating system level by inspecting the contents of datafile. TDE also offers protect of the loss, theft or inadequate decommission of media used to house or backup the database.

Its called transparent as there nothing that an application needs to do to either encrypt or decrypt the data as it is all done by at the database layer, and its fast as it uses oracle database caching optimizations.

In this article we are going to look at implementing both tablespace and column level encryption and investigate how secure the data is.

Example 1 – Encrypting Tablespaces

The first step is to create an Oracle Wallet to store the encryption key details. There are several type of wallet available we are going to create a simple file based one.  The default location of the wallet for a database is denoted by one of the following:-

  1. ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
  2. WALLET_LOCATION parameter in the sqlnet.ora file.
  3. The default location for the wallet ($ORACLE_BASE/admin/$ORACLE_SID/wallet).

In this case we are going to go for the default. Login as sysdba and run the following command:-

SQL> alter system set encryption key identified by “M0resecure”;System altered.

At this point it is worth noting that you will need to reopen  the wallet after an instance restart with the following command:-

alter system set encryption wallet open identified by “M0resecure”;

Should you need to the wallet can also be closed with the following:-

Alter system set encryption wallet close identified by “M0resecure”;

The next step is to create the encrypted tablespace. Now for this example I am going to create two tablespaces, one encrypted and the other not.

CREATE TABLESPACE encryptedts DATAFILE ‘/u01/app/oracle/datapump/data/encryptedts.dbf’ size 10M autoextend on encryption using ‘AES256′ DEFAULT STORAGE(ENCRYPT);
CREATE TABLESPACE nocryptedts DATAFILE ‘/u01/app/oracle/datapump/data/nocryptedts.dbf’ size 10M autoextend on;

The syntax for the tablespace encryptedts is very basic and there are a lot more options you can add, please review the official documentation. Please note that the datafiles are created on the local filsystem and not within ASM. This isn’t a TDE requirement, I’m doing it for investigation work later.

SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;TABLESPACE_NAME                ENC—————————— —SYSTEM                         NOSYSAUX                         NO

UNDOTBS1                       NO

TEMP                           NO

USERS                          NO

EXAMPLE                        NO

ENCRYPTEDTS                    YES

NOCRYPTEDTS                    NO

Then create a couple of users and some objects to test with to test with:-

create USER test_encrypt identified by password;alter user test_encrypt QUOTA UNLIMITED ON encryptedts;alter user test_encrypt QUOTA UNLIMITED ON users;grant create session to test_encrypt;grant create any table to test_encrypt;

create table test_encrypt.enc_test (nocrypt  VARCHAR2(50),encrypt  VARCHAR2(50)) tablespace encryptedts;

insert into test_encrypt.enc_test values(‘Nocrypt column’, ‘This is a not encrypted!’);

commit;

create USER test_nocrypt identified by password;alter user test_nocrypt QUOTA UNLIMITED ON nocryptedts;alter user test_nocrypt QUOTA UNLIMITED ON users;grant create session to test_nocrypt;grant create any table to test_nocrypt;

create table test_nocrypt.enc_test (nocrypt  VARCHAR2(50), encrypt  VARCHAR2(50))

tablespace nocryptedts;

insert into test_nocrypt.enc_test values(‘nocrypt column’, ‘This is a not encrypted!’);

commit;

Now we are going to drop to os and inspect the datafiles. For safetys sake shutdown the database but if you’re a bit more cavalier than me flush the buffer cache to write the data down to disc first.

SQL> connect / as sysdbaConnected.SQL> alter system flush buffer_cache;

So the next few steps are for LINUX users, if your running on windows (Why?) you can use a HEX editor like ultraedit or something and search the file. For the sensible in the (e-)room you can use the LINUX (or OS equivalent) strings command.

[oracle@oel6 data]$ strings nocryptedts.dbf | grep cryptNocrypt columnThis is a not encrypted!
[oracle@oel6 data]$ strings encryptedts.dbf | grep crypt

In the unencrypted datafile you will see the entered data as plain as the nose on your face, however in the encrypted file you will see no matches hence the data is now protected at the operating system layer.

Example 2 – Encrypting columns

Next step is to look at column level encryption. For this we are going to use the test_nocrypt user and create a new table with an encrypted column.

CREATE TABLE test_nocrypt.enc_test_col (
  nocrypt  VARCHAR2(50),
  encrypt  VARCHAR2(50) encrypt NO SALT
)
TABLESPACE nocryptedts;

INSERT INTO test_nocrypt.enc_test_col VALUES ('nosecretcolln1', 'secretcol');
COMMIT;
INSERT INTO test_nocrypt.enc_test_col VALUES ('nosecretcolln2', 'secretcol');
COMMIT;
INSERT INTO test_nocrypt.enc_test_col VALUES ('nosecretcolln3', 'secretcol');
COMMIT;

Notice the parameter NO SALT at the end of the definition for the encrypt column. SALT is a way to strengthen the security of encrypted data. Basically it’s a random string added to the data before its encrypted causing repetition of text in the clear to appear different when encrypted, it’s a method to prevent hackers matching patterns of encrypted text. By default all encrypted columns have salt applied, however if you wish to index the table by the encrypted column you will need to remove salt from the column.

Again either shutdown or flush the buffer_cache and then use strings to scan the file for secret:-

[oracle@oel6 data]$ strings nocryptedts.dbf | grep secretnosecretcolln34nosecretcolln24Znosecretcolln14

From the above output we can see that the same results as with the encrypted tablespace where the encrypted data is no longer visible within the datafile.

Example 3 – Encrypting and existing column

Just to prove the point we are going to alter the table test_nocrypt.enc_test and encrypt the encrypt (poor naming I know!) column. Too do this run the following:-

alter table test_nocrypt.enc_test modify(encrypt encrypt no salt);

Again shutdown or flush the buffer_cache and then strings the file for the word crypt

SQL> !strings nocryptedts.dbf | grep cryptNocrypt columnThis is a not encrypted!
SQL> ! strings nocryptedts.dbf | grep cryptNocrypt column4Nocrypt columnThis is a not encrypted!

What witch craft is this? A new entry has been added containing the unencrypted column and the value that’s meant to be encrypted in the datafile is still visible. Well this is due to the fact that the row that contained the unencrypted data has been duplicated when adding the encryption to the column and the old entry has been marked for deletion but hasn’t yet been over written due to all the other free blocks in the datafile. Lets test with a table with more data.

For this example I have dropped the table, tablespace and recreated them , then added 10 rows of data. Flushed the data down to disc, encrypted the column and flushed the data down to disc again.

[oracle@oel6 data]$ strings nocryptedts.dbf | grep cryptnocrypt col10notencrypted10,nocrypt col09

notencrypted09,

nocrypt col08

notencrypted08,

nocrypt col07

notencrypted07,

nocrypt col06

notencrypted06,

nocrypt col05

notencrypted05,

nocrypt col04

notencrypted04,

nocrypt col03

notencrypted03,

nocrypt col02

notencrypted02,

nocrypt col01

notencrypted01

[oracle@oel6 data]$ strings nocryptedts.dbf | grep cryptnocrypt col10$nocrypt col09$nocrypt col08$^~B

nocrypt col07$

nocrypt col06$

nocrypt col05$

nocrypt col04$)%

nocrypt col03$

nocrypt col02$^

nocrypt col01$,

nocrypt col10

notencrypted10,

nocrypt col09

notencrypted09,

nocrypt col08

notencrypted08,

nocrypt col07

notencrypted07,

nocrypt col06

notencrypted06,

nocrypt col05

notencrypted05,

nocrypt col04

notencrypted04,

nocrypt col03

notencrypted03,

nocrypt col02

notencrypted02,

nocrypt col01

notencrypted01

[oracle@oel6 data]$

We can see that the old unencrypted data is still visible directly in the datafile. It also appears that its not only TDE where this is an issue and it should also be noted that any other encryption software will leave the old clear text somewhere in a datafile when updating a column to encrypted.

During the testing for this article I have completed several tests with the most conclusive way of removing the clear text being to migrate data from the tablespace, drop and recreate the tablespace and move the data back. I also moved the table to a new tablespace filled the old tablespace with junk data then dropped the junk and moved the tablespace back. This worked for me but in real life it would not be so practical I think.

ORA-28368: cannot auto-create wallet

[oracle@oel6 ~]$ sqlplus “/ as sysdba”

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 10 13:46:53 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter system set encryption key identified by “mypassword”;

alter system set encryption key identified by manager

*

ERROR at line 1:

ORA-28368: cannot auto-create wallet

The cause of this issue in my case was due to the location of the wallet (or lack of!). The location of the wallet for a database is denoted by one of the following:-

  1. ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
  2. WALLET_LOCATION parameter in the sqlnet.ora file.
  3. The default location for the wallet ($ORACLE_BASE/admin/$ORACLE_SID/wallet).

I created the directory in the default location and reran the command.

[oracle@oel6 ~]$ sqlplus “/ as sysdba”

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 10 13:46:53 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter system set encryption key identified by manager;

 

System altered.

 

Resolving ORA-28113: policy predicate has error

Whilst reviewing VPD I came across many errors that get introduced due to the incorrect setting up of policies and general poor quality (I was learning) implementation. With a little thought the majority of issues quickly solved however resolving some of the ORA-28113 can be baffling at time so why not get a little help straight from the databases mouth?

While working on my blog overview for VPD (LINK) I setup an example using the employees table from the sample schema. The general idea was to write a policy to allow user ‘SKING’ to see the entire contents of the hr.employees table while all other employees could only see their own row plus those of the employees that they directly managed. As such I wrote the following function to enforce the policy.

CREATE or REPLACE FUNCTION sysadmin_vpd.ac_emp_email(schema_p IN VARCHAR2,

table_p  IN VARCHAR2

)

RETURN VARCHAR2

AS

return_val VARCHAR2 (400):= ’1=2′;

BEGIN

CASE

WHEN SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) = ‘SKING’ THEN

return_val :=’1=1′;

ELSE

return_val := ‘EMAIL = SYS_CONTEXT(”USERENV”, ”SESSION_USER”) or manager_id = (select employee_id from hr.employees where email = SYS_CONTEXT(”USERENV”, ”SESSION_USER”)) ‘;

END CASE;

RETURN return_val;

END ac_emp_email;

/

With the idea being that the subquery feed the emp_id into the main query and generate the required data. I test this is sqlplus with the desired results:-

SQL>  connect mweiss/passwordConnected.SQL> SELECT count(*) FROM hr.employees WHERE email=sys_context(‘USERENV’,’SESSION_USER’) or manager_id = (SELECT employee_id FROM hr.employees WHERE email=sys_context(‘USERENV’,’SESSION_USER’));

COUNT(*)

———-

9

SQL>

However the results of testing proved to be not so positive

SQL> connect sking/passwordConnected.SQL> select count(*) from hr.employees;

COUNT(*)

———-

107

SQL> connect sking/password

Connected.

SQL> select count(*) from hr.employees;

select count(*) from hr.employees

*

ERROR at line 1:

ORA-28113: policy predicate has error

SQL>

ORA-28113: policy predicate has error – WTF?

Off to the great oracle error lookup to see what a 28113 is:-

 [oracle@oel6 ~]$ oerr ORA 2811328113, 00000, “policy predicate has error”
// *Cause: Policy function generates invalid predicate.
// *Action: Review the trace file for detailed error information.

Review the trace file – Hiho its of to udump we go!

A quick cat of the file shows the error quite clearly!?!:-

————————————————————-Error information for ORA-28113:Logon user     : LOZER

Table/View     : HR.EMPLOYEES

Policy name    : ACCESSCTRL_EMP_EMAIL

Policy function: SYSADMIN_VPD.AC_EMP_EMAIL

RLS view  :

SELECT  “EMPLOYEE_ID”,”FIRST_NAME”,”LAST_NAME”,”EMAIL”,”PHONE_NUMBER”,”HIRE_DATE

“,”JOB_ID”,”SALARY”,”COMMISSION_PCT”,”MANAGER_ID”,”DEPARTMENT_ID” FROM “HR”.”EMP

LOYEES”   “EMPLOYEES” WHERE (EMAIL = SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) or m

anager_id = (select employee_id from hr.employees where email = SYS_CONTEXT(‘USE

RENV’, ‘SESSION_USER’)) )

ORA-28108: circular security policies detected

————————————————————-

So my ORA-28113 was actually caused by an ORA-28108 error. Off to the great book of oracle error lookup again

 [oracle@oel6 ~]$ oerr ORA 2810828108, 00000, “circular security policies detected”
// *Cause: Policies for the same object reference each other.
// *Action: Drop the policies
[oracle@oel6 ~]$

So policies for the same object reference each other – eh?

Basically the issue was due to the subquery I was using to return the employees directs, as it was a subquery it is treated as a different query and hence can only return data according to the table policy that its currently trying to set evalute – hence the circular reference. This meant for me that I had to use an application context assist with my test VPD deployment. For you – well this depends on your issue – hopefully as simple as mine!

Oracle Virtual Private Database

What is Oracle Virtual Private database?

Virtual private database is a “free” enterprise edition only feature (it doesn’t appear to be a purchasable option for any other edition) that allows the DBA to control access to data in the database. It is known by many names Virtual Private Database, Fine Grained Access and Row Level security and can be used to control access at the row and column level beyond the ability of views and roles.

Most applications have in built security that stops users from seeing data that they shouldn’t – but what if that same users user connects to the database via say SQL*Plus, Toad or some other tool? Suddenly all the data access controls imposed by the application are removed and the user has virtually unrestricted access to the data within the database. If security was controlled by VPD then the users would receive consistent access to the data no matter how they access the database.

This access control is achieved by creating security policies to control access and applying them to tables as required. Essentially with each policy you are adding dynamic where clause to all SQL statements that are issued against the table. This enables extremely high level of control to the data and database. Each policy applied to a table is also applied to any views that may query the table too, so no sneaky workaround there!

In the article I am going to walk through a few examples of VPD polices using the standard HR schema available in the oracle example schemas. (Check out http://docs.oracle.com/cd/B13789_01/server.101/b10771/installation002.htm for detail how to load these into your database).

Example 1 – Basic select access restrict

For the first example we are going to restrict access to the employees table depending on the user that logs in. If user SKING logs in then they get unrestricted access to the employees table, if any other user logs in then they can only see their own record.

Step 1. Create vpd administrator

As sys run the following to create the sysadmin_vpd user with the minimum required privledges:-

CREATE USER sysadmin_vpd IDENTIFIED BY mypassword
DEFAULT TABLESPACE example
PROFILE default
TEMPORARY TABLESPACE temp ACCOUNT unlock;

GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_vpd;
GRANT SELECT,UPDATE ON HR.EMPLOYEES TO sysadmin_vpd;
GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;
GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;

As the sysadmin_vpd user run the following to create the access control package to contain all functions that will be used to :-

CREATE or REPLACE PACKAGE sysadmin_vpd.vpdpkg_accesscontrol

AS

 

FUNCTION hr_employees_s (schema_p IN VARCHAR2 ,table_p  IN VARCHAR2) RETURN varchar2;

FUNCTION hr_employees_u (schema_p IN VARCHAR2 ,table_p  IN VARCHAR2) RETURN varchar2;

 

END vpdpkg_accesscontrol;

/

CREATE or REPLACE PACKAGE BODY sysadmin_vpd.vpdpkg_accesscontrol

AS

—-

FUNCTION hr_employees_s (schema_p IN VARCHAR2 ,table_p  IN VARCHAR2) RETURN VARCHAR2

AS

lv_predicate VARCHAR2 (400) := ’1=2′;

BEGIN

 

CASE SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’)

WHEN ‘SKING’ THEN

lv_predicate :=’1=1′;

ELSE

lv_predicate := ‘EMAIL = SYS_CONTEXT(”USERENV”, ”SESSION_USER”)’;

END CASE;

 

RETURN lv_predicate;

 

END hr_employees_s;

—-

FUNCTION hr_employees_u (schema_p IN VARCHAR2 ,table_p  IN VARCHAR2) RETURN VARCHAR2

AS

lv_predicate VARCHAR2 (400) := ’1=2′;

BEGIN

 

null;

RETURN lv_predicate;

 

END hr_employees_u;

—-

 

END;

/

 

Step 3. Create the RLS policy

As the sysadmin_vpd user create the policy for the employees table.

BEGIN

DBMS_RLS.ADD_POLICY (

object_schema    => ‘hr’,

object_name      => ‘employees’,

policy_name      => ‘vpdpol_hr_employees_s’,

function_schema  => ‘sysadmin_vpd’,

policy_function  => ‘vpdpkg_accesscontrol.hr_employees_s’,

statement_types  => ‘select’

);

END;

/

For this example we are only writing a policy for select statements, however statement_types can be any of the following values or a comma separated list of 2 or more of them:-

SELECT, INSERT, UPDATE, DELETE, INDEX and from 11gR2 MERGE

Step 4.  Create test users and check access

GRANT CREATE SESSION TO MWEISS IDENTIFIED BY password;

GRANT CREATE SESSION TO SKING IDENTIFIED BY password;

GRANT SELECT,UPDATE ON HR.EMPLOYEES TO sking;

GRANT SELECT,UPDATE ON HR.EMPLOYEES TO mweiss;

SQL> connect mweiss/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

1

 

SQL> connect sking/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

107

 

SQL>

So we can see that the VPD policy has been applied successfully.

Step 5. Check access for the sys, hr and sysadmin_vpd users

SQL> connect / as sysdba
Connected.
SQL> select count(*) from hr.employees;

 

COUNT(*)
———-
107

 

SQL> connect sysadmin_vpd/mypassword
Connected.
SQL> select count(*) from hr.employees;

 

COUNT(*)
———-
0

 

SQL> connect hr/hr
Connected.
SQL> select * from employees;

 

no rows selected

 

SQL>

So as we can see that sys is exempt from the VPD policy, however the schema owner and vpd admin user are not exempt and hence return no data. This should be considered when implementing and troubleshooting VPD policies.

Application context

To complete this task we are going to need to use application context to enable us to get the level of access that we required to the table to perform the selects and updates required. Application contexts can basically be thought of as a predefined collection of required or frequently used data that you can quickly access rather than looking it up each time. Depending on how you configure the context it is stored in either the PGA or UGA and can be used to return the correct security policy at a session level.

Example 2 – Restricting updates and selects

Its pay review time and as the only person who can see all the salaries for members of staff SKING is going to be busy. As he is the president of the company and busy “networking” he decides to relax security on the table and allow managers to see the details for their directs and the HR representative to be able to query all salaries. Company policy is that no employee can update their employee record and the president states that with this in mind if you can see it then you can update it. As such it is your job to amend and create policies on the table to allow this.

To complete this task we are going to need to update the existing select policy on the employees table to reflect the change in requirements, we are going to need to create a new policy for updates to the table and to enable us to do this the first step is going to be creating an application context.

Step 1 – Implement the application context

As sysadmin_vpd run the following to create the application context:-

CREATE CONTEXT vpdctx_employees USING sysadmin_vpd.vpdctx_employees;

Here, vpdctx_employees will be the context namespace and vpdctx_employees is the procedure that is used to populate the vpdctx_employees namespace. When you create the application context, the PL/SQL package does not need to exist, but it must exist at run time.

CREATE or REPLACE PROCEDURE vpdctx_employees

IS

 

emp_id   HR.EMPLOYEES.EMPLOYEE_ID%TYPE := -100;

mgr_id   HR.EMPLOYEES.MANAGER_ID%TYPE := -100;

dept_id  HR.EMPLOYEES.DEPARTMENT_ID%TYPE := -100 ;

job_id   HR.EMPLOYEES.JOB_ID%TYPE := ‘ctxdefault’ ;

 

BEGIN

 

DBMS_SESSION.SET_CONTEXT(‘vpdctx_employees’, ‘job_id’, job_id);

 

SELECT EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID , JOB_ID

INTO emp_id, mgr_id, dept_id, job_id

FROM HR.EMPLOYEES

WHERE email = SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’);

 

DBMS_SESSION.SET_CONTEXT(‘vpdctx_employees’, ‘emp_id’, emp_id);

DBMS_SESSION.SET_CONTEXT(‘vpdctx_employees’, ‘mgr_id’, mgr_id);

DBMS_SESSION.SET_CONTEXT(‘vpdctx_employees’, ‘dept_id’, dept_id);

DBMS_SESSION.SET_CONTEXT(‘vpdctx_employees’, ‘job_id’, job_id);

 

EXCEPTION

WHEN NO_DATA_FOUND THEN

 

DBMS_SESSION.SET_CONTEXT(‘vpdctx_employees’, ‘emp_id’ , -100);

DBMS_SESSION.SET_CONTEXT(‘vpdctx_employees’, ‘mgr_id’ , -100);

DBMS_SESSION.SET_CONTEXT(‘vpdctx_employees’, ‘dept_id’, -100);

DBMS_SESSION.SET_CONTEXT(‘vpdctx_employees’, ‘job_id’ , ‘ctxnotset’);

 

END vpdctx_employees;

/

The next step is to create a Logon trigger to set the application context for every login. To do this as sysadmin_vpd.

CREATE or REPLACE TRIGGER sysadmin_vpd.vpdtrg_logon_setcontext

AFTER LOGON ON DATABASE

BEGIN

sysadmin_vpd.vpdctx_employees;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(

20000, ‘Trigger sysadmin_vpd.vpdtrg_logon_setcontext violation. Login denied.’);

END;

/

To test the context login and query the context using the users sking and mweiss.

SQL> sking/password

 

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

107

SQL> SELECT * FROM session_context;

 

NAMESPACE                      ATTRIBUTE

—————————— ——————————

VALUE

——————————————————————————–

VPDCTX_EMPLOYEES               JOB_ID

AD_PRES

 

VPDCTX_EMPLOYEES               DEPT_ID

90

 

VPDCTX_EMPLOYEES               EMP_ID

100

 

VPDCTX_EMPLOYEES               MGR_ID

 

SQL> conn mweiss/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

1

SQL> select * from session_context;

 

NAMESPACE                      ATTRIBUTE

—————————— ——————————

VALUE

——————————————————————————–

VPDCTX_EMPLOYEES               JOB_ID

ST_MAN

 

VPDCTX_EMPLOYEES               DEPT_ID

50

 

VPDCTX_EMPLOYEES               EMP_ID

120

 

VPDCTX_EMPLOYEES               MGR_ID

100

Step 2 – Update the select policy for the hr.employees table.

In the previous example we created the policy vpdpol_hr_employees_s and the function vpdpkg_accesscontrol.hr_employees_s to return the predicate for select access to the employees table. In this example we are going to modify this function to use the application context vpdctx_employees to implement the security changes required by the business.

CREATE or REPLACE PACKAGE BODY sysadmin_vpd.vpdpkg_accesscontrol

AS

—-

FUNCTION hr_employees_s (schema_p IN VARCHAR2 ,table_p  IN VARCHAR2) RETURN VARCHAR2

AS

lv_predicate VARCHAR2 (400) := ’1=2′;

BEGIN

 

CASE

WHEN SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) = ‘SKING’ THEN

lv_predicate :=’1=1′;

WHEN SYS_CONTEXT(‘VPDCTX_EMPLOYEES’, ‘JOB_ID’) = ‘HR_REP’ THEN

lv_predicate := ’1=1′;

ELSE

lv_predicate := ‘EMPLOYEE_ID = SYS_CONTEXT(”VPDCTX_EMPLOYEES”, ”EMP_ID”) or MANAGER_ID = SYS_CONTEXT(”VPDCTX_EMPLOYEES”, ”EMP_ID”)’;

END CASE;

 

RETURN lv_predicate;

 

END hr_employees_s;

—-

FUNCTION hr_employees_u (schema_p IN VARCHAR2 ,table_p  IN VARCHAR2) RETURN VARCHAR2

AS

lv_predicate VARCHAR2 (400) := ’1=2′;

BEGIN

 

null;

RETURN lv_predicate;

 

END hr_employees_u;

—-

 

END;

/

Create hr_rep user then test for all users

GRANT CREATE SESSION TO smavris IDENTIFIED BY password;

GRANT SELECT,UPDATE ON HR.EMPLOYEES TO smavris;

SQL> connect sking/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

107

 

SQL> connect mweiss/password

Connected.

SQL>  select count(*) from hr.employees;

 

COUNT(*)

———-

0

 

SQL> select * from session_context;

 

NAMESPACE                      ATTRIBUTE

—————————— ——————————

VALUE

——————————————————————————–

VPDCTX_EMPLOYEES               JOB_ID

ctxnotset

 

 

SQL> connect smavris/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

0

 

SQL> select * from session_context;

 

NAMESPACE                      ATTRIBUTE

—————————— ——————————

VALUE

——————————————————————————–

VPDCTX_EMPLOYEES               JOB_ID

ctxnotset

 

So user SKING returns the correct amount of rows but SMARVIS and MWEISS don’t return any rows and their application context has not been setup correctly. Why is this? Well it all has to do with the predicate function vpdac_employees that we just changed. Previously the function evaluated as follows:-

  CASE SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’)

WHEN ‘SKING’ THEN

lv_predicate :=’1=1′;

ELSE

lv_predicate := ‘EMAIL = SYS_CONTEXT(”USERENV”, ”SESSION_USER”)’;

END CASE;

Here the function uses the default context namespace ‘USRENV’ to create the predicate used to access to the table, and hence as the user SKING is still evaluated in the same way they still access the correct amount of data. However all other predicate evaluations now use application context to set the predicate for the policy. As the application context is based on data in the employees table access this table needs to be granted for the first pass after logon so that application can be set up. This can be achieved in many ways but my preference is to program within the case statement in the predicate generating function. This means that vpdac_employees will now look like this:-

CREATE or REPLACE PACKAGE BODY sysadmin_vpd.vpdpkg_accesscontrol

AS

—-

FUNCTION hr_employees_s (schema_p IN VARCHAR2 ,table_p  IN VARCHAR2) RETURN VARCHAR2

AS

lv_predicate VARCHAR2 (400) := ’1=2′;

BEGIN

 

CASE

WHEN SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) = ‘SKING’ THEN

lv_predicate :=’1=1′;

WHEN SYS_CONTEXT(‘VPDCTX_EMPLOYEES’, ‘JOB_ID’) = ‘HR_REP’ THEN

lv_predicate := ’1=1′;

WHEN SYS_CONTEXT(‘VPDCTX_EMPLOYEES’, ‘JOB_ID’) = ‘ctxdefault’ THEN

lv_predicate := ‘EMAIL = SYS_CONTEXT(”USERENV”, ”SESSION_USER”)’;

ELSE

lv_predicate := ‘EMPLOYEE_ID = SYS_CONTEXT(”VPDCTX_EMPLOYEES”, ”EMP_ID”) or MANAGER_ID = SYS_CONTEXT(”VPDCTX_EMPLOYEES”, ”EMP_ID”)’;

END CASE;

 

RETURN lv_predicate;

 

END hr_employees_s;

—-

FUNCTION hr_employees_u (schema_p IN VARCHAR2 ,table_p  IN VARCHAR2) RETURN VARCHAR2

AS

lv_predicate VARCHAR2 (400) := ’1=2′;

BEGIN

 

null;

RETURN lv_predicate;

 

END hr_employees_u;

—-

 

END;

/

Which generates the following results for MWEISS and SMARCUS

SQL> connect mweiss/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

9

 

SQL> select * from session_context;

 

NAMESPACE                      ATTRIBUTE

—————————— ——————————

VALUE

——————————————————————————–

VPDCTX_EMPLOYEES               JOB_ID

ST_MAN

 

VPDCTX_EMPLOYEES               DEPT_ID

50

 

VPDCTX_EMPLOYEES               EMP_ID

120

 

VPDCTX_EMPLOYEES               MGR_ID

100

 

 

SQL> connect smavris/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

107

 

SQL> select * from session_context;

 

NAMESPACE                      ATTRIBUTE

—————————— ——————————

VALUE

——————————————————————————–

VPDCTX_EMPLOYEES               JOB_ID

HR_REP

 

VPDCTX_EMPLOYEES               DEPT_ID

40

 

VPDCTX_EMPLOYEES               EMP_ID

203

 

VPDCTX_EMPLOYEES               MGR_ID

101

 

The reason why this works is that the predicate is generated every time the table is accessed. So when the user first logs into the database and the on logon procedure calls vpdctx_employees the namespace VPDCTX_employees.job_id has been explicitly set to ‘ctxdefault’ allowing the logged on user access to the employees table to setup the context as long as the username exists in the email column of the employees data.

Step 3 – Create update policy

BEGIN

DBMS_RLS.ADD_POLICY (

object_schema    => ‘hr’,

object_name      => ‘employees’,

policy_name      => ‘vpdpol_hr_employees_u’,

function_schema  => ‘sysadmin_vpd’,

policy_function  => ‘vpdpkg_accesscontrol.hr_employees_u’,

statement_types  => ‘update’

);

END;

/

The last step is to create the predicate for updates as follows:-

CREATE or REPLACE PACKAGE BODY sysadmin_vpd.vpdpkg_accesscontrol

AS

—-

FUNCTION hr_employees_s (schema_p IN VARCHAR2 ,table_p  IN VARCHAR2) RETURN VARCHAR2

AS

lv_predicate VARCHAR2 (400) := ’1=2′;

BEGIN

 

CASE

WHEN SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) = ‘SKING’ THEN

lv_predicate :=’1=1′;

WHEN SYS_CONTEXT(‘VPDCTX_EMPLOYEES’, ‘JOB_ID’) = ‘HR_REP’ THEN

lv_predicate := ’1=1′;

WHEN SYS_CONTEXT(‘VPDCTX_EMPLOYEES’, ‘JOB_ID’) = ‘ctxdefault’ THEN

lv_predicate := ‘EMAIL = SYS_CONTEXT(”USERENV”, ”SESSION_USER”)’;

ELSE

lv_predicate := ‘EMPLOYEE_ID = SYS_CONTEXT(”VPDCTX_EMPLOYEES”, ”EMP_ID”) or MANAGER_ID = SYS_CONTEXT(”VPDCTX_EMPLOYEES”, ”EMP_ID”)’;

END CASE;

 

RETURN lv_predicate;

 

END hr_employees_s;

—-

FUNCTION hr_employees_u (schema_p IN VARCHAR2 ,table_p  IN VARCHAR2) RETURN VARCHAR2

AS

lv_predicate VARCHAR2 (400) := ’1=2′;

BEGIN

 

CASE

WHEN SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) = ‘SKING’ THEN

lv_predicate := ‘EMPLOYEE_ID != SYS_CONTEXT(”VPDCTX_EMPLOYEES”, ”EMP_ID”)’;

WHEN SYS_CONTEXT(‘VPDCTX_EMPLOYEES’, ‘JOB_ID’) = ‘HR_REP’ THEN

lv_predicate := ‘EMPLOYEE_ID != SYS_CONTEXT(”VPDCTX_EMPLOYEES”, ”EMP_ID”)’;

ELSE

lv_predicate := ‘EMPLOYEE_ID != SYS_CONTEXT(”VPDCTX_EMPLOYEES”, ”EMP_ID”) AND MANAGER_ID = SYS_CONTEXT(”VPDCTX_EMPLOYEES”, ”EMP_ID”)’;

END CASE;

 

RETURN lv_predicate;

 

END hr_employees_u;

—-

 

END;

/

The last step is to test as such create a new user lozer and lets see what we can update:-

GRANT CREATE SESSION TO lozer IDENTIFIED BY password;

GRANT SELECT,update ON HR.EMPLOYEES TO lozer;

SQL> connect sking/password

Connected.

SQL> update hr.employees set salary=100;

 

106 rows updated.

 

SQL> rollback;

 

Rollback complete.

 

SQL> connect smavris/password

Connected.

SQL> update hr.employees set salary=100;

 

106 rows updated.

 

SQL> rollback;

 

Rollback complete.

 

SQL> connect mweiss/password

Connected.

SQL> update hr.employees set salary=100;

 

8 rows updated.

 

SQL> rollback;

 

Rollback complete.

SQL> connect lozer/password

Connected.

SQL> update hr.employees set salary=100;

 

0 rows updated.

 

SQL> rollback;

 

Rollback complete.

 

Example 3 – Hiding data in columns

Requirements are changing again at the company. The president of the company has decided that the update rules should remain the same but that all employees should have full access to the employees table but they should only be able to see hire_date, salary and commission_pct for themselves and any directs that they may have.

 

To complete this task we are going to implement column level security. To do this we are going to alter the select policy that we created in example 1 to allow users to see all data as long as they are not  trying to select restricted columns.

Step 1 – Drop existing Policy

BEGIN

DBMS_RLS.DROP_POLICY (

object_schema    => ‘hr’,

object_name      => ‘employees’,

policy_name      => ‘vpdpol_hr_employees_s’

);

END;

/

Step 2 – Create the new policy

So before the policy has been created against the table all users can see all rows:-

SQL> connect mweiss/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

107

 

SQL> select count(employee_id) from hr.employees;

 

COUNT(EMPLOYEE_ID)

——————

107

 

SQL> connect lozer/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

107

 

SQL> select count(employee_id) from hr.employees;

 

COUNT(EMPLOYEE_ID)

——————

107

 

Now create the new policy.

BEGIN

DBMS_RLS.ADD_POLICY (

object_schema    => ‘hr’,

object_name      => ‘employees’,

policy_name      => ‘vpdpol_hr_employees_s’,

function_schema  => ‘sysadmin_vpd’,

policy_function  => ‘vpdpkg_accesscontrol.hr_employees_s’,

statement_types  => ‘select’,

sec_relevant_cols=> ‘hire_date,salary,commission_pct’

);

END;

/

Note the extra condition sec_relevant_cols. This ensures that the predicate is applied only when the user attempts to use one of the identified columns in any part of the query.

SQL> connect mweiss/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

9

 

SQL> select count(employee_id) from hr.employees;

 

COUNT(EMPLOYEE_ID)

——————

107

 

SQL> select count(employee_id) from hr.employees where salary > 0;

 

COUNT(*)

———-

9

 

SQL>  select count(employee_id), nvl(salary,-1) from hr.employees group by  nvl(salary,-1);

 

 

COUNT(EMPLOYEE_ID)     SALARY

—————— ———-

1       2400

1       2500

1       2700

1       2200

1       2800

1       8000

2       3200

1       3100

 

8 rows selected.

 

SQL> Select * from hr.employees where department_id=90;

 

no rows selected

 

SQL> Select employee_id  from hr.employees where department_id=90;

 

EMPLOYEE_ID

———–

100

101

102

 

SQL> connect lozer/password

Connected.

SQL> select count(*) from hr.employees;

 

COUNT(*)

———-

1

 

SQL> select count(employee_id) from hr.employees;

 

COUNT(EMPLOYEE_ID)

——————

107

 

So as we can see, whenever a restricted column is directly(named) or indirectly(*) referenced the predicate in the function is enforced and the rows restricted.

Now it doesn’t end here, you can also allow users to display the restricted columns without the data. To do this you need to use dbms_rls.all_rows. Below is an example:-

BEGIN

DBMS_RLS.DROP_POLICY (

object_schema    => ‘hr’,

object_name      => ‘employees’,

policy_name      => ‘vpdpol_hr_employees_s’

);

END;

/

 

BEGIN

DBMS_RLS.ADD_POLICY (

object_schema    => ‘hr’,

object_name      => ‘employees’,

policy_name      => ‘vpdpol_hr_employees_s’,

function_schema  => ‘sysadmin_vpd’,

policy_function  => ‘vpdpkg_accesscontrol.hr_employees_s’,

statement_types  => ‘select’,

sec_relevant_cols=> ‘hire_date,salary,commission_pct’,

sec_relevant_cols_opt => dbms_rls.all_rows

);

END;

/

Lets run the tests for mweiss again where a restricted column is used.

SQL> select count(employee_id) from hr.employees where salary > 0;

 

COUNT(EMPLOYEE_ID)

——————

9

 

No changes here as expected

SQL>  select count(employee_id), nvl(salary,-1) from hr.employees group by  nvl(salary,-1);

 

COUNT(EMPLOYEE_ID) NVL(SALARY,-1)

—————— ————–

1           2400

1           2500

1           2700

1           2200

1           2800

98             -1

1           8000

2           3200

1           3100

 

9 rows selected.

 

So here we have an extra row containing 98 null values representing the employees whose salary we are not able to see

 

SQL> Select * from hr.employees where department_id=90;

 

EMPLOYEE_ID FIRST_NAME           LAST_NAME

———– ——————– ————————-

EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY

————————- ——————– ——— ———- ———-

COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

————– ———- ————-

100 Steven               King

SKING                     515.123.4567                   AD_PRES

90

 

101 Neena                Kochhar

NKOCHHAR                  515.123.4568                   AD_VP

100            90

 

102 Lex                  De Haan

LDEHAAN                   515.123.4569                   AD_VP

100            90

And this time we get data returned with nulls returned for hire_date, salary and commission_pct;

Rollback

To remove all changes in this example use the following script as sys.

DROP USER mweiss cascade;

DROP USER sking cascade;

DROP USER lozer cascade;

DROP USER marvis cascade;

DROP USER sysadmin_vpd cascade;

BEGIN

DBMS_RLS.DROP_POLICY (

object_schema    => ‘hr’,

object_name      => ‘employees’,

policy_name      => ‘hr_employees_s’

);

END;

/

DBMS_RLS.DROP_POLICY (

object_schema    => ‘hr’,

object_name      => ‘employees’,

policy_name      => ‘hr_employees_u’

);

END;

/

 

 

DataGuard 11gR2 – Prevent accidental Real-time Query access with a parameter

After a server restart of one of my DR servers i noticed that all my databases where open, whats the problem i hear you ask, well this included our 2 physical standby databases. Now I was a little confused as my dataguard check scripts weren’t reporting that these databases where lagging behind their primaries, and those where definitely open as users werent crowded around my desk screaming obsenities. Whats occurring i thought. I had a quick check and yes the databases where definitelty upto date – a quick query on v$database revealed the answer:-

SQL> SELECT open_mode FROM   V$DATABASE;

OPEN_MODE

——————–

READ ONLY WITH APPLY

Oh S*%t we where accidentally running in real-time query mode.

NOTE: This problem was caused by a bit of scripting lazyiness on my part, which has now been resolved!

Now real-time query is a fantastic feature, allowing you to open your standby for query AND apply logs at the same time. However this feature requires the Active Data Guard option which obviously costs. This raised quite a panic at HO and but after a bit of time i found the following MOS Note 1436313.1 which states that the undocumented parameter _query_on_phyiscal can be used to stop this from happening in the future. Obviously as its undocumented you should speak to MOS before using.

SQL> alter system set “_query_on_physical”=false scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  812529152 bytes

Fixed Size                  2264280 bytes

Variable Size             960781800 bytes

Database Buffers           54654432 bytes

Redo Buffers                3498640 bytes
Database mounted.
ORA-16669: instance cannot be opened because the Active Data Guard option is
disabled

SQL> alter database open read only;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ ONLY

where ora-16669 is defined as

ORA-16669   instance cannot be opened because the Active Data Guard option is disabled

Cause:     Stop Redo Apply or set the database state   to APPLY-OFF and then open the database.

Action:     The attempt to open the instance failed because the Active Data Guard option was   not enabled and Redo Apply was either running or was about to be started by the Data Guard broker.

 

11gR2 on OUL6 using Vbox on a LAPTOP – PT3 – Storage Configuration and VM Cloning

Introduction

Hello and welcome to part 3 of my guide 4 part guide to installing an 2 node 11gR2 RAC on a laptop using Oracle VBox (Click here for part 1). In this section i am going to cover creating and configuring the shared disks required for the cluster storage and cloning the VM to create the required addtional nodes.

Creating the storage for cluster disk

You can either do this through the GUI or at the command line. I find the command line option easier and less prone to errors later. So the first step is to create the initial file for the shared disk to use.

vboxmanage createhd –filename D:\VBoxFiles\HD\hdd_6u2_01.vhd –size   10240 –format vhd –variant fixed

0%…10%…20%…30%…40%…50%…60%…70%…80%…90%…100%

Disk image created. UUID:XXXe262c1-XXX3-XXX3-XXX2-XXXedf80aXXX

Once this has completed the next step is to make the disc sharable

vboxmanage modifyhd d:\vboxfiles\hd\hdd_6u2_01.vhd   –type shareable

Once you have made the disc sharable you will need to add the disc to a VM. To do this you will need to run the commands making the necessary changes for each disk you wish to add.

VBoxManage storageattach oul-6u2-r01 –storagectl “SATA”   –port 1 –device 0 –type hdd  –medium   D:\VBoxFiles\HD\hdd_6u2_01.vhd  –mtype   shareable

Repeat the previous step for all the discs you want to share between the servers updating the port and medium values and that’s the initial configuration complete.

Using udev to configure the storage for ASM

The first step is to identify the scsi_id of the discs that you require for ASM

/sbin/scsi_id -g -u -d /dev/sdb

SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_

Once you have identified all the SCSI ID’s the next step is to update the SCSI configuration to make SCSI trusted. To do this add the following to the “/etc/scsi_id.config” file to configure SCSI devices as trusted. If the file doesn’t exists then create it.

options=-g

The next step is to create a rules file for udev to use in /etc/udev/rules.d. To do this create the file /etc/udev/rules.d/99-oracle/asmdevices.rules and insert the for each asm disc, making appropiate changes to RESULT and NAME.

KERNEL==”sd?1″, BUS==”scsi”,   PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”,   RESULT==”1ATA_VBOX_HARDDISK_VB1677d964-917bb0a9″,   NAME=”asm-disk3″, OWNER=”oragrid”,   GROUP=”asmdba”, MODE=”0660″

Once you have completed the updates, save the file and rescan the all the asm discs.

/sbin/partprobe /dev/sdb1

Restart the udev service

udevadm control –reload-rules

/sbin/start_udev

The next step is to check the permissions on the disk

[root@racnode2 ~]# ls -ltr /dev/asm*

brw-rw—- 1 oragrid asmdba 8, 49 Oct    4 16:57 /dev/asm-disk3

brw-rw—- 1 oragrid asmdba 8, 33 Oct    4 16:57 /dev/asm-disk2

brw-rw—- 1 oragrid asmdba 8, 17 Oct    4 17:08 /dev/asm-disk1

Cloning the VM

To clone the VM so that you are able to create new nodes for the cluster you will need to cleanly shutdown the current VM. Once the VM is closed run the following to create the clone of the VM.

c:\Program Files\Oracle\VirtualBox>vboxmanage clonehd   D:\VBoxFiles\oul-6u2-r01\o

ul-6u2-r01.vdi D:\VBoxFiles\VMachine\oul-6u2-r02.vdi

0%…10%…20%…30%…40%…50%…60%…70%…80%…90%…100%

Clone hard disk created in format ‘VDI’. UUID: 3135a133-e01f-4e7e-b8b5-98167e5d20a3

Once the new VM image has been created the next step is to create the VM with VBox. To do this start the vBox application and then click New. Enter the detail sof the VM then click Next.

Clone1

Set the memory settigns as required. Then click Next to continue.

Clone2

The next step is to select the hard drive. Select Use an existing Virtual hard drive file and select the VM Image file you just created. When finished click Create to complete the process.

Clone3

You will now be returned to the main VBox screen.

Clone4

Completing the clone

To complete the clone start the VM and update “/etc/sysconfig/network”, set hostname to the correct value.

The next step is to update the network configuration. The first step is to

[root@racnode1 network-scripts]# ip -o link

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN \    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000\    link/ether 08:00:27:a0:21:1b brd ff:ff:ff:ff:ff:ff
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000\    link/ether 08:00:27:42:bb:ab brd ff:ff:ff:ff:ff:ff
4: eth2: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000\    link/ether 08:00:27:9e:6e:1b brd ff:ff:ff:ff:ff:ff

Now update the network interface configuration files updating the MAC address and ipaddr settings.

HWADDR=08:00:27:0C:D2:F6

IPADDR=10.10.10.2

Lastly update all .bash_profile scripts for the new server, then restart and check connectively to the other nodes in the cluster.

Adding the storage to the 2nd Node

To add the disc to the second node you need to shutdown all nodes that have access to the shared disc and then run the follwoing

VBoxManage storageattach oul-6u2-r01 –storagectl “SATA”   –port 1 –device 0 –type hdd  –medium   D:\VBoxFiles\HD\hdd_6u2_01.vhd  –mtype   shareable

Again repeat the previous step for all the discs you want to share between the servers updating the port and medium values and that’s the configuration of the additional node complete.

And that as they say is that. So we have finished the setup of the storage and cloned the VM to make our additional nodes for our RAC cluster. The next step is to install and patch the GI and RDBMS homes which will be upcoming in part 4.

Follow

Get every new post delivered to your Inbox.