Gurpartap Singh's Blog: March 2014

Tuesday, March 25, 2014

Post 57 of series : Using DBRM (Database Resource Manager) on RAC 11gR2

SOOHEE, THIRD MEHL: The Dear Lord is subtle and inaccessible; how can we ever meet Him? Through the Word of the Guru's Shabad, doubt is dispelled, and the Carefree Lord comes to abide in the mind. || 1 || The Gurmukhs chant the Name of the Lord, Har, Har. I am a sacrifice to those who chant the Glorious Praises of the Lord in their minds forever. || 1 || Pause ||

Index of all the posts of Gurpartap Singh's Blog

There were many perfromance problems on the server as there were so many applications that were connecting to this clustered database.
Some people have habbit to use crazy number of parallel processes in the hint and can stall your database.

To stop this, I just thought to deploy DBRM i.e. database resource manager. Here are all the steps to deploy database resource manager DBRM.
I simulated the problem on this 2 node RAC cluster on 11gR2 and solved it using Database Resource Manager.

Create the new table:

sqlplus /

create table test as select * from dba_tables;

insert into test select * from dba_tables;

connect gurpartap/gurpartap

select /*+parallel(a,16)*/ * from ops$oracle.test a;

create user prabgun identified by prabgun;

grant dba to prabgun;

connect prabgun/prabgun

select /*+parallel(a,16)*/ * from ops$oracle.test a;

Using the following commands you can see the status of the sessions in the database:

select inst_id ins, sid, serial# ser,username, substr(osuser,1,12) osuser, substr(machine,1,25) machine,substr(module,1,15) module, substr(action,1,25) action,sql_id,

substr(event,1,20) event, seconds_in_wait siw, blocking_session ||','|| blocking_instance blocks, LAST_CALL_ET lst_cl_et, service_name srvc ,logon_time from gv$session

where wait_class!='Idle' order by inst_id,username,sql_id,event;

and

for parallel connections use:

SELECT a.qcinst_id, a.qcsid, a.qcserial# qcser, a.sid||','||a.serial# sid_serial, substr(y.osuser,1,13) osuser, y.username,

substr(y.module,1,25) module, substr(y.action,1,15) action, substr(y.machine,1,20) machine, a.degree, a.req_degree, y.inst_id,

y.sql_id, y.status, y.logon_time

FROM gv$px_session a, gv$session y

WHERE y.sid = a.sid

and y.inst_id = a.inst_id

order by a.qcsid, y.username, y.sql_id, y.inst_id, y.status

SQL> l

1 SELECT a.qcinst_id, a.qcsid, a.qcserial# qcser, a.sid||','||a.serial# sid_serial, substr(y.osuser,1,13) osuser, y.username,

2 substr(y.module,1,25) module, substr(y.action,1,15) action, substr(y.machine,1,20) machine, a.degree, a.req_degree, y.inst_id,

3 y.sql_id, y.status, y.logon_time

4 FROM gv$px_session a, gv$session y

5 WHERE y.sid = a.sid

6 and y.inst_id = a.inst_id

7* order by a.qcsid, y.username, y.sql_id, y.inst_id, y.status

SQL> /

QCINST_ID QCSID QCSER SID_SERIAL OSUSER USERNAME MODULE ACTION MACHINE DEGREE REQ_DEGREE INST_ID SQL_ID STATUS LOGON_TIM

--------- ---------- ---------- --------------- ------------ ------------------- --------------- -------------------- -------------------- ---------- ---------- ---------- ------------- -------- ---------

1 62 21 57,1455 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 34,1173 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 33,1189 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 71,2195 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 67,125 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 69,1723 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 61,707 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 65,31 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 68,1235 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

62 62,21 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 1 58xq49rpnt254 INACTIVE 17-MAR-14

1 62 21 65,255 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 55,2763 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 54,2607 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 63,1335 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 1,1775 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 51,2307 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 52,1849 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 79,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 78,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 76,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 75,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 74,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 73,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 72,33 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 77,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

64 64,1759 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 1 58xq49rpnt254 INACTIVE 17-MAR-14

1 64 1759 64,19 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 70,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 69,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 59,2023 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 60,1033 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 48,1527 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 66,11 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 67,5 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 70 755 54,479 oracle OPS$ORACLE SQL*Plus rac70.rac.meditate.c 2 2 1 50nywwwt9tg51 ACTIVE 17-MAR-14

70 70,755 oracle OPS$ORACLE SQL*Plus rac70.rac.meditate.c 1 50nywwwt9tg51 ACTIVE 17-MAR-14

1 70 755 71,5 oracle OPS$ORACLE SQL*Plus rac70.rac.meditate.c 2 2 2 50nywwwt9tg51 ACTIVE 17-MAR-14

37 rows selected.

SQL>

SQL> select sql_text from v$sql where sql_text='58xq49rpnt254';

select /*+parallel(a,16)*/ * from ops$oracle.test a;

SQL>

22:01 : oracle : rac70.rac.meditate.com : @SDIEGO1 : /u01/app/oracle/diag/rdbms/prabgun/PRABGUN1/trace

$ top

top - 22:01:57 up 4 days, 10:09, 4 users, load average: 14.42, 5.38, 2.22

Tasks: 298 total, 3 running, 295 sleeping, 0 stopped, 0 zombie

Cpu(s): 4.2%us, 3.4%sy, 0.0%ni, 88.6%id, 2.1%wa, 0.7%hi, 0.9%si, 0.0%st

Mem: 1491784k total, 1424300k used, 67484k free, 2052k buffers

Swap: 5242872k total, 1490988k used, 3751884k free, 497652k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

3538 oracle RT 0 692m 151m 80m S 27.6 10.4 49:03.84 ocssd.bin

21730 oracle 20 0 97.8m 3316 732 S 24.0 0.2 0:33.68 sshd

21337 oracle 20 0 99744 2864 732 S 7.4 0.2 0:30.24 sshd

23258 oracle 20 0 71452 2764 1796 R 7.4 0.2 1:55.66 sqlplus

23901 oracle 20 0 71452 5872 2064 R 7.4 0.4 1:16.43 sqlplus

28 root 20 0 0 0 0 D 1.8 0.0 1:29.87 kswapd0

3480 oracle 20 0 546m 26m 13m S 1.8 1.8 97:33.24 gipcd.bin

4383 root 20 0 711m 29m 17m S 1.8 2.0 10:04.62 orarootagent.bi

4396 root 20 0 661m 18m 13m S 1.8 1.2 20:34.67 octssd.bin

5049 oracle -2 0 1369m 4524 4348 S 1.8 0.3 91:33.19 asm_vktm_+asm1

5059 oracle 20 0 1375m 7896 7424 S 1.8 0.5 5:21.50 asm_diag_+asm1

5067 oracle 20 0 1384m 11m 5884 S 1.8 0.8 16:11.33 asm_lmd0_+asm1

6313 root 20 0 788m 37m 21m S 1.8 2.5 26:28.04 crsd.bin

6435 root 20 0 695m 17m 12m S 1.8 1.2 56:48.63 orarootagent.bi

7133 oracle -2 0 614m 34m 27m S 1.8 2.4 47:09.21 oracle

23745 oracle 20 0 601m 25m 21m S 1.8 1.7 0:00.06 oracle

24169 oracle 20 0 603m 40m 34m D 1.8 2.8 0:00.13 oracle

24390 oracle 20 0 15164 1280 844 R 1.8 0.1 0:00.01 top

1 root 20 0 19360 1064 888 S 0.0 0.1 0:04.43 init

22:01 : oracle : rac70.rac.meditate.com : @SDIEGO1 : /u01/app/oracle/diag/rdbms/prabgun/PRABGUN1/trace

Got error: Out of memeory

Normal CPU usage is like:

$ top

top - 23:39:58 up 4 days, 11:47, 3 users, load average: 0.46, 0.28, 0.61

Tasks: 328 total, 2 running, 326 sleeping, 0 stopped, 0 zombie

Cpu(s): 4.3%us, 3.4%sy, 0.0%ni, 87.9%id, 2.8%wa, 0.7%hi, 0.9%si, 0.0%st

Mem: 1491784k total, 1404692k used, 87092k free, 7020k buffers

Swap: 5242872k total, 1532504k used, 3710368k free, 566896k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

7 root 20 0 0 0 0 R 1.9 0.0 21:01.73 events/0

3480 oracle 20 0 546m 26m 13m S 1.9 1.8 99:00.37 gipcd.bin

5090 oracle 20 0 1372m 10m 10m S 1.9 0.7 4:59.68 asm_rbal_+asm1

6435 root 20 0 695m 16m 12m S 1.9 1.2 57:30.74 orarootagent.bi

23740 oracle 20 0 601m 8148 7660 S 1.9 0.5 0:00.37 oracle

37832 oracle -2 0 599m 3820 3560 S 1.9 0.3 17:31.66 oracle

1 root 20 0 19360 1064 888 S 0.0 0.1 0:04.50 init

2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd

3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0

4 root 20 0 0 0 0 S 0.0 0.0 0:17.51 ksoftirqd/0

5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0

6 root RT 0 0 0 0 S 0.0 0.0 0:01.30 watchdog/0

8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 cgroup

9 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khelper

10 root 20 0 0 0 0 S 0.0 0.0 0:00.00 netns

11 root 20 0 0 0 0 S 0.0 0.0 0:00.00 async/mgr

12 root 20 0 0 0 0 S 0.0 0.0 0:00.00 pm

13 root 20 0 0 0 0 S 0.0 0.0 0:00.90 sync_supers

14 root 20 0 0 0 0 S 0.0 0.0 0:01.28 bdi-default

15 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kintegrityd/0

16 root 20 0 0 0 0 S 0.0 0.0 1:55.81 kblockd/0

So, we need to make some arrangements so that out server doesn't run out of resoruces.

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

We will use DBRM i.e. (Database resource manager)

The database parameters that we should set for DBRM are:

db_writer_processes

pga_aggregate_target

parallel_degree_policy

parallel_max_servers

parallel_min_servers

parallel_servers_target

parallel_adaptive_multi_user

optimizer_dynamic_sampling

Lets see the settings of these parameters on our database using the following query:

set line 300

set pages 100

col VALUE format a50

select INST_ID,NAME,VALUE from gv$parameter

where lower(NAME) in (

'db_writer_processes',

'pga_aggregate_target',

'parallel_degree_policy',

'parallel_max_servers',

'parallel_min_servers',

'parallel_servers_target',

'parallel_adaptive_multi_user',

'optimizer_dynamic_sampling'

)

order by 2;

Here is the output:

SQL> set line 300

set pages 100

SQL> SQL> col VALUE format a50

SQL> select INST_ID,NAME,VALUE from gv$parameter

2 where lower(NAME) in (

3 'db_writer_processes',

4 'pga_aggregate_target',

5 'parallel_degree_policy',

6 'parallel_max_servers',

7 'parallel_min_servers',

8 'parallel_servers_target',

9 'parallel_adaptive_multi_user',

10 'optimizer_dynamic_sampling'

11 )

12 order by 2;

INST_ID NAME VALUE

---------- -------------------------------------------------------------------------------- --------------------------------------------------

1 db_writer_processes 1

2 db_writer_processes 1

1 optimizer_dynamic_sampling 2

2 optimizer_dynamic_sampling 2

2 parallel_adaptive_multi_user TRUE

1 parallel_adaptive_multi_user TRUE

1 parallel_degree_policy MANUAL

2 parallel_degree_policy MANUAL

2 parallel_max_servers 40

1 parallel_max_servers 40

1 parallel_min_servers 0

2 parallel_min_servers 0

1 parallel_servers_target 16

2 parallel_servers_target 16

2 pga_aggregate_target 52428800

1 pga_aggregate_target 52428800

16 rows selected.

SQL>

DBRM

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

--Parameters for DBRM Plan

Read more about db_writer_processes in the following article. I will keep the default value of 1.

http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams072.htm

Read more about optimizer_dynamic_sampling in the following article. I will keep the default value of 2.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams141.htm

Read more about optimizer_dynamic_sampling in the following article. I will change the default value from TRUE to FALSE as we will

use DBRM to control degree of parallelism.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams149.htm

Execute following as a DBA user:

alter system set parallel_adaptive_multi_user=FALSE scope=both;

Output is as:

SQL> alter system set parallel_adaptive_multi_user=FALSE scope=both;

System altered.

SQL>

Read more about parallel_degree_policy in the following article. I will change the default value from MANUAL to AUTO.

http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10310.htm

Execute the following as DBA user:

alter system set parallel_degree_policy=AUTO scope=both sid='*';

Output is as:

SQL> alter system set parallel_degree_policy=AUTO scope=both sid='*';

System altered.

SQL>

Read more about parallel_max_servers in the following article. I will change the default value from 40 to 68.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams153.htm

Execute the following as a DBA user:

alter system set parallel_max_servers=68 scope=both sid='*';

Output is as:

SQL> alter system set parallel_max_servers=68 scope=both sid='*';

System altered.

SQL>

Read more about parallel_min_servers in the following article. I will change the default value from 0 to 68 i.e. (max_parallel_servers).

http://docs.oracle.com/cd/B12037_01/server.101/b10755/initparams155.htm

Execute the following asd a DBA user:

alter system set parallel_min_servers=68 scope=both sid='*';

Output is as:

SQL> alter system set parallel_min_servers=68 scope=both sid='*';

System altered.

SQL>

Read more about parallel_servers_target in the following article. I will change the default value from 16 to 68 i.e. (max_parallel_servers).

http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10314.htm

alter system set parallel_servers_target=68 scope=both sid='*';

Output is as:

SQL> alter system set parallel_servers_target=68 scope=both sid='*';

System altered.

SQL>

Read more about pga_aggregate_target in the following article. I will keep the default value of 50mb due to memmory constraints.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams157.htm

Lets see out parameter settings now using the follwing query as:

set line 300

set pages 100

col VALUE format a50

select INST_ID,NAME,VALUE from gv$parameter

where lower(NAME) in (

'db_writer_processes',

'pga_aggregate_target',

'parallel_degree_policy',

'parallel_max_servers',

'parallel_min_servers',

'parallel_servers_target',

'parallel_adaptive_multi_user',

'optimizer_dynamic_sampling'

)

order by 2;

Output is as:

SQL> set line 300

set pages 100

col VALUE format a50

select INST_ID,NAME,VALUE from gv$parameter

where lower(NAME) in (

'db_writer_processes',

'pga_aggregate_target',

'parallel_degree_policy',

'parallel_max_servers',

'parallel_min_servers',

'parallel_servers_target',

'parallel_adaptive_multi_user',

'optimizer_dynamic_sampling'

)

SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 order by 2;

INST_ID NAME VALUE

---------- -------------------------------------------------------------------------------- --------------------------------------------------

2 db_writer_processes 1

1 db_writer_processes 1

2 optimizer_dynamic_sampling 2

1 optimizer_dynamic_sampling 2

1 parallel_adaptive_multi_user FALSE

2 parallel_adaptive_multi_user FALSE

2 parallel_degree_policy AUTO

1 parallel_degree_policy AUTO

1 parallel_max_servers 68

2 parallel_max_servers 68

2 parallel_min_servers 68

1 parallel_min_servers 68

2 parallel_servers_target 68

1 parallel_servers_target 68

1 pga_aggregate_target 52428800

2 pga_aggregate_target 52428800

16 rows selected.

SQL>

Now, lets define the consumer groups for our system:

I will have:

1. DOP with Very High Importance

2. DOP with High Importance

3. DOP with Medium Importance

4. DOP with Low Importance

5. DOP with Very Low Importance

Lets Define these Counsumer Groups in the database witht eh following procedure:

BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

CONSUMER_GROUP => 'DOP_VHI',

COMMENT => 'DOP Very High Importance');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

CONSUMER_GROUP => 'DOP_HI',

COMMENT => 'DOP High Importance');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

CONSUMER_GROUP => 'DOP_MI',

COMMENT => 'DOP Medium Importance');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

CONSUMER_GROUP => 'DOP_LI',

COMMENT => 'DOP Low Importance');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

CONSUMER_GROUP => 'DOP_VLI',

COMMENT => 'DOP Very Low Importance');

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Output is as:

SQL> BEGIN

2 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

3 4

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

5 6 CONSUMER_GROUP => 'DOP_VHI',

7 COMMENT => 'DOP Very High Importance');

9 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

10 CONSUMER_GROUP => 'DOP_HI',

11 COMMENT => 'DOP High Importance');

13 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

14 CONSUMER_GROUP => 'DOP_MI',

15 COMMENT => 'DOP Medium Importance');

17 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

18 CONSUMER_GROUP => 'DOP_LI',

19 COMMENT => 'DOP Low Importance');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

20 21 22 CONSUMER_GROUP => 'DOP_VLI',

23 COMMENT => 'DOP Very Low Importance');

25 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

27 END;

28 /

PL/SQL procedure successfully completed.

SQL>

Now lets create a plan using teh following procedure:

BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

-- Creates Main Plan

DBMS_RESOURCE_MANAGER.CREATE_PLAN(

PLAN => 'HOME_DBRM_PLAN',

COMMENT => 'HOME DBRM PLAN');

-- Creates Plan Directive for sub Groups or subplan:

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'SYS_GROUP',

COMMENT=> 'Directive for SYS_GROUP',

MGMT_P1 => 30);

--Priority Group - Reserved for jobs where needed. (Service: OLTPsv1)

--14% of 68 is PARALLEL_TARGET_PERCENTAGE

-- mgmt_p1 is teh priority given to this "p1" is the heigest importance

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'DOP_VHI',

COMMENT=> 'Directive for P1_VERY_HIGH_IMPORTANCE',

parallel_degree_limit_p1 => 8,

PARALLEL_TARGET_PERCENTAGE=>14,

mgmt_p1 => 70);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'DOP_HI',

COMMENT=> 'Directive for P2_HIGH_IMPORTANCE',

parallel_degree_limit_p1 => 6,

PARALLEL_TARGET_PERCENTAGE=>25,

mgmt_p2 => 50

);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'DOP_MI',

COMMENT=> 'Directive for P3_MEDIUM_IMPORTANCE',

parallel_degree_limit_p1 => 6,

PARALLEL_TARGET_PERCENTAGE=>30,

mgmt_p3 => 60

);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'DOP_LI',

COMMENT=> 'Directive for P4_LOW_IMPORTANCE',

parallel_degree_limit_p1 => 4,

PARALLEL_TARGET_PERCENTAGE=>25,

mgmt_p4 => 70

);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'DOP_VLI',

COMMENT=> 'Directive for P5_VEY_LOW_IMPORANCE',

parallel_degree_limit_p1 => 2,

PARALLEL_TARGET_PERCENTAGE=>25,

mgmt_p5 => 100

);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'OTHER_GROUPS',

COMMENT=> 'Directive for P6_ALL_OTHER_GROUPS',

parallel_degree_limit_p1 => 2,

PARALLEL_TARGET_PERCENTAGE=>20,

mgmt_p6 => 100

);

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

Output is as:

SQL> BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

2 3 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

5 -- Creates Main Plan

6 DBMS_RESOURCE_MANAGER.CREATE_PLAN(

7 PLAN => 'HOME_DBRM_PLAN',

8 COMMENT => 'HOME DBRM PLAN');

10 -- Creates Plan Directive for sub Groups or subplan:

11 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

12 PLAN => 'HOME_DBRM_PLAN',

13 GROUP_OR_SUBPLAN => 'SYS_GROUP',

14 COMMENT=> 'Directive for SYS_GROUP',

15 MGMT_P1 => 30);

16 17 --Priority Group - Reserved for jobs where needed. (Service: OLTPsv1)

18 --14% of 68 is PARALLEL_TARGET_PERCENTAGE

19 -- mgmt_p1 is teh priority given to this "p1" is the heigest importance

20 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

21 PLAN => 'HOME_DBRM_PLAN',

22 GROUP_OR_SUBPLAN => 'DOP_VHI',

23 COMMENT=> 'Directive for P1_VERY_HIGH_IMPORTANCE',

24 parallel_degree_limit_p1 => 8,

25 PARALLEL_TARGET_PERCENTAGE=>14,

26 mgmt_p1 => 70);

28 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

29 PLAN => 'HOME_DBRM_PLAN',

30 GROUP_OR_SUBPLAN => 'DOP_HI',

COMMENT=> 'Directive for P2_HIGH_IMPORTANCE',

31 32 parallel_degree_limit_p1 => 6,

33 PARALLEL_TARGET_PERCENTAGE=>25,

34 mgmt_p2 => 50

35 );

37 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

38 PLAN => 'HOME_DBRM_PLAN',

39 GROUP_OR_SUBPLAN => 'DOP_MI',

40 COMMENT=> 'Directive for P3_MEDIUM_IMPORTANCE',

41 parallel_degree_limit_p1 => 6,

42 PARALLEL_TARGET_PERCENTAGE=>30,

mgmt_p3 => 60

43 44 );

46 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

47 PLAN => 'HOME_DBRM_PLAN',

48 GROUP_OR_SUBPLAN => 'DOP_LI',

49 COMMENT=> 'Directive for P4_LOW_IMPORTANCE',

50 parallel_degree_limit_p1 => 4,

51 PARALLEL_TARGET_PERCENTAGE=>25,

52 mgmt_p4 => 70

53 );

55 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

56 PLAN => 'HOME_DBRM_PLAN',

57 GROUP_OR_SUBPLAN => 'DOP_VLI',

58 COMMENT=> 'Directive for P5_VEY_LOW_IMPORANCE',

parallel_degree_limit_p1 => 2,

59 60 PARALLEL_TARGET_PERCENTAGE=>25,

61 mgmt_p5 => 100

62 );

64 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

65 PLAN => 'HOME_DBRM_PLAN',

66 GROUP_OR_SUBPLAN => 'OTHER_GROUPS',

67 COMMENT=> 'Directive for P6_ALL_OTHER_GROUPS',

68 parallel_degree_limit_p1 => 2,

69 PARALLEL_TARGET_PERCENTAGE=>20,

70 mgmt_p6 => 100

71 );

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

73 74 END;

75 /

PL/SQL procedure successfully completed.

SQL> SQL>

Now, lets map the services of the database with the Consumer Groups we greated earlier.

Now, lets create some services :

srvctl add service -d SDIEGO -s OLTPsv1 -r SDIEGO1 -a SDIEGO2

srvctl add service -d SDIEGO -s OLTPsvb2 -r SDIEGO2 -a SDIEGO1

srvctl add service -d SDIEGO -s OLTPsvb1 -r SDIEGO2 -a SDIEGO1

srvctl add service -d SDIEGO -s HOMEsv1 -r SDIEGO1 -a SDIEGO2

srvctl add service -d SDIEGO -s HOMEsv2 -r SDIEGO2 -a SDIEGO1

After executing, lets check the services:

SQL> select SERVICE_ID,NAME,NAME_HASH from dba_services

2 ;

SERVICE_ID NAME NAME_HASH

---------- ---------------------------------------------------------------- ----------

1 SYS$BACKGROUND 165959219

2 SYS$USERS 3427055676

3 SDIEGOsv1 274715180

4 SDIEGO.rac.meditate.com 3539745710

5 SDIEGOXDB 2453147176

6 SDIEGO 2193528589

7 HOMEsv2 110420717

8 OLTPsvb1 385981247

9 OLTPsvb2 194003067

10 HOMEsv1 1644361115

11 OLTPsv1 556876437

11 rows selected.

SQL>

create user simar identified by simar;

create user SDIO identified by sdio;

grant dba to simar;

grant dba to sdio;

BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

begin

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsv1',CONSUMER_GROUP => 'DOP_VHI');

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsvb2',CONSUMER_GROUP => 'DOP_HI');

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsvb1',CONSUMER_GROUP => 'DOP_MI');

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'HOMEsv2',CONSUMER_GROUP => 'DOP_LI');

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'HOMEsv2',CONSUMER_GROUP => 'DOP_VLI');

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('GURPARTAP','DOP_MI',TRUE);

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('PRABGUN','DOP_LI',TRUE);

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SIMAR','DOP_LI',TRUE);

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SDIO','DOP_VLI',TRUE);

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

end;

Output is as:

SQL> BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

2 3 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

5 begin

6 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

7 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

8 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsv1',CONSUMER_GROUP => 'DOP_VHI');

9 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsvb2',CONSUMER_GROUP => 'DOP_HI');

10 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsvb1',CONSUMER_GROUP => 'DOP_MI');

11 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'HOMEsv2',CONSUMER_GROUP => 'DOP_LI');

12 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'HOMEsv2',CONSUMER_GROUP => 'DOP_VLI');

14 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('GURPARTAP','DOP_MI',TRUE);

15 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('PRABGUN','DOP_LI',TRUE);

16 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SIMAR','DOP_LI',TRUE);

17 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SDIO','DOP_VLI',TRUE);

18 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

19 END;

20 end;

21 /

PL/SQL procedure successfully completed.

SQL>

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:HOME_DBRM_PLAN' SID = '*' SCOPE = BOTH;

Now you run the parallel commands and you will see the difference.

Here are some of the views and commands to look at the configuration of your DBRM (Database resource Manager)

To see the details of the directives that have been configured:
select * from DBA_RSRC_PLAN_DIRECTIVES where plan='HOME_DBRM_PLAN';

To see the CPU's capable
select * from DBA_RSRC_CAPABILITY;

SQL> show parameter resource_manager_plan

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string FORCE:HOME_DBRM_PLAN
SQL>

Check all the categories that we have defined use the following:
select * from DBA_RSRC_CATEGORIES;

Result is:
SQL> select * from DBA_RSRC_CATEGORIES;

NAME COMMENTS STATU MAN
------------------------------ ---------------------------------------- ----- ---
ADMINISTRATIVE Administrative Consumer Groups NO
INTERACTIVE Interactive, OLTP Consumer Groups NO
BATCH Batch, Non-Interactive Consumer Groups NO
MAINTENANCE Maintenance Consumer Groups NO
OTHER Unclassified Consumer Groups YES

SQL>

To see how many CPU's DBRM can leverage use:
select * from DBA_RSRC_CAPABILITY;

SQL> select * from DBA_RSRC_CAPABILITY;

CPU_CAPABLE IO_CAPABLE STATU
----------- ------------------------------ -----
1

SQL>

Select the consumer groups that we have defined in the active plan:

select *
from DBA_RSRC_CONSUMER_GROUPS
where CONSUMER_GROUP in
(select GROUP_OR_SUBPLAN from DBA_RSRC_PLAN_DIRECTIVES where PLAN='HOME_DBRM_PLAN' );

In the following:

CPU_P1 is First Parameter for the CPU resource allocation method
CPU_P2 is Second Parameter for the CPU resource allocation method
and so on

and

MGMT_P1 is First parameter for the resource allocation method
MGMT_P2 is Second parameter for the resource allocation method

While creating the plan directive we didn't define "CPU resource allocation method", it picked the value of
co-responding MGMT_P. That is why the values of CPU_P1 and MGMT_P1 are same and so on.

set line 300
set pages 100
col CPU_P1 format 999
col CPU_P2 format 999
col CPU_P3 format 999
col CPU_P4 format 999
col CPU_P5 format 999
col CPU_P6 format 999
col CPU_P7 format 999
col CPU_P8 format 999
col MGMT_P1 format 999
col MGMT_P2 format 999
col MGMT_P3 format 999
col MGMT_P4 format 999
col MGMT_P5 format 999
col MGMT_P6 format 999
col MGMT_P7 format 999
col MGMT_P8 format 999
col GROUP_OR_SUBPLAN format a15
col COMMENTS format a40
col status format a5
select PLAN,GROUP_OR_SUBPLAN,TYPE,CPU_P1,CPU_P2,CPU_P3,CPU_P4,CPU_P5,
CPU_P6,CPU_P7,CPU_P8,MGMT_P1,MGMT_P2,MGMT_P3,MGMT_P4,MGMT_P5,MGMT_P6,
MGMT_P7,MGMT_P8,COMMENTS,STATUS,MANDATORY
from DBA_RSRC_PLAN_DIRECTIVES
where PLAN='HOME_DBRM_PLAN' ;

Output is as:

SQL> l
1 select PLAN,GROUP_OR_SUBPLAN,TYPE,CPU_P1,CPU_P2,CPU_P3,CPU_P4,CPU_P5,
2 CPU_P6,CPU_P7,CPU_P8,MGMT_P1,MGMT_P2,MGMT_P3,MGMT_P4,MGMT_P5,MGMT_P6,
3 MGMT_P7,MGMT_P8,COMMENTS,STATUS,MANDATORY
4 from DBA_RSRC_PLAN_DIRECTIVES
5* where PLAN='HOME_DBRM_PLAN'
SQL> /

PLAN GROUP_OR_SUBPLA TYPE CPU_P1 CPU_P2 CPU_P3 CPU_P4 CPU_P5 CPU_P6 CPU_P7 CPU_P8 MGMT_P1 MGMT_P2 MGMT_P3 MGMT_P4 MGMT_P5 MGMT_P6 MGMT_P7 MGMT_P8 COMMENTS STATU MAN
------------------------------ --------------- -------------- ------ ------ ------ ------ ------ ------ ------ ------ ------- ------- ------- ------- ------- ------- ------- ------- ---------------------------------------- ----- ---
HOME_DBRM_PLAN SYS_GROUP CONSUMER_GROUP 30 0 0 0 0 0 0 0 30 0 0 0 0 0 0 0 Directive for SYS_GROUP NO
HOME_DBRM_PLAN DOP_VHI CONSUMER_GROUP 70 0 0 0 0 0 0 0 70 0 0 0 0 0 0 0 Directive for P1_VERY_HIGH_IMPORTANCE NO
HOME_DBRM_PLAN DOP_HI CONSUMER_GROUP 0 50 0 0 0 0 0 0 0 50 0 0 0 0 0 0 Directive for P2_HIGH_IMPORTANCE NO
HOME_DBRM_PLAN DOP_MI CONSUMER_GROUP 0 0 60 0 0 0 0 0 0 0 60 0 0 0 0 0 Directive for P3_MEDIUM_IMPORTANCE NO
HOME_DBRM_PLAN DOP_LI CONSUMER_GROUP 0 0 0 70 0 0 0 0 0 0 0 70 0 0 0 0 Directive for P4_LOW_IMPORTANCE NO
HOME_DBRM_PLAN DOP_VLI CONSUMER_GROUP 0 0 0 0 100 0 0 0 0 0 0 0 100 0 0 0 Directive for P5_VEY_LOW_IMPORANCE NO
HOME_DBRM_PLAN OTHER_GROUPS CONSUMER_GROUP 0 0 0 0 0 100 0 0 0 0 0 0 0 100 0 0 Directive for P6_ALL_OTHER_GROUPS NO

7 rows selected.

SQL>

To check which user is granted which group use:
select * from DBA_RSRC_CONSUMER_GROUP_PRIVS;

Output is as:

SQL> l
1* select * from DBA_RSRC_CONSUMER_GROUP_PRIVS
SQL> /

GRANTEE GRANTED_GROUP GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC DEFAULT_CONSUMER_GROUP YES YES
PRABGUN DOP_LI YES NO
SIMAR DOP_LI YES NO
SYSTEM SYS_GROUP NO YES
PUBLIC LOW_GROUP NO NO
SDIO DOP_VLI YES NO
GURPARTAP DOP_MI YES NO

7 rows selected.

SQL>

Check which service is given which consumer group use:

set line 300
select * from DBA_RSRC_GROUP_MAPPINGS;

Output is as:

SQL> l
1* select * from DBA_RSRC_GROUP_MAPPINGS
SQL> /

ATTRIBUTE VALUE CONSUMER_GROUP STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ------------------------------
SERVICE_NAME OLTPSVB2 DOP_HI
SERVICE_NAME OLTPSVB1 DOP_MI
SERVICE_NAME OLTPSV1 DOP_VHI
SERVICE_NAME HOMESV2 DOP_VLI
ORACLE_USER SYS SYS_GROUP
ORACLE_USER SYSTEM SYS_GROUP
ORACLE_FUNCTION BACKUP BATCH_GROUP
ORACLE_FUNCTION COPY BATCH_GROUP
ORACLE_FUNCTION DATALOAD ETL_GROUP

9 rows selected.

SQL>

To see your Calibration results check the following view:
select * from DBA_RSRC_IO_CALIBRATE;

To see which role in the database has privilege "" use:
select * from DBA_RSRC_MANAGER_SYSTEM_PRIVS;

Output is as:

SQL> select * from DBA_RSRC_MANAGER_SYSTEM_PRIVS;

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA ADMINISTER RESOURCE MANAGER YES
EXP_FULL_DATABASE ADMINISTER RESOURCE MANAGER NO
IMP_FULL_DATABASE ADMINISTER RESOURCE MANAGER NO
APPQOSSYS ADMINISTER RESOURCE MANAGER NO
SYS ADMINISTER RESOURCE MANAGER NO

SQL>

To see all the resource plan's defined in the database use:
select * from DBA_RSRC_PLANS

MARU, SOLAHAS, FIFTH MEHL:

ONE UNIVERSAL CREATOR GOD. BY THE GRACE OF THE TRUE GURU:

The Primal, Immaculate Lord God is formless. The Detached Lord is Himself prevailing in all. He has no race or social class, no identifying mark. By the Hukam of His Will, He created the entire universe. || 1 || Out of all the 8.4 million species of be

ings, God blessed mankind with glory. That human who misses this chance, shall suffer the pains of coming and going in

reincarnation.

Monday, March 17, 2014

Post 56 of series: Using DBMS_FILE_TRANSFER.put_file to clone the database on ASM

BILAAVAL, FIRST MEHL:

My mind is filled with such a great joy; I have blossomed forth in Truth. I am enticed by the love of my Husband Lord, the Eternal, Imperishable Lord God. The Lord is everlasting, the Master of masters. Whatever He wills, happens. O Great Giver, You are always kind and compassionate. You infuse life into all living beings. I have no other spiritual wisdom, meditation or worship; the Name of the Lord alone dwells deep within me. I know nothing about religious robes, pilgrimages or stubborn fanaticism; O Nanak, I hold tight to the Truth. || 1 ||

Index of all the posts of Gurpartap Singh's Blog

I had a problem where I was dealing witha multiple terabyte database on RAC and couple of years ago the DBA added the file to the tablespace in ASM diskgroup but the file file was added with '+' sign in front of the diskgroup name. So, the file got create in $ORACLE_HOME/dbs. Instead of moving that file to ASM and recovering it, the file was "offline dropped". Now after few years, we need to move this database to another machine and we don't have the storage for RMAN backups for this monster size database. This problematic tablespace belongs to the important tablespace that cannot be skipped. We were provided with very fast network only and no space for storing backup or low level clone. With RMAN active clone, I got the following error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/13/2014 16:21:14
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01152: file 4 was not restored from a sufficiently old backup
ORA-01110: data file 4: '+DATA/prabgun/datafile/users.278.842113199'

RMAN active clone failed on the missing file as we cannot use the "skip tablespace" feature as it's one of the
most important tablespace. import export of ths huge database was not an option. On source and target side
we had grid infrastructure installed with one Oracle database up and running on it. On source i.e. teh database we want to move was "SIMAR" on target the database that was running was "SDIEGO". I used the following oracle package to do this clone:

DBMS_FILE_TRANSFER.put_file

Here are the detail steps:

-- Login to the target server (where you want to make the clone).
CONN system/gurpartap

-- Create the destination directory object.
CREATE OR REPLACE DIRECTORY db_files_dir2 AS '+DATA';

-- Login to the local server.

Add the following entry in tnsnames.ora:

TARGET =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac70.rac.meditate.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SDIEGO.rac.meditate.com)
)
)

Now connect to the database as:
CONN system/gurpartap@local

-- Create the source directory object, database link.
CREATE OR REPLACE DIRECTORY db_files_dir1 AS '+DATA/SIMAR/DATAFILE';
CREATE DATABASE SDIEGO target CONNECT TO system IDENTIFIED BY gurpartap USING 'TARGET';

Now I see on this database as stated in the problem, we have a missing data file so make it offline.
This file was created in tablespace USERS with '+' in from of the ASM diskgroup and so got created in the
ORACLE_HOME. So, the mistake that was done couple of years ago was that instead of moning this file to ASM,
it was offline dropped. I will re-do offline drop again, just to make sure as:

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/simar/datafile/ggdata.274.839364817
+DATA/simar/datafile/data_encrypt.273.839255003
+DATA/simar/datafile/data_encrypt.270.839255001
+DATA/simar/datafile/data_encrypt.264.839254791
+DATA/simar/datafile/goldengate.258.839254791
+DATA/simar/datafile/undotbs2.265.839254793
+DATA/simar/datafile/example.257.839254791
+DATA/simar/datafile/users.269.839254793
+DATA/simar/datafile/undotbs1.259.839254791
+DATA/simar/datafile/sysaux.256.839254791
+DATA/simar/datafile/system.271.839254791

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.3/dbs/MISSING00012

12 rows selected.

SQL> alter database datafile '/u01/app/oracle/product/11.2.0.3/dbs/MISSING00012' offline drop;

Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/simar/datafile/ggdata.274.839364817
+DATA/simar/datafile/data_encrypt.273.839255003
+DATA/simar/datafile/data_encrypt.270.839255001
+DATA/simar/datafile/data_encrypt.264.839254791
+DATA/simar/datafile/goldengate.258.839254791
+DATA/simar/datafile/undotbs2.265.839254793
+DATA/simar/datafile/example.257.839254791
+DATA/simar/datafile/users.269.839254793
+DATA/simar/datafile/undotbs1.259.839254791
+DATA/simar/datafile/sysaux.256.839254791
+DATA/simar/datafile/system.271.839254791

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.3/dbs/MISSING00012

12 rows selected.

SQL>

Now before moving the datafiles place the database in backup mode as:

alter database begin backup;

Log is as:
SQL> alter database begin backup;

Database altered.

Now lets move the files to the target ASM using the following script:

BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'USERS.269.839254793', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'USERS.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.264.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.264.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.270.839255001', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.270.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.273.839255003', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.273.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'EXAMPLE.257.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'EXAMPLE.257.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'GGDATA.274.839364817', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'GGDATA.274.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'GOLDENGATE.258.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'GOLDENGATE.258.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'SYSAUX.256.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'SYSAUX.256.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'SYSTEM.271.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'SYSTEM.271.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'UNDOTBS1.259.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'UNDOTBS1.259.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'UNDOTBS2.265.839254793', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'UNDOTBS2.265.transfer.dbf', destination_database => 'SDIEGO'); END;
/

Log is as:

SQL> BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'USERS.269.839254793', destination_directory_obj ect => 'DB_FILES_DIR2', destination_file_name => 'USERS.transfer.dbf', destination_database => 'SDIEGO'); END;
/
2
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.264.839254791', destination_directory_o bject => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.264.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.270.839255001', destination_directory_o bject => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.270.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'DATA_ENCRYPT.273.839255003', destination_directory_o bject => 'DB_FILES_DIR2', destination_file_name => 'DATA_ENCRYPT.273.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'EXAMPLE.257.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'EXAMPLE.257.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'GGDATA.274.839364817', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'GGDATA.274.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'GOLDENGATE.258.839254791', destination_directory_obj ect => 'DB_FILES_DIR2', destination_file_name => 'GOLDENGATE.258.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'SYSAUX.256.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'SYSAUX.256.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'SYSTEM.271.839254791', destination_directory_object => 'DB_FILES_DIR2', destination_file_name => 'SYSTEM.271.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'UNDOTBS1.259.839254791', destination_directory_objec t => 'DB_FILES_DIR2', destination_file_name => 'UNDOTBS1.259.transfer.dbf', destination_database => 'SDIEGO'); END;
/
BEGIN DBMS_FILE_TRANSFER.put_file(source_directory_object => 'DB_FILES_DIR1',source_file_name => 'UNDOTBS2.265.839254793', destination_directory_objec t => 'DB_FILES_DIR2', destination_file_name => 'UNDOTBS2.265.transfer.dbf', destination_database => 'SDIEGO'); END;
/

PL/SQL procedure successfully completed.

SQL> SQL> 2

PL/SQL procedure successfully completed.

SQL> 2
PL/SQL procedure successfully completed.

SQL> 2
PL/SQL procedure successfully completed.

SQL> 2
PL/SQL procedure successfully completed.

SQL> 2
PL/SQL procedure successfully completed.

SQL> 2
PL/SQL procedure successfully completed.

SQL> 2

PL/SQL procedure successfully completed.

SQL> 2

PL/SQL procedure successfully completed.

SQL> 2
PL/SQL procedure successfully completed.

SQL> 2
PL/SQL procedure successfully completed.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL>

Now alter the database to end backup using following:
alter database end backup;

Log is as:

SQL> alter database end backup;
alter database end backup
*
ERROR at line 1:
ORA-01260: warning: END BACKUP succeeded but some files found not to be in
backup mode

Ignore the ORA- error here as the missing file was never placed in te backup mode.

Now lets go to the target database and see if the files are there:

ASMCMD> pwd
+DATA/SDIEGO/DATAFILE
ASMCMD> ls -lart
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'

WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'

Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => ENCRYPT.271.839195159
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => EXAMPLE.264.830971505
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => GGDATA.272.839362901
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => GOLDENGATE.270.839193779
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => READ.269.838771155
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => SYSAUX.257.830971359
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => SYSTEM.256.830971357
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => UNDOTBS1.258.830971359
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => UNDOTBS2.265.830971971
DATAFILE UNPROT COARSE MAR 13 12:00:00 Y none => USERS.259.830971359
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/DATA_ENCRYPT.270.transfer.dbf => FILE_TRANSFER.275.842352323
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/UNDOTBS2.265.transfer.dbf => FILE_TRANSFER.277.842352555
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/DATA_ENCRYPT.273.transfer.dbf => FILE_TRANSFER.280.842352325
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/EXAMPLE.257.transfer.dbf => FILE_TRANSFER.281.842352327
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/GGDATA.274.transfer.dbf => FILE_TRANSFER.282.842352359
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/GOLDENGATE.258.transfer.dbf => FILE_TRANSFER.283.842352369
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/SYSAUX.256.transfer.dbf => FILE_TRANSFER.284.842352395
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/SYSTEM.271.transfer.dbf => FILE_TRANSFER.285.842352481
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/UNDOTBS1.259.transfer.dbf => FILE_TRANSFER.286.842352547
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/USERS.transfer.dbf => FILE_TRANSFER.288.842352313
DATAFILE UNPROT COARSE MAR 16 10:00:00 Y +DATA/DATA_ENCRYPT.264.transfer.dbf => FILE_TRANSFER.289.842352315
ASMCMD> ls
ENCRYPT.271.839195159
EXAMPLE.264.830971505
FILE_TRANSFER.275.842352323
FILE_TRANSFER.277.842352555
FILE_TRANSFER.280.842352325
FILE_TRANSFER.281.842352327
FILE_TRANSFER.282.842352359
FILE_TRANSFER.283.842352369
FILE_TRANSFER.284.842352395
FILE_TRANSFER.285.842352481
FILE_TRANSFER.286.842352547
FILE_TRANSFER.288.842352313
FILE_TRANSFER.289.842352315
GGDATA.272.839362901
GOLDENGATE.270.839193779
READ.269.838771155
SYSAUX.257.830971359
SYSTEM.256.830971357
UNDOTBS1.258.830971359
UNDOTBS2.265.830971971
USERS.259.830971359
ASMCMD>

I had placed word "FILE_TRANSFER" in front of each file so that I can easily recognise which files we
trasfered and which are of the old database.

Now update the target server's /etc/oratab to reflect the name of the new database by adding following line to it:

PRABGUN1:/u01/app/oracle/product/11.2.0.3:N

Now oratab will look like following:

#
+ASM1:/u01/app/12.1.0.0/grid:N: # line added by Agent
DUMMY:/u01/app/oracle/product/11.2.0.3:N
SDIEGO1:/u01/app/oracle/product/11.2.0.3:N:
PRABGUN1:/u01/app/oracle/product/11.2.0.3:N
SDIEGO:/u01/app/oracle/product/11.2.0.3:N: # line added by Agent

Now source the environment of instance PRABGUN1 as:

. oraenv
PRABGUN1

Output is:
23:31 : oracle : rac70.rac.meditate.com : @+ASM1 : /home/oracle
$ . oraenv
ORACLE_SID = [+ASM1] ? PRABGUN1
The Oracle base remains unchanged with value /u01/app/oracle

23:31 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /home/oracle
$

Now create the parameter file as in $ORACLE_HOME/dbs and name it as 'initPRABGUN1.ora'

Following are the contents of the file 'initPRABGUN1.ora'

23:32 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$ cat initPRABGUN1.ora
_compression_compatibility= "11.2.0"
*.aq_tm_processes=1
*.archive_lag_target=0
*.audit_trail='DB'
*.cluster_database=FALSE
*.compatible='11.2.0.3.0'
*.control_file_record_keep_time=14
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_multiblock_read_count=64
*.db_flashback_retention_target=1440
*.db_name='PRABGUN'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=1G
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=true
*.fast_start_mttr_target=0
*.fast_start_parallel_rollback='LOW'
*.global_names=FALSE
*.instance_name='PRABGUN1'
*.instance_number=1
*.job_queue_processes=10
*.large_pool_size=0
*.log_archive_dest_1='LOCATION=+FLASH'
*.log_archive_format='PRABGUN%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_buffer=4190208
*.log_checkpoints_to_alert=false
*.open_cursors=250
*.open_links=4
*.optimizer_mode='choose'
*.parallel_max_servers=25
*.pga_aggregate_target=50M
*.processes=100
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=50
*.sessions=250
*.sga_max_size=368m
*.sga_target=368m
*.shared_pool_size=0
*.star_transformation_enabled='true'
*.streams_pool_size=0
*.tape_asynch_io=true
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='*'
*.workarea_size_policy='AUTO'

23:32 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$

Now lets start the database in the nomount state as:

. oraenv
PRABGUN1

sqlplus / as sysdba
startup nomount;

Following is the log:

23:32 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$ . oraenv
ORACLE_SID = [PRABGUN1] ? PRABGUN1
The Oracle base remains unchanged with value /u01/app/oracle

23:34 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 384143360 bytes
Fixed Size 2228624 bytes
Variable Size 125832816 bytes
Database Buffers 251658240 bytes
Redo Buffers 4423680 bytes
SQL>

Now using the following command, lets create the controlfile as:

CREATE CONTROLFILE SET DATABASE "PRABGUN" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA' SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.275.842352323',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.277.842352555',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.280.842352325',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.281.842352327',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.282.842352359',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.283.842352369',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.284.842352395',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.285.842352481',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.286.842352547',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.288.842352313',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.289.842352315'
CHARACTER SET AL32UTF8
;

Here is the log:

SQL> CREATE CONTROLFILE SET DATABASE "PRABGUN" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '+DATA' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '+DATA' SIZE 50M BLOCKSIZE 512
10 -- STANDBY LOGFILE
DATAFILE
11 12 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.275.842352323',
13 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.277.842352555',
14 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.280.842352325',
15 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.281.842352327',
'+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.282.842352359',
16 17 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.283.842352369',
18 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.284.842352395',
19 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.285.842352481',
20 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.286.842352547',
21 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.288.842352313',
22 '+DATA/SDIEGO/DATAFILE/FILE_TRANSFER.289.842352315'
23 CHARACTER SET AL32UTF8
24 ;

Control file created.

SQL>

Once we create teh controlfile, database is in mount state. Now lets recvoer the database and open it:

From the source database get the current SCN till we want to recover using command:
select curent_scn from v$database:

I got number '17959782'

On target database as sysdba execute teh following command and start the recovery:

sqlplus / as sysdba
recover database until change 17959782 using backup controlfile;

I scp the required archive logs from source server to the target server and applied them

Log is as:

23:34 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 16 23:39:17 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>SQL> recover database until change 17959782 using backup controlfile;
ORA-00279: change 17959484 generated at 03/16/2014 10:45:00 needed for thread 1
ORA-00289: suggestion : +FLASH
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'PRABGUN'
ORA-00280: change 17959484 for thread 1 is in sequence #25

Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/thread_1_seq_25.364.842352867
ORA-00279: change 17959749 generated at 03/16/2014 10:54:26 needed for thread 1
ORA-00289: suggestion : +FLASH
ORA-00280: change 17959749 for thread 1 is in sequence #26
ORA-00278: log file '/home/oracle/thread_1_seq_25.364.842352867' no longer
needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/thread_1_seq_26.363.842352879
ORA-00279: change 17959760 generated at 03/16/2014 10:54:38 needed for thread 1
ORA-00289: suggestion : +FLASH
ORA-00280: change 17959760 for thread 1 is in sequence #27
ORA-00278: log file '/home/oracle/thread_1_seq_26.363.842352879' no longer
needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/thread_1_seq_27.362.842352887
ORA-00279: change 17959768 generated at 03/16/2014 10:54:47 needed for thread 1
ORA-00289: suggestion : +FLASH
ORA-00280: change 17959768 for thread 1 is in sequence #28
ORA-00278: log file '/home/oracle/thread_1_seq_27.362.842352887' no longer
needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/thread_1_seq_28.361.842352891
ORA-00279: change 17959774 generated at 03/16/2014 10:54:50 needed for thread 1
ORA-00289: suggestion : +FLASH
ORA-00280: change 17959774 for thread 1 is in sequence #29
ORA-00278: log file '/home/oracle/thread_1_seq_28.361.842352891' no longer
needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/thread_1_seq_29.360.842352913
Log applied.
Media recovery complete.
SQL>

Now lets open the database using the following command as:

SQL> alter database open resetlogs;

Database altered.

SQL>

Lets update the parameter file with the location of control file as we started with pfile and at that time controlfile was not created :

SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 14
control_files string +DATA/prabgun/controlfile/curr
ent.276.842352827, +FLASH/prab
gun/controlfile/current.362.84
2352831
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>

So, in parameter file i.e. 'initPRABGUN1.ora' at $ORACLE_HOME/dbs add the following line:

*.control_files='+DATA/prabgun/controlfile/current.276.842352827','+FLASH/prabgun/controlfile/current.362.842352831'

Now parameter file looks like following:

23:46 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$ cat initPRABGUN1.ora
_compression_compatibility= "11.2.0"
*.aq_tm_processes=1
*.archive_lag_target=0
*.audit_trail='DB'
*.cluster_database=FALSE
*.control_files='+DATA/prabgun/controlfile/current.276.842352827','+FLASH/prabgun/controlfile/current.362.842352831'
*.compatible='11.2.0.3.0'
*.control_file_record_keep_time=14
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_multiblock_read_count=64
*.db_flashback_retention_target=1440
*.db_name='PRABGUN'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=1G
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=true
*.fast_start_mttr_target=0
*.fast_start_parallel_rollback='LOW'
*.global_names=FALSE
*.instance_name='PRABGUN1'
*.instance_number=1
*.job_queue_processes=10
*.large_pool_size=0
*.log_archive_dest_1='LOCATION=+FLASH'
*.log_archive_format='PRABGUN%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_buffer=4190208
*.log_checkpoints_to_alert=false
*.open_cursors=250
*.open_links=4
*.optimizer_mode='choose'
*.parallel_max_servers=25
*.pga_aggregate_target=50M
*.processes=100
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=50
*.sessions=250
*.sga_max_size=368m
*.sga_target=368m
*.shared_pool_size=0
*.star_transformation_enabled='true'
*.streams_pool_size=0
*.tape_asynch_io=true
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='*'
*.workarea_size_policy='AUTO'

23:46 : oracle : rac70.rac.meditate.com : @PRABGUN1 : /u01/app/oracle/product/11.2.0.3/dbs
$

Now add temp file to the temp tablespace using the following command
alter tablespace temp add tempfile '+DATA' size 10m;

Log is as:
SQL> alter tablespace temp add tempfile '+DATA' size 10m;

Tablespace altered.

SQL>

Now bounce the database to make sure everything looks good as follows:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 384143360 bytes
Fixed Size 2228624 bytes
Variable Size 125832816 bytes
Database Buffers 251658240 bytes
Redo Buffers 4423680 bytes
Database mounted.
Database opened.
SQL>

Now you can add it to OCR and add instances on other nodes to this database.

RAAMKALEE, FIRST MEHL:

As you come, so will you leave, you fool; as you were born, so will you die. As you enjoy pleasures, so will you suffer pain. Forgetting the Naam, the Name of the Lord, you will fall into the terrifying world-ocean. || 1 || Gazing upon your body and wealth, you are so proud. Your love for gold and sexual pleasures increases; why have you forgotten the Naam, and why do you wander in doubt? || 1 || Pause || You do not practice truth, abstinence, self-discipline or humility;

the ghost within your skeleton has turned to dry wood. You have not practiced charity, donations, cleansing baths or austerities. Without the Saadh Sangat, the Company of the Holy, your life has gone in vain. || 2 ||