If you have the required additional license, with Oracle 11gR1 onward you can open your physical standby database for read-only operations. If correctly configured, it gives you the benefit of relieving the primary database from the burden of running those long, heavy reports users are always wanting.
The database section is very easy to implement:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL IMMEDIATE;
ALTER DATABASE OPEN; (it will open read-only, don't worry)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 4 USING CURRENT LOGFILE DISCONNECT;
Now, OPEN_MODE in V$DATABASE should read “READ ONLY WITH APPLY”. That’s it, users are able to connect using the DR database name. But to enhance manageability I recommend that you create a specific service for this usage. The commands are
srvctl add service -d mydb_dr -s mydb_rptg.mydomain.com -r mydb1 -a mydb2 -l PHYSICAL_STANDBY
srvctl start service -d mydb_dr -s mydb_rptg.mydomain.com
If you go and create the service only on the standby side, however, you’ll get an error like I got:
PRCD-1084 : Failed to start service mydb_rptg.mydomain.com
PRCR-1079 : Failed to start resource ora.mydb_dr.mydb_rptg.mydomain.com.svc
CRS-5017: The resource action "ora.mydb_dr.mydb_rptg.mydomain.com.svc start" encountered the following error:
ORA-44317: database open read-only
ORA-06512: at "SYS.DBMS_SERVICE", line 478
ORA-06512: at "SYS.DBMS_SERVICE", line 229
ORA-06512: at line 1
CRS-2674: Start of 'ora.mydb_dr.mydb_rptg.mydomain.com.svc' on 'myDRserver01' failed
CRS-5017: The resource action "ora.mydb_dr.mydb_rptg.mydomain.com.svc clean" encountered the following error:
ORA-44304: service mydb_rptg.mydomain.com does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE", line 452
ORA-06512: at "SYS.DBMS_SERVICE", line 400
ORA-06512: at line 1
This happens because for a service to be activated some records regarding it must exist in the data dictionary of the database, and as the standby cannot write it’s own dictionary this error is raised.
The solution is to briefly enable the same service in the primary database:
srvctl add service -d mydb -s mydb_rptg.mydomain.com -r mydb1 -a mydb2 -l PHYSICAL_STANDBY
srvctl start service -d mydb -s mydb_rptg.mydomain.com
srvctl stop service -d mydb -s mydb_rptg.mydomain.com
By doing so the service will be registered in the database dictionary and the records transferred to standby via log transfer. Then you can enable the service on standby. Also, it’s wise to keep the same set of services in both clusters, so you won’t need to worry about them on a failover or switchover.
The value on the -l switch will make this service enable itself only when the database has the physical standby role.