AskTom

Hi,

this error mean that you reach max limit for open cursors.

you can check how many cursors do you have open by this select:

–total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = ‘opened cursors current’;

–total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = ‘opened cursors current’
group by s.username, s.machine
order by 1 desc;

– max opened cursors and current state
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = ‘opened cursors current’
and p.name= ‘open_cursors’
group by p.value;

2) when you know current state you can set up parameter open_cursors.

- this parameter set value for max number of open cursors per session.

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
———————————— ———– ——————————
open_cursors                         integer     300

There is nice article link

Regards,

Tom

- I suppose you have configures tnsnames.ora, listener.ora

1) create initstandby.ora
db_name=prod    –> same as production name

2) starup nomount db standby

3) run RMAN
connect target sys/oracle123@prod
connect auxiliary sys/oracle123@standby

4) run online duplication
RMAN> duplicate target database for standby from active database;

5) when its done aktivate DataGuard on both sides
ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

6) setup GataGuard
dgmgrl

connect sys/oracle123@prod

CREATE CONFIGURATION production AS PRIMARY DATABASE IS prod CONNECT IDENTIFIER IS prod;
ADD DATABASE standby AS CONNECT IDENTIFIER IS standby MAINTAINED AS PHYSICAL;

enable configuration

7) check configuration

DGMGRL> show configuration

Configuration
Name:                production
Enabled:             YES
Protection Mode:     MaxPerformance
Databases:
prod    - Primary database
standby - Physical standby database

Fast-Start Failover: DISABLED

Current status for “production”:
SUCCESS

DONE

You can check Oracle magazine link

Regards,

Tom

1. Identify the location of the current controlfile:
SQL> select name from v$controfile;

NAME
——————————————————————————–
/opt/app/oracle/product/11/db_1/dbs/standby_control.ctl’

2. Shutdown the database and start the instance:
SQL> shutdown normal
SQL> startup nomount

3. Use RMAN to move the controlfile to ASM :
rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Thu Mar 19 05:30:10 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: PROD (not mounted)

RMAN> restore controlfile to ‘+DATA’ from ‘/opt/app/oracle/product/11/db_1/dbs/standby_control.ctl’;

Starting restore at 19-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=539 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 19-MAR-09

RMAN> restore controlfile to ‘+DATA’ from ‘/opt/app/oracle/product/11/db_1/dbs/standby_control.ctl’;

Starting restore at 19-MAR-09
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 19-MAR-09

RMAN> exit

We are only specifying the name of the diskgroup, so Oracle will create an OMF (Oracle Mana
ged File). Use ASMCMD or sqlplus to identify the name assigned to the controlfile

4. On the ASM instance, identify the name of the controlfile:

Using ASMCMD:
$ asmcmd
ASMCMD> pwd
+data/standby/controlfile
ASMCMD> ls
current.1384.681888623
current.1385.681888685

This just the name assigned for the identical copy of the current controlfile.

5. On the database side:

* Modify init.ora or spfile, replacing the new path to the init parameter control_files.
* if using init<SID>.ora, just modify the control_files parameter and restart the database.
* If using spfile,

5.
start the instance.

Verify that new control file has been recognized. If the new controlfile was not used, the complete procedure needs to be repeated.

6.
SQL> show parameter control_file

NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_file_record_keep_time        integer     7
control_files                        string      +DATA/standby/controlfile/curr
ent.1384.681888623, +DATA/stan
dby/controlfile/current.1385.6
81888685

NOTE : Steps 3 to 5 Could be repeated to multiplexes the contolfile to more than on each diskgroup .

Regards,

Tom