Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Oracle 11gR2 KFOD utility

•KFOD for ASM monitoring

•KFOD help=y

[oracle@raclinux1 ~]$ kfod help=y

_asm_a/llow_only_raw_disks              KFOD allow only raw devices [_asm_allow_only_raw_disks=TRUE/(FALSE)]

_asm_l/ibraries         ASM Libraries[_asm_libraries=lib1,lib2,…]

_asms/id                ASM Instance[_asmsid=sid]

a/sm_diskstring         ASM Diskstring [asm_diskstring=discoverystring, discoverystring …]

c/luster                KFOD cluster [cluster=TRUE/(FALSE)]

db/_unique_name         db_unique_name for ASM instance[db_unique_name=dbname]

di/sks          Disks to discover [disks=raw,asm,all]

ds/cvgroup              Include group name [dscvgroup=TRUE/(FALSE)]

g/roup          Disks in diskgroup [group=diskgroup]

h/ostlist               hostlist[hostlist=host1,host2,…]

metadata_a/usize                AU Size for Metadata Size Calculation

metadata_c/lients               Client Count for Metadata Size Calculation

metadata_d/isks         Disk Count for Metadata Size Calculation

metadata_n/odes         Node Count for Metadata Size Calculation

metadata_r/edundancy            Redundancy for Metadata Size Calculation

n/ohdr          KFOD header suppression [nohdr=TRUE/(FALSE)]

o/p             KFOD options type [OP=DISKS/CANDIDATES/MISSING/GROUPS/INSTS/VERSION/CLIENTS/RM/RMVERS/DFLTDSTR/GPNPDSTR/METADATA/ALL]

p/file          ASM parameter file [pfile=parameterfile]

s/tatus         Include disk header status [status=TRUE/(FALSE)]

v/erbose                KFOD verbose errors [verbose=TRUE/(FALSE)]

KFOD-01000: USAGE: kfod op= asm_diskstring=… | pfile=…

oracle@raclinux1 ~]$ kfod disk=all

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

Disk          Size Path                                     User     Group

================================================================================

1:      15358 Mb /dev/sdb1                                oracle   oinstall

2:      15358 Mb /dev/sdc1                                oracle   oinstall

3:      15358 Mb /dev/sdd1                                oracle   oinstall

4:      40954 Mb /dev/sde1                                oracle   oinstall

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

ORACLE_SID ORACLE_HOME

================================================================================

+ASM1 /u01/app/11.2.0/grid

[oracle@raclinux1 ~]$ kfod op=groups

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

Group          SizeFree Redundancy Name

================================================================================

1:      40954 Mb      30440 Mb     EXTERN FLASH

2:      46074 Mb      42080 Mb     NORMAL DATA

[oracle@raclinux1 ~]$

[oracle@raclinux1 ~]$ kfod op=insts

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

ORACLE_SID ORACLE_HOME

================================================================================

+ASM1 /u01/app/11.2.0/grid

[oracle@raclinux1 ~]$

[oracle@raclinux1 ~]$ kfod op=version

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

ORACLE_SID RAC VERSION

================================================================================

+ASM1 YES 11.2.0.1.0

[oracle@raclinux1 ~]$ kfod op=clients

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

ORACLE_SID VERSION

================================================================================

RACDB_1 11.2.0.1.0

+ASM1 11.2.0.1.0

+ASM1 11.2.0.1.0

RACDB_1 11.2.0.1.0

[oracle@raclinux1 ~]$

[oracle@raclinux1 ~]$ kfod op=rm

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

Rolling Migration State

================================================================================

Inactive

[oracle@raclinux1 ~]$ kfod op=rmvers

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

Rolling Migration Compatible Versions

================================================================================

11.1.0.6.0

11.1.0.7.0

[oracle@raclinux1 ~]$

January 27, 2010 Posted by | oracle | Leave a comment

Oracle Fine-Grained Access Control (FGAC) by example

Oracle Fine-Grained Access Control (FGAC) by example

Table employee:

EMP_ID FIRSTNAME                 LASTNAME                  LO DE

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

1 Steve                     Miller                    GA IT

2 Scott                     Tiger                     GA HR

3 Tom                       Lutz                      FL HR

4 Harry                     OCM                       FL IT

Table orders:

ORDER_ID     AMOUNT LO DE

———- ———- — —

1    3454.45 GA IT

2  324893.34 FL IT

3   34545.11 FL HR

4        234.99 GA HR

Lets assume there are two customers (customer 1, customer 2) using the those tables, one wants to limit access by department, the other by location.

The problem with multiple policies is, that Oracle adds them and then  there would be multiple conditions added to the WHERE clause. That’s the reason why you have to work with grouped policies.

First you must create a Driving Application Context. This context sets the active application.

1. Create a driving context:

CREATE CONTEXT drivectx USING ocm.appsec;

CREATE OR REPLACE PACKAGE ocm.appsec AS

v_department VARCHAR2(2);

v_location VARCHAR2(2);

PROCEDURE setcont(policy_group VARCHAR2);

END;

/

CREATE OR REPLACE PACKAGE BODY ocm.appsec AS

PROCEDURE setcont(policy_group VARCHAR2) AS

v_department VARCHAR2(2);

v_location VARCHAR2(2);

BEGIN

DBMS_SESSION.SET_CONTEXT(‘drivectx’,’active_app’,policy_group);

SELECT department INTO v_department

FROM ocm.employee WHERE lastname = SYS_CONTEXT(‘userenv’,’session_user’);

DBMS_SESSION.SET_CONTEXT(‘drivectx’,’department’,v_department);

SELECT location INTO v_location

FROM ocm.employee WHERE lastname = SYS_CONTEXT(‘userenv’,’session_user’);

DBMS_SESSION.SET_CONTEXT(‘drivectx’,’location’,v_location);

END;

END;

/

2. Define Driving Context for the orders table:

EXEC DBMS_RLS.ADD_POLICY_CONTEXT(‘ocm’,’orders’,’drivectx’,’active_app’);

3. Create DEPARTMENT policy group and add policy to the group:

CREATE OR REPLACE FUNCTION ocm.dep_policy(d1 VARCHAR2, d2 VARCHAR2)

RETURN VARCHAR2 AS

BEGIN

RETURN ‘SYS_CONTEXT(”drivectx”,”department”) = department’;

END;

EXEC DBMS_RLS.CREATE_POLICY_GROUP(‘OCM’,’ORDERS’,’DEPARTMENT’);

EXEC DBMS_RLS.ADD_GROUPED_POLICY(‘OCM’,’ORDERS’,’DEPARTMENT’,’DEP_SECURITY’,’OCM’,’DEP_POLICY’);

4. Create LOCATION policy group and add policy to the group:

CREATE OR REPLACE FUNCTION ocp.loc_policy(d1 VARCHAR2, d2 VARCHAR2)

RETURN VARCHAR2 AS

BEGIN

RETURN ‘SYS_CONTEXT(”ID”,”location”) = location’;

END;

EXEC DBMS_RLS.CREATE_POLICY_GROUP(‘OCP’,’ORDERS’,’LOCATION’);

EXEC DBMS_RLS.ADD_GROUPED_POLICY(‘OCP’,’ORDERS’,’LOCATION’,’LOC_SECURITY’,’OCP’,’LOC_POLICY’);

5. Test it

CONNECT ocm/ocm@demo

EXEC ocm.appsec.setcont(‘DEPARTMENT’);   (use the policy group here)

!!! Attention, group is case sensitve !!!

SELECT * FROM orders;

ORDER_ID     AMOUNT LO DE

———- ———- — —

1    3454.45 GA IT

2    324893.34 FL IT

EXEC ocm.appsec.setcont(‘LOCATION’);

SELECT * FROM orders;

ORDER_ID     AMOUNT LO DE

———- ———- — —

2  324893.34 FL IT

3     34545.11 FL HR

This way you can use policies depending on the application.

January 17, 2010 Posted by | oracle | Leave a comment

Oracle 11g VPD

Oracle 11g VPD

In this article you will see how to use a VPD to provide a record-level security across applications tables. In VPD you attach security policies directly to tables, views and synonyms so there is no ways for users to bypass your security settings.

In a VPD any SQL used to access a table, view or synonym protected by VPD policy is dynamically modified to include a limiting condition such as where clause or and clause. The modification occurs transparently and the user sees only the data that passes the limiting conditions in the where clause. The find grained access allows a great deal of control over the access to application tables. VPD policies can be applied to select, insert, update, index and delete commands. You can create different security policies for each type of access, one for selects another for inserts and so on.

Implementing VPD requires the use of some advanced features such as procedures, triggers and PL/SQL.  VPD includes column-level VPD in which security policies are applied only when a particular column is accessed. Oracle also supports column masking, in which protected columns may display NULL values if their rows are returned. Additionally, auditing has been enhanced so that DBAs can perform fine-grained auditing of select, insert, update and delete commands.

Initial Configuration

VPD allows you to customize the way different users see the table data. VPD requires the use of a package DBMS_RLS.  From sysdba privileged account grant EXECUTE to the DBMS_RLS to all users. Create the users that will be used during the examples: PRACTICE that will own the tables, ADAMS abd BURLINGTON as illustrated below.

connect system/ as sysdba

grant execute on DBMS_RLS to public;

create user adams identified by adams;

create user Burlington identified by Burlington;

create user practice identified by practice;

grant resource to adams, burlington, practice;

grant create any context, create public synonym to practice;

Within the practice user create two tables the stock_account table and the stock_trx table. The stock_account table contains one row for each account that can perform stock trades. The stock_trx table contains one row for each stock bought or sold by that account holder.

connect  practice/practice

create table stock_account

( account                             number(10),

accountlongname           varchar2(50));

insert into stock_account values (

1234, ‘ADAMS’);

Insert into stock_account values (

7777, ‘BURLINGTON’);

create table stock_trx (

account                                number(10),

symbol                 varchar2(20),

price                      number(6,2),

quantity               number(6),

trx_flag                                varchar2(1));

insert into stock_trx values (1234,’ADSP’,31.75,100,’B’);

insert into stock_trx values (7777,’ADSP’,31.50,300,’S’);

insert into stock_trx values (1234.’ADSP’,31.55,100,’B’);

insert into stock_trx values (7777,’OCKS’21.75,1000.’B’);

commit;

Once the tables are created grant the ADAMS and BURLINGTON users access to them.

grant select, insert on stock_trx to ADAMS, BURLINGTON;

grant select on stock_account to ADAMS, BURLINGTON;

You now have the application configuration set. The practice schema owns the data table stock_trx and has a table that you can use to map usernames to account numbers that is, stock_account. With the configuration you can create an application context.

Create an application context

The next step in the VPD configuration is to create an application context.  The purpose of the context is to define the rules you will use to separate users. The context will be part of each session characteristics. Use the crate context command to specify the name of the package you will use to set the rules.

connect  practice/practice

create context practice using practice.context_package

Next create the package.

Create or replace package context_package as

Procedure set_context;

End;

/

Now create the package body.

create or replace package body context_package is

procedure set_context is

v_user                   varchar2(20);

v_id                        number;

begin

dbms_session.set_context(‘PRACTICE’,’SETUP’,’TRUE’);

v_user := SYS_CONTEXT(‘USERENV’,’SESSION_USER’);

begin

select account into v_id from stock_account where accountlongname = v_user;

dbms_session.set_context(‘PRACTICE’,’USER_ID’,v_id0;

exeption

WHEN NO_DATA_FOUND then

Dbms_session.set_context(‘PRACTICE’,’USER_ID’,0);

end;

dbms_session.set_context(‘PRACTICE’,’SETUP’,’FALSE’);

end set_context;

end context_package;

/

The account number is set as the USER_ID variable for the session if the user is account ho;der otherwise a value of 0 is set.

dbms_session.set_context(‘PRACTICE’,’USER_ID’,v_id);

You will be able to reference this context value in your programs and VPD restrictions will be based on the context setting.

Grant a public access to the package.

grant EXECUTE on practice.context_package to public;

create public synonym context_package for practice.context_package;

The rules for establishing the context for sessions are now ready to be activated.

Crate a logon trigger

In order for the context to be set within each user session create a trigger that will be executed each time a user logs into the database.

create or replace trigger practice.set_security_context

after logon on database

begin

practice.context_package.set_context;end;

/

Each time a user logs in Oracle will execute the set_context procedure within the practice user’s context_package package. This procedure will query the stock_account table and see if there is a matching account name. If there is a match then the session context will be altered to reflect the account value.

Test the context setting before going past this point in the implementation of a VPD. You can use the SYS_CONTEXT function of determine whether the configuration is working properly as shown below.

connect adams/adams

select SYS_CONTEXT(‘USERENV’,’SESSION_USER’) Username,

SYS_CONTEXT(‘PRACTICE’,’USER_ID’) ID from dual;

Username

ID

ADAMS

1234

The SYS_CONTEXT function show the power of the VPD configuration. For each user who logs in, Oracle will check the stock_account table and will use the matching account value to set a value for the session’s User_ID.   In this case, Oracle looked up the ADAMS user in the stock_account table, found its account value 1234 and set its session’s User_ID variable to that value. If you have that information, you can restrict access to rows in other tables based on the account data in the stock_account table. VPD allows you to restrict that access automatically with no need to create views. Every time a user attempts to access a table access can be restricted regardless of the tool used.

If you log in as a user not listed in the stock_account table the User_ID value will be 0.

Creating a security policy

So far the context and the logon trigger have been created. For the context package to have an effect on the users’ interaction with the database you need to create a security package that acts on the output of the context package.

First create the package header. In this example there are two type of actions supported against the stock_trx table, inserts and selects, so two functions will be created within the package.

connect practice/practice

create or replace package security_package as

function stock_trx_insert_security(owner varchar2, objectname varchar2) return varchar2;

function stock_trx_select_security(owner varchar2, objname varchar2) return varchar2;

end security_package;

/

Next create the package body. Both functions follow the same pattern, geared toward generating a predicate that will be applied every time a user queries the stock_trx table. If the session username is the owner of the table then the predicate is NULL. For all other users the User_ID context variable is evaluated and is passed as a limiting condition to any query. The object of these functions is to generate a predicate such as ‘where account=1234’ to be applied each time the table is queried by a user.

create or replace package body security_package is

function stock_trx_select_security(owner varchar2, objname varchar2) return varchar2 is

Predicate varchar2(2000);

Begin

Predicate := ‘1=2’;

If (SYS_CONTEXT(‘USERENV’,’SESSION_USER’) – ‘PRACTICE’ ) then

Predicate := NULL;

Else

Predicate := ‘ACCOUNT = SYS_CONTEXT(‘’PRACTICE’’,’’USER_ID’’)’;

End if;

Return Predicate;

end stock_trx_select_security;

function stock_trx_insert_security ( owner varchar2, objname varchar2) return varchar2 is

Predicate varchar(2000);

begin

Predicate := ‘1=2’;

If ( SYS_CONTEXT(‘USERENV’,’SESSION_USER’) = ‘PRACTICE’ ) then

Predicate := NULL;

Else

Predicate := ‘ACCOUNT = SYS_CONTEXT(‘’PRACTICE’’,’’USER_ID’’)’;

End if;

Return Predicate;

end stock_trx_insert_security;

end security_package;

/

Since this package will be executed by all the users who access the stock_trx table grant users the privilege to execute it and crate a public synonym.

grant execute on practice.security_package to public;

create public synonym security_package for practice.security_package;

Apply the security policy to tables

Now that security package exists you can relate it to the tables. To add a policy to a table use the ADD_POLICY procedure of the DBMS_RLS package as follows.

begin

dbms_rls.add_policy(‘PRACTICE’,’STOCK_TRX’,’STOCK_TRX_INSERT_POLICY’,’PRACTICE’,’SECURITY_PACKAGE.STOCK_TRX_INSERT_SECURITY’,’INSERT’,TRUE);

dbms_rls.add_policy(‘PRACTICE’,’STOCK_TRX’,’STOCK_TRX_SELECT_POLICY’,’PRACTICE’,’SECURITY_PACKAGE.STOCK_TRX_SELECT_SECURITY’,’SELECT’ );

end;

/

The ADD_POLICY procedure is executed two times. In the first execution the stock_trx_insert_security function is specified for execution during attempts to insert rows into the stock_trx table. In the second execution the stock_trx_select_select_security function is applied to queries of the stock_trx table. You can have distinct rules for security for different operations against the table. The variables passed to the ADD_SECURITY procedure are the schema name, table name, the schema name and function name for the security function and the operation affected.

Test VPD

To see the results of the security policy query the table. The stock_trx table has four rows, two from the ADAMS account ‘1234’ and two from the BURLINGTHON account ‘7777’. Log in as one of those users and query the tables.

connect adams/adams

select * from practice.stock_trx;

ACCOUNT                           SYMBOL                                               PRICE                    QUANTITY                           T

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

1234                                       ADSP                                                     31.75                     100                                         B

1234                                       ADSP                                                     31.55                     100                                         B

Although the ADAMS user did not provide a where clause in his query Oracle applied a predicate to his query limiting the results to rows with the account value of 1234. What if the BURLINGTON  user attempts the same query?

connect burlington/burlington

select * from practice.stock_trx;

ACCOUNT                           SYMBOL                                               PRICE                    QUANTITY                           T

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

7777                                       ADSP                                                     31.50                     300                                         S

7777                                       OCKS                                                     21.75                     1000                                       B

The BURLINGTON user only sees the rows with account value of 7777 based on his row in the stock_account table.

What about other users? If they are not in the stock_account table they cannot retrieve rows from the stock_trx table.

connect system/manager

select * from practice.stock_trx;

no rows selected

In addition to selects, inserts werealso  limited by the security policy. User can only insert rows into stock_trx if the account value matches the user’s context settings. Attempts to insert other rows fail.

connect practice/practice

grant insert on stock_trx to adams;

insert into practice.stock_trx values (7777,’ADSP’,31.50,100,’B’);

ERROR at line 1:

ORA-28115: policy with check option violation

The ORA-28115 error indicates that the user attempted to insert a row that the user would not be able to select due to the VPD settings. You can establish several policies for the same table, view or synonym. All policies applied to a table are enforced with and syntax. If you have three policies applied to the table each one is applied to that table.

How to implement column-level VPD

You can implement VPD at the column level. If a column with secure data is referenced by a query you can use column-level VPD to apply a security or display the column with NULL values.  This approach is different from the row-based VPD solution shown earlier. In a column-level VPD all rows may be displayed but the columns may be shielded from the user. Column-level VPD can be applied to a table or a view. To use column level VPD specify a value for the sec_relevant_cols input parameters for the ADD_POLICY procedure of DBMS_RLS. When creating a policy you can use the following command.

begin

dbms_rls.add_policy

( object_schema=>’PRACTICE’,

object_name=>’STOCK_TRX’,

policy_name=>’STOCK_TRX_SELECT_POLICY’,

function_schema=>’PRACTICE’,

policy_function=>’SECURITY_PACKAGE.STOCK_TRX_SELECT_SECURITY’,

sec_relevant_cols=>’Price’);

end;

/

By default the rows will be returned based on the security policy function when the Price column if referenced by a query. To use the column masking option tell Oracle to return all rows by setting the sec_relevant_col_opts parameter to DBMS_RLS.ALL_ROWS following the sec_relevant_cols parameter setting. When this version of the policy is used all rows will be returned by the query.  For the rows that the user would not normally be able to see the secure column Price will display NULL values. Column masking applies only to queries not to DML operations. Your applications must support the display of NULL values if you use column masking.

How to disable VPD

To remove a VPD functionality reverse the steps shown above.

  1. Remove the policy from the table using the DROP_POLICY procedure of the DBMS_RLS package.
  2. Drop the logon trigger
  3. Drop the other packages.

To drop a policy execute the DROP_POLICY procedure. Its three input variables are the schema name, the object name and the policy. The following example drops the STOCK_TRX_INSERT_POLICY.

dbms_rls.drop_policy(‘PRACTICE’,’STOCK_TRX’,’STOCK_TRX_INSERT_POLICY’);

Similarly you can drop the STOCK_TRX_SELECT_POLICY.

dbms_rls.drop_policy(‘PRACTICE’,’STOCK_TRX’,’STOCK_TRX_SELECT_POLICY’);

At this point the policy will be dropped but the login trigger will still remain and will be executing during each login. Remember to drop the trigger (in this case practice.set_security_context) so the database does not perform unnecessary work. Drop the security context trigger as shown bellow.

connect / as sysdba

select owner, trigger_name from dba_triggers where triggering_event like ‘LOGON%’;

OWNER                                                                                                TRIGGER_NAME

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

PRACTICE                                                                            SET_SECURITY_CONTEXT

drop trigger practice.set_security_context;

Trigger dropped.

If the VPD restrictions are no longer needed you can then drop the packages that set the application context.

How to use policy groups

You can create policy groups, add policies to a policy group and enable policies within a group.  You can create groups that integrate policies affecting the same tables. If multiple applications use the same tables you can use groups to manage the table-level policies that should be enabled during the application usage. All policies in a policy group can be applied at application run time. By default all policies belong to the SYS_DEFAULT policy group. Policies in the SYS_DEFAULT policy group will always be executed along with the policy group specified by the driving context. You cannot drop the SYS_DEFAULT policy group. To add a new policy group use the CREATE_POLICY_GROUP procedure.

dbms_rls.create_policy_group(

object_schema                 varchar2,

object_name                     varchar2,

policy_group                      varchar2);

You can then add a policy to the group using the ADD_GROUPED_POLICY procedure.

dbms_rls.add_grouped_policy(

object_schema                 varchar2,

object_name                     varchar2,

policy_group                      varchar2,

policy_name                      varchar2,

function_schema             varchar2,

policy_function                 varchar2,

statement_types              varchar2,

update_check                    boolean,

enabled                                boolean,

static_policy                       IN BOOLEAN FALSE,

policy_type                         IN BINARY_INTEGER NULL,

long_predicate                  IN BOOLEAN      FALSE,

sec_relevant_cols             IN VARCHAR2);

For example you can create a policy group named TRXAUDIT and then add the STOCK_TRX_SELECT_POLICY to it.

begin

dbms_rls.create_policy_group(‘PRACTICE’,’STOCK_TRX’,’TRXAUDIT’);

dbms_rls.add_grouped_policy(‘PRACTICE’,’STOCK_TRX’,’TRXAUDIT’,

‘STOCK_TRX_SELECT_POLICY’,’PRACTICE’,’SECURITY_PACKAGE.STOCK_TRX_SELECT_SECURE’);

End;

/

When the database is accessed the application initializes the driving context to specify the policy group that will be used. In the create context command specify the name of the procedure to use.

create context PRACTICE using PRACTICE.CONTEXT_PACKAGE;

PRACTICE.CONTEXT_PACKAGE executed the SET_CONTEXT procedure to set the application context.

DBMS_SESSION.SET_CONTEXT(‘PRACTICE’,’SETUP’,’TRUE’);

For policy group the third parameter passed to SET_CONTEXT will be the policy group name. For the application to invoke a specific context group rewrite CONTEXT_PACKAGE to support a third input variable (policy group) and add that to the SET_CONTEXT execution.

DBMS_SESSION.SET_CONTEXT(‘PRACTICE’,’SETUP’,<policy_group_>);

Within your application execute CONTEXT_PACKAGE and set the policy group value to TRXAUDIT. When the user queries the STOCK_TRX table the VPD restrictions in SYS_DEFAULT and the TRXAUDIT policy group will then be applied. You can remove a policy from a policy group using the DROP_GROUPED_POLICY , disble in using DISABLED_GROUPED_POLICY or re-enable it using ENABLE_GROUPED_POLICY.n Use DELETE_POLICY_GROUP to drop the policy group entirely.

January 15, 2010 Posted by | oracle | 4 Comments

Listing active contexts in Oracle

Listing active contexts in Oracle

— Listing active contexts
CREATE OR REPLACE PROCEDURE listContext AS
list DBMS_SESSION.APPCTXTABTYP;
cnt NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘USERENV nls_territory = ‘||SYS_CONTEXT(‘userenv’,’nls_territory’));
DBMS_OUTPUT.PUT_LINE(‘USERENV nls_currency = ‘||SYS_CONTEXT(‘userenv’,’nls_currency’));
DBMS_OUTPUT.PUT_LINE(‘USERENV nls_calendar = ‘||SYS_CONTEXT(‘userenv’,’nls_calendar’));
DBMS_OUTPUT.PUT_LINE(‘USERENV nls_date_format = ‘||SYS_CONTEXT(‘userenv’,’nls_date_format’));
DBMS_OUTPUT.PUT_LINE(‘USERENV nls_date_language = ‘||SYS_CONTEXT(‘userenv’,’nls_date_language’));
DBMS_OUTPUT.PUT_LINE(‘USERENV nls_sort = ‘||SYS_CONTEXT(‘userenv’,’nls_sort’));
DBMS_OUTPUT.PUT_LINE(‘USERENV session_user = ‘||SYS_CONTEXT(‘userenv’,’session_user’));
DBMS_OUTPUT.PUT_LINE(‘USERENV current_user = ‘||SYS_CONTEXT(‘userenv’,’current_user’));
DBMS_OUTPUT.PUT_LINE(‘USERENV current_schema = ‘||SYS_CONTEXT(‘userenv’,’current_schema’));
DBMS_OUTPUT.PUT_LINE(‘USERENV current_schemaid = ‘||SYS_CONTEXT(‘userenv’,’current_schemaid’));
DBMS_OUTPUT.PUT_LINE(‘USERENV session_userid = ‘||SYS_CONTEXT(‘userenv’,’session_userid’));
DBMS_OUTPUT.PUT_LINE(‘USERENV currend_userid = ‘||SYS_CONTEXT(‘userenv’,’current_userid’));
— Listing user defined namespaces
DBMS_SESSION.LIST_CONTEXT(list, cnt);
FOR i IN 1..cnt LOOP
DBMS_OUTPUT.PUT_LINE(list(i).namespace||’  ‘||list(i).attribute||’ = ‘||list(i).value);
END LOOP;
END;
/

January 13, 2010 Posted by | oracle | 2 Comments

Rename disk group in Oracle 11g R2

Rename disk group in Oracle 11g R2

In this article will look at renamedg utility introduced in Oracle 11gR2 to change the name of an existing ASM disk group. In order to use the command a disk group needs to be dismounted. Oracle GI is installed on a standalone server to implement ASM and Oracle restart. Although, to rename a disk group seems to be a straightforward task the resource dependencies are important and need to be taken into consideration. The disk group DATA in the exercise is a storage for the ASM spfile and for couple of databases. During the test we will look at how to point the ASM and existing databases to the newly renamed disk group DG. The renamedg utility works in two phases. First phase identifies the disk group, performs a discovery of the disks member of the group, checks if the disk group is mounted and if the group is not mounted checks the member disks and generates the configuration file. Second phase performs the actual renaming. By default the renamed utility combines the two phases, though we can explicitly separate the two phases by running the utility twice with a phase parameter. Note that if using Oracle GI for a standalone server we need to start explicitly ./acfsload start –s from $GI_HOME/bin in comparison to Oracle GI for a cluster.

The utility options

[oracle@oel55 ~]$ renamedg -help

NOTE: No asm libraries found in the system

Parsing parameters..

phase Phase to execute,

(phase=ONE|TWO|BOTH), default BOTH

dgname Diskgroup to be renamed

newdgname New name for the diskgroup

config intermediate config file

check just check-do not perform actual operation,

(check=TRUE/FALSE), default FALSE

confirm confirm before committing changes to disks,

(confirm=TRUE/FALSE), default FALSE

clean ignore errors,

(clean=TRUE/FALSE), default TRUE

asm_diskstring ASM Diskstring (asm_diskstring=’discoverystring’,

‘discoverystring1’ …)

verbose verbose execution,

(verbose=TRUE|FALSE), default FALSE

keep_voting_files Voting file attribute,

(keep_voting_files=TRUE|FALSE), default FALSE

[oracle@oel55 ~]$

Later we will use the renamed utility to rename a disk group data using the default phase=both option with a single call. Below are examples of invoking renamed utility to prepare a configuration file. Note that in order to avoid errors and have a successful completion

  1. Asm_diskstring need to be specified
  2. Disk group needs to be dismounted

 

What if the asm_string is not specified?

oracle@oel55 ~]$ renamedg phase=one dgname=data1 newdgname=dg1 confirm=true config=/tmp/renamedg verbose=true

NOTE: No asm libraries found in the system

Parsing parameters..

Parameters in effect:

Old DG name : DATA1

New DG name : DG1

Phases :

Phase 1

Discovery str : (null)

Confirm : TRUE

Clean : TRUE

Raw only : TRUE

renamedg operation: phase=one dgname=data1 newdgname=dg1 confirm=true config=/tmp/renamedg verbose=true

Executing phase 1

Discovering the group

Performing discovery with string:

KFNDG-00407: file not found; arguments: []

Terminating kgfd context 0xb7f1a050

[oracle@oel55 ~]$

What if the disk group is mounted?

[oracle@oel55 ~]$ renamedg phase=one dgname=data1 newdgname=dg1 confirm=false config=/tmp/renamedg verbose=true asm_diskstring=’/dev/oracleasm/disks/*’

NOTE: No asm libraries found in the system

Parsing parameters..

Parameters in effect:

Old DG name : DATA1

New DG name : DG1

Phases :

Phase 1

Discovery str : /dev/oracleasm/disks/*

Clean : TRUE

Raw only : TRUE

renamedg operation: phase=one dgname=data1 newdgname=dg1 confirm=false config=/tmp/renamedg verbose=true asm_diskstring=/dev/oracleasm/disks/*

Executing phase 1

Discovering the group

Performing discovery with string:/dev/oracleasm/disks/*

Identified disk UFS:/dev/oracleasm/disks/DISK4 with disk number:0 and timestamp (32939882 785868800)

Identified disk UFS:/dev/oracleasm/disks/DISK5 with disk number:1 and timestamp (32939882 785868800)

Identified disk UFS:/dev/oracleasm/disks/DISK6 with disk number:2 and timestamp (32939882 785868800)

Checking for hearbeat…

Re-discovering the group

Performing discovery with string:/dev/oracleasm/disks/*

Identified disk UFS:/dev/oracleasm/disks/DISK4 with disk number:0 and timestamp (32939882 785868800)

Identified disk UFS:/dev/oracleasm/disks/DISK5 with disk number:1 and timestamp (32939882 785868800)

Identified disk UFS:/dev/oracleasm/disks/DISK6 with disk number:2 and timestamp (32939882 785868800)

Checking if the diskgroup is mounted

Checking disk number:0

KFNDG-00405: file not found; arguments: [DATA1]

Terminating kgfd context 0xb7f61050

[oracle@oel55 ~]$

Successful completion of phase one

[oracle@oel55 ~]$ renamedg phase=one dgname=data1 newdgname=dg1 confirm=true config=/tmp/renamedg verbose=true asm_diskstring=’/dev/oracleasm/disks/*’

NOTE: No asm libraries found in the system

Parsing parameters..

Parameters in effect:

Old DG name : DATA1

New DG name : DG1

Phases :

Phase 1

Discovery str : /dev/oracleasm/disks/*

Confirm : TRUE

Clean : TRUE

Raw only : TRUE

renamedg operation: phase=one dgname=data1 newdgname=dg1 confirm=true config=/tmp/renamedg verbose=true asm_diskstring=/dev/oracleasm/disks/*

Executing phase 1

Discovering the group

Performing discovery with string:/dev/oracleasm/disks/*

Identified disk UFS:/dev/oracleasm/disks/DISK4 with disk number:0 and timestamp (32939882 785868800)

Identified disk UFS:/dev/oracleasm/disks/DISK5 with disk number:1 and timestamp (32939882 785868800)

Identified disk UFS:/dev/oracleasm/disks/DISK6 with disk number:2 and timestamp (32939882 785868800)

Checking for hearbeat…

Re-discovering the group

Performing discovery with string:/dev/oracleasm/disks/*

Identified disk UFS:/dev/oracleasm/disks/DISK4 with disk number:0 and timestamp (32939882 785868800)

Identified disk UFS:/dev/oracleasm/disks/DISK5 with disk number:1 and timestamp (32939882 785868800)

Identified disk UFS:/dev/oracleasm/disks/DISK6 with disk number:2 and timestamp (32939882 785868800)

Checking if the diskgroup is mounted

Checking disk number:0

Checking disk number:1

Checking disk number:2

Checking if diskgroup is used by CSS

Generating configuration file..

Completed phase 1

Terminating kgfd context 0xb7f27050

[oracle@oel55 ~]$

[oracle@oel55 ~]$cat /tmp/renamedg

/dev/oracleasm/disks/DISK4 DATA1 DG1

/dev/oracleasm/disks/DISK5 DATA1 DG1

/dev/oracleasm/disks/DISK6 DATA1 DG1

[oracle@oel55 ~]$

Determine the resources that depend on disk group DATA.

  1. ASM uses DATA for the spfile

     

[oracle@oel55 ~]$ srvctl config asm

ASM home: /u01/app/11.2.0/grid

ASM listener: LISTENER

Spfile: +DATA/asm/asmparameterfile/registry.253.725013905

ASM diskgroup discovery string: /dev/oracleasm/disks/*

[oracle@oel55 ~]$

  1. Few databases (DB11P, D11P and REP) are using DATA disk group

 

[oracle@oel55 ~]$ cat /etc/oratab

#Backup file is /u01/app/oracle/product/11.2.0/db_1/srvm/admin/oratab.bak.oel55 line added by Agent

#

# This file is used by ORACLE utilities. It is created by root.sh

# and updated by the Database Configuration Assistant when creating

# a database.

# A colon, ‘:’, is used as the field terminator. A new line terminates

# the entry. Lines beginning with a pound sign, ‘#’, are comments.

#

# Entries are of the form:

# $ORACLE_SID:$ORACLE_HOME:<N|Y>:

#

# The first and second fields are the system identifier and home

# directory of the database respectively. The third filed indicates

# to the dbstart utility that the database should , “Y”, or should not,

# “N”, be brought up at system boot time.

#

# Multiple entries with the same $ORACLE_SID are not allowed.

#

#

+ASM:/u01/app/11.2.0/grid:N

*:/u01/app/oracle/Middleware/oms11g:N

*:/u01/app/oracle/Middleware/agent11g:N

DPS1:/u01/app/oracle/product/11.2.0/db_1:N # line added by Agent

D11P:/u01/app/oracle/product/11.2.0/db_1:N # line added by Agent

REP:/u01/app/oracle/product/11.2.0/db_1:N # line added by Agent

DB11P:/u01/app/oracle/product/11.2.0/db_1:N # line added by Agent

[oracle@oel55 ~]$ srvctl config database -d REP

Database unique name: REP

Database name: REP

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA/REP/spfileREP.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATA

Services:

[oracle@oel55 ~]$

[oracle@oel55 ~]$ srvctl config database -d DB11P

Database unique name: DB11P

Database name: DB11P

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA/DB11P/spfileDB11P.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATA

Services:

[oracle@oel55 ~]$

[oracle@oel55 ~]$ srvctl config database -d D11P

Database unique name: D11P

Database name: D11P

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA/D11P/spfileD11P.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATA

Services:

[oracle@oel55 ~]$

[oracle@oel55 ~]$ srvctl config database -d DPS1

Database unique name: DPS1

Database name: DB11P

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA1/DPS1/spfileDPS1.ora_262

Domain:

Start options: open

Stop options: immediate

Database role: PHYSICAL_STANDBY

Management policy: AUTOMATIC

Disk Groups:

Services:

[oracle@oel55 ~]$

The crs resources are as follow:

[oracle@oel55 ~]$ crsctl status resource

NAME=ora.DATA.dg

TYPE=ora.diskgroup.type

TARGET=ONLINE

STATE=ONLINE on oel55

NAME=ora.DATA1.dg

TYPE=ora.diskgroup.type

TARGET=ONLINE

STATE=ONLINE on oel55

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on oel55

NAME=ora.asm

TYPE=ora.asm.type

TARGET=ONLINE

STATE=ONLINE on oel55

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on oel55

NAME=ora.d11p.db

TYPE=ora.database.type

TARGET=ONLINE

STATE=ONLINE on oel55

NAME=ora.db11p.db

TYPE=ora.database.type

TARGET=ONLINE

STATE=ONLINE on oel55

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on oel55

NAME=ora.dps1.db

TYPE=ora.database.type

TARGET=OFFLINE

STATE=OFFLINE

NAME=ora.rep.db

TYPE=ora.database.type

TARGET=ONLINE

STATE=ONLINE on oel55

[oracle@oel55 ~]$

Prepare for disk group rename.

  1. Note the ASM spfile location
  2. Prepare scripts to rename the file names of the databases using DATA disk group.
  3. Recreate the spfile to point to the new DG group for each affected database.

 

ASM spfile is +DATA/asm/asmparameterfile/registry.253.725013905262 and will point to +DG/asm/asmparameterfile/registry.253.725013905

Prepare scripts to rename the file names of the affected databases.

[oracle@oel55 tmp]$ cat d11p.sql

alter database rename file ‘+DATA/d11p/datafile/system.775.725458923’ to ‘+DG/d11p/datafile/system.775.725458923’;

alter database rename file ‘+DATA/d11p/datafile/sysaux.776.725458923’ to ‘+DG/d11p/datafile/sysaux.776.725458923’;

alter database rename file ‘+DATA/d11p/datafile/undotbs1.777.725458923’ to ‘+DG/d11p/datafile/undotbs1.777.725458923’;

alter database rename file ‘+DATA/d11p/datafile/users.746.725458923’ to ‘+DG/d11p/datafile/users.746.725458923’;

alter database rename file ‘+DATA/d11p/onlinelog/group_3.737.725459019’ to ‘+DG/d11p/onlinelog/group_3.737.725459019’;

alter database rename file ‘+DATA/d11p/onlinelog/group_3.655.725459021’ to ‘+DG/d11p/onlinelog/group_3.655.725459021’;

alter database rename file ‘+DATA/d11p/onlinelog/group_2.763.725459017’ to ‘+DG/d11p/onlinelog/group_2.763.725459017’;

alter database rename file ‘+DATA/d11p/onlinelog/group_2.699.725459019’ to ‘+DG/d11p/onlinelog/group_2.699.725459019’;

alter database rename file ‘+DATA/d11p/onlinelog/group_1.757.725459015’ to ‘+DG/d11p/onlinelog/group_1.757.725459015’;

alter database rename file ‘+DATA/d11p/onlinelog/group_1.759.725459017’ to ‘+DG/d11p/onlinelog/group_1.759.725459017’;

[oracle@oel55 tmp]$

[oracle@oel55 tmp]$ cat db11p.sql

alter database rename file ‘+DATA/db11p/datafile/system.770.725452799’ to ‘+DG/db11p/datafile/system.770.725452799’;

alter database rename file ‘+DATA/db11p/datafile/sysaux.771.725452801’ to ‘+DG/db11p/datafile/sysaux.771.725452801’;

alter database rename file ‘+DATA/db11p/datafile/undotbs1.781.725452801’ to ‘+DG/db11p/datafile/undotbs1.781.725452801’;

alter database rename file ‘+DATA/db11p/datafile/users.635.725452801’ to ‘+DG/db11p/datafile/users.635.725452801’;

alter database rename file ‘+DATA/db11p/datafile/example.281.725452961’ to ‘+DG/db11p/datafile/example.281.725452961’;

alter database rename file ‘+DATA/db11p/onlinelog/group_3.314.725452933’ to ‘+DG/db11p/onlinelog/group_3.314.725452933’;

alter database rename file ‘+DATA/db11p/onlinelog/group_3.313.725452937’ to ‘+DG/db11p/onlinelog/group_3.313.725452937’;

alter database rename file ‘+DATA/db11p/onlinelog/group_2.282.725452929’ to ‘+DG/db11p/onlinelog/group_2.282.725452929’;

alter database rename file ‘+DATA/db11p/onlinelog/group_2.315.725452931’ to ‘+DG/db11p/onlinelog/group_2.315.725452931’;

alter database rename file ‘+DATA/db11p/onlinelog/group_1.772.725452925’ to ‘+DG/db11p/onlinelog/group_1.772.725452925’;

alter database rename file ‘+DATA/db11p/onlinelog/group_1.545.725452927’ to ‘+DG/db11p/onlinelog/group_1.545.725452927’;

alter database rename file ‘+DATA/db11p/onlinelog/group_4.354.725478873’ to ‘+DG/db11p/onlinelog/group_4.354.725478873’;

alter database rename file ‘+DATA/db11p/onlinelog/group_4.780.725478873’ to ‘+DG/db11p/onlinelog/group_4.780.725478873’;

alter database rename file ‘+DATA/db11p/onlinelog/group_5.356.725478875’ to ‘+DG/db11p/onlinelog/group_5.356.725478875’;

alter database rename file ‘+DATA/db11p/onlinelog/group_5.330.725478877’ to ‘+DG/db11p/onlinelog/group_5.330.725478877’;

alter database rename file ‘+DATA/db11p/onlinelog/group_6.722.725478877’ to ‘+DG/db11p/onlinelog/group_6.722.725478877’;

alter database rename file ‘+DATA/db11p/onlinelog/group_6.720.725478879’ to ‘+DG/db11p/onlinelog/group_6.720.725478879’;

alter database rename file ‘+DATA/db11p/onlinelog/group_7.719.725478879’ to ‘+DG/db11p/onlinelog/group_7.719.725478879’;

alter database rename file ‘+DATA/db11p/onlinelog/group_7.718.725478881’ to ‘+DG/db11p/onlinelog/group_7.718.725478881’;

[oracle@oel55 tmp]$

[oracle@oel55 tmp]$ cat rep.sql

alter database rename file ‘+DATA/rep/datafile/system.264.725016995’ to ‘+DG/rep/datafile/system.264.725016995’;

alter database rename file ‘+DATA/rep/datafile/sysaux.265.725017009’ to ‘+DG/rep/datafile/sysaux.265.725017009’;

alter database rename file ‘+DATA/rep/datafile/undotbs1.266.725017019’ to ‘+DG/rep/datafile/undotbs1.266.725017019’;

alter database rename file ‘+DATA/rep/datafile/users.268.725017033’ to ‘+DG/rep/datafile/users.268.725017033’;

alter database rename file ‘+DATA/rep/datafile/mgmt_ecm_depot_ts.284.725021583’ to ‘+DG/rep/datafile/mgmt_ecm_depot_ts.284.725021583’;

alter database rename file ‘+DATA/rep/datafile/mgmt_tablespace.285.725021587’ to ‘+DG/rep/datafile/mgmt_tablespace.285.725021587’;

alter database rename file ‘+DATA/rep/datafile/mgmt_ad4j_ts.286.725021595’ to ‘+DG/rep/datafile/mgmt_ad4j_ts.286.725021595’;

alter database rename file ‘+DATA/rep/onlinelog/group_1.258.725016991’ to ‘+DG/rep/onlinelog/group_1.258.725016991’;

alter database rename file ‘+DATA/rep/onlinelog/group_1.259.725016991’ to ‘+DG/rep/onlinelog/group_1.259.725016991’;

alter database rename file ‘+DATA/rep/onlinelog/group_2.260.725016993’ to ‘+DG/rep/onlinelog/group_2.260.725016993’;

alter database rename file ‘+DATA/rep/onlinelog/group_2.261.725016993’ to ‘+DG/rep/onlinelog/group_2.261.725016993’;

alter database rename file ‘+DATA/rep/onlinelog/group_3.262.725016993’ to ‘+DG/rep/onlinelog/group_3.262.725016993’;

alter database rename file ‘+DATA/rep/onlinelog/group_3.263.725016995’ to ‘+DG/rep/onlinelog/group_3.263.725016995’;

[oracle@oel55 tmp]$

Create a pfile in /tmp for each of the databases on DATA disk group.

Make sure that data disk group is not mounted prior to the rename to avoid error KFNDG-00405: file not found; arguments: [DATA]. Start the rename.

[oracle@oel55 ~]$ renamedg dgname=data newdgname=dg asm_diskstring=’/dev/oracleasm/disks/*’ verbose=true

NOTE: No asm libraries found in the system

Parsing parameters..

Parameters in effect:

Old DG name : DATA

New DG name : DG

Phases :

Phase 1

Phase 2

Discovery str : /dev/oracleasm/disks/*

Clean : TRUE

Raw only : TRUE

renamedg operation: dgname=data newdgname=dg asm_diskstring=/dev/oracleasm/disks/* verbose=true

Executing phase 1

Discovering the group

Performing discovery with string:/dev/oracleasm/disks/*

Identified disk UFS:/dev/oracleasm/disks/DISK1 with disk number:0 and timestamp (32939720 -1287618560)

Identified disk UFS:/dev/oracleasm/disks/DISK2 with disk number:1 and timestamp (32939720 -1287618560)

Identified disk UFS:/dev/oracleasm/disks/DISK3 with disk number:2 and timestamp (32939720 -1287618560)

Checking for hearbeat…

Re-discovering the group

Performing discovery with string:/dev/oracleasm/disks/*

Identified disk UFS:/dev/oracleasm/disks/DISK1 with disk number:0 and timestamp (32939720 -1287618560)

Identified disk UFS:/dev/oracleasm/disks/DISK2 with disk number:1 and timestamp (32939720 -1287618560)

Identified disk UFS:/dev/oracleasm/disks/DISK3 with disk number:2 and timestamp (32939720 -1287618560)

Checking if the diskgroup is mounted

Checking disk number:0

Checking disk number:1

Checking disk number:2

Checking if diskgroup is used by CSS

Generating configuration file..

Completed phase 1

Executing phase 2

Looking for /dev/oracleasm/disks/DISK1

Modifying the header

Looking for /dev/oracleasm/disks/DISK2

Modifying the header

Looking for /dev/oracleasm/disks/DISK3

Modifying the header

Completed phase 2

Terminating kgfd context 0xb7f48050

[oracle@oel55 ~]$

Reconfigure ASM

[oracle@oel55 ~]$ srvctl modify asm -p +DG/asm/asmparameterfile/registry.253.725013905

[oracle@oel55 ~]$

[oracle@oel55 ~]$ srvctl start asm

[oracle@oel55 ~]$ srvctl config asm

ASM home: /u01/app/11.2.0/grid

ASM listener: LISTENER

Spfile: +DG/asm/asmparameterfile/registry.253.725013905

ASM diskgroup discovery string: /dev/oracleasm/disks/*

[oracle@oel55 ~]$

Reconfigure the databases for DG

  1. Modify CRS configuration.

 

[oracle@oel55 dbs]$ srvctl modify database -d D11P -p ‘+DG/D11P/spfileD11P.ora’

[oracle@oel55 dbs]$ srvctl modify database -d D11P -a DG

[oracle@oel55 dbs]$

  1. Create a pfile in /tmp. Change the init.ora parameter to point to DG instead of DATA.
  2. Start the database from the pfile and recreate the spfile.
  3. Execute the file rename SQL script.

 

SQL> startup mount pfile=’/tmp/initD11P.ora’

ORACLE instance started.

Total System Global Area 418484224 bytes

Fixed Size 1336932 bytes

Variable Size 318769564 bytes

Database Buffers 92274688 bytes

Redo Buffers 6103040 bytes

Database mounted.

SQL> create spfile=’+DG/D11P/spfileD11P.ora’ from pfile=’/tmp/initD11P.ora’;

File created.

SQL>

  1. Modify CRS configuration.

 

[oracle@oel55 dbs]$ cat initREP.ora

SPFILE=’+DG/REP/spfileREP.ora’

[oracle@oel55 dbs]$ srvctl modify database -d REP -p +DG/REP/spfileREP.ora

[oracle@oel55 dbs]$ srvctl modify database -d REP -a DG

[oracle@oel55 dbs]$

  1. Create a pfile in /tmp. Change the init.ora parameter to point to DG instead of DATA.
  2. Start the database from the pfile and recreate the spfile.
  3. Execute the file rename SQL script.

     

 

SQL> startup pfile=’/tmp/initREP.ora’ mount

ORACLE instance started.

Total System Global Area 523108352 bytes

Fixed Size 1337632 bytes

Variable Size 444597984 bytes

Database Buffers 62914560 bytes

Redo Buffers 14258176 bytes

Database mounted.

SQL> create SPFILE=’+DG/REP/spfileREP.ora’ from pfile=’/tmp/initREP.ora’;

File created.

SQL>

  1. Modify CRS configuration.

 

[oracle@oel55 dbs]$ cat initDB11P.ora

SPFILE=’+DG/DB11P/spfileDB11P.ora’ # line added by Agent

[oracle@oel55 dbs]$ srvctl modify database -d DB11P -p +DG/DB11P/spfileDB11P.ora

[oracle@oel55 dbs]$ srvctl modify database -d DB11P -a DG

[oracle@oel55 dbs]$

  1. Create a pfile in /tmp. Change the init.ora parameter to point to DG instead of DATA.
  2. Start the database from the pfile and recreate the spfile.
  3. Execute the file rename SQL script.

     

 

SQL> startup pfile=’/tmp/initDB11P.ora’ mount

ORACLE instance started.

Total System Global Area 523108352 bytes

Fixed Size 1337632 bytes

Variable Size 398460640 bytes

Database Buffers 117440512 bytes

Redo Buffers 5869568 bytes

Database mounted.

SQL> create SPFILE=’+DG/DB11P/spfileDB11P.ora’ from pfile=’/tmp/initDB11P.ora’

2 ;

File created.

SQL>

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-38760: This database instance failed to turn on flashback database

SQL> !oerr ora 38760

38760, 00000, “This database instance failed to turn on flashback database”

// *Cause: Database flashback is on but this instance failed to

// start generating flashback data. Look in alert log for more

// specific errors.

// *Action: Correct the error or turn off database flashback.

We will set flashback off bounce the database and set flashback on to point the flashback logs to DG group.

SQL> alter database flashback off;

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 523108352 bytes

Fixed Size 1337632 bytes

Variable Size 398460640 bytes

Database Buffers 117440512 bytes

Redo Buffers 5869568 bytes

Database mounted.

Database opened.

SQL> alter database flashback on;

Database altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 523108352 bytes

Fixed Size 1337632 bytes

Variable Size 398460640 bytes

Database Buffers 117440512 bytes

Redo Buffers 5869568 bytes

Database mounted.

Database opened.

SQL>

Summary

Oracle’s 11gR2 renamedg utility can be used to rename a disk group. The disk group has to be dismounted for the rename to complete. Prior to using renamedg utility it is necessary to do some prep work to identify the resources depending on the disk group to be renamed and to point the resources to the newly named disk group.

January 11, 2010 Posted by | oracle | Leave a comment

Export Full=y fails with PLS-00201 ORA-06510 ORA-06512

Export Full=y fails with PLS-00201 ORA-06510 ORA-06512

Problem Description
In the Oracle export with FULL=Y option fails with error PLS-00201: identifier ‘SYS.DBMS_DEFER_IMPORT_INTERNAL’ must be declared. From the log file error stack is shown below.

Table DEF$_AQCALL will be exported in conventional path.

. . exporting table DEF$_AQCALL

EXP-00008: ORACLE error 6550 encountered

ORA-06550: line 1, column 18:

PLS-00201: identifier ‘SYS.DBMS_DEFER_IMPORT_INTERNAL’ must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

ORA-06512: at “SYS.DBMS_SYS_SQL”, line 1204

ORA-06512: at “SYS.DBMS_SQL”, line 323

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 97

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 126

ORA-06512: at line 1

Table DEF$_AQERROR will be exported in conventional path.

. . exporting table DEF$_AQERROR

EXP-00008: ORACLE error 6510 encountered

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 50

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 126

ORA-06512: at line 1

. . exporting table DEF$_CALLDEST

EXP-00008: ORACLE error 6550 encountered

ORA-06550: line 1, column 18:

PLS-00201: identifier ‘SYS.DBMS_DEFER_IMPORT_INTERNAL’ must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

ORA-06512: at “SYS.DBMS_SYS_SQL”, line 1204

ORA-06512: at “SYS.DBMS_SQL”, line 323

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 97

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 126

ORA-06512: at line 1

. . exporting table DEF$_DEFAULTDEST

EXP-00008: ORACLE error 6510 encountered

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 50

ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 126

ORA-06512: at line 1

. . exporting table DEF$_DESTINATION

EXP-00008: ORACLE error 6550 encountered

ORA-06550: line 1, column 18:

PLS-00201: identifier ‘SYS.DBMS_DEFER_IMPORT_INTERNAL’ must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

ORA-06512: at “SYS.DBMS_SYS_SQL”, line 1204

ORA-06512: at “SYS.DBMS_SQL”, line 323
Cause of the Problem
The error occurred during full database export operation because the user who is performing export operation does not have execute privilege on DBMS_DEFER_IMPORT_INTERNAL and DBMS_EXPORT_EXTENSION. Though the user might be a dba or sysdba user but it need explicit privilege to these two packages.

Solution of the Problem

Note that the role IMP_FULL_DATABASE, this is not enough to export FULL database schema in this case. So, it’s necessary to grant the execute right over this package directly to the user and not through a role.

The solution is,
1) Connect to database as sys user.
SQL> conn / as sysdba

2) Explicitly grant the execute privilege on DBMS_DEFER_IMPORT_INTERNAL and DBMS_EXPORT_EXTENSION to the user performing the export as shown below.

SQL> GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO <user_name>;

SQL> GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO <user_name>;

3) Run the export operation again.

January 6, 2010 Posted by | oracle | 3 Comments

Patching Oracle 11gR1 clusterware to 11.1.0.7

Download Orace patch 11.1.0.7 from Metalink and unzip into a staging area. Run OUI from the staging area by issuing the

./runInstaller

Select Next to specify the Oracle home for the clusterwatre

Select next in order to install the clusterware for the nodes.

Select Next for the OUI to perform a prerequisite checks.

Once the prerequisite check are done and there are no errors select Next to move forward into the patching process.

Select Install to start the installation.

Once installation completes you are prompted to execute as root user the following commands. These commands allow us to perform a rolling upgrade of Oracle clusterware 11g R1 to version 11.1.0.7.

This concludes the patching to Oracle clusterware to version 11.1.0.7

January 2, 2010 Posted by | oracle | Leave a comment