Skip to Main Content
  • Questions

  • Database Service Configuration Requirements

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ram.

Asked: September 08, 2017 - 6:59 am UTC

Last updated: October 13, 2017 - 3:43 am UTC

Version: Any

Viewed 1000+ times

You Asked

Hi TOM,

Was going through the section 5.6.1.2 in http://docs.oracle.com/database/121/DGBKR/sofo.htm#DGBKR3425

It says "Services that are to be active while the database is in the physical standby role must also be created and started on the current primary database regardless of whether the service will be started on that database or not. This is to ensure that the service definition gets propagated to the physical standby database via the redo stream and thus allows for the service to be started on the physical standby database. The service can be started on the physical standby only after the redo generated by starting the service has been applied. It is important that all SRVCTL add service options be identical on all the databases so that the services behave the same way before and after a role change".

Two questions:

1) We have an FSFO configuration which has 1 primary and 1 standby database. We add both the primary database service (EXAMPLE) and the read only service (EXAMPLE_RO) on both the databases like below :

=======
PRIMARY
=======
srvctl add service –db RAM_PRIMARY –service EXAMPLE –role PRIMARY
srvctl add service –db RAM_PRIMARY –service EXAMPLE_RO –role PHYSICAL_STANDBY

srvctl start service –db RAM_PRIMARY –service EXAMPLE

=======
STANDBY
=======
srvctl add service –db RAM_STANDBY –service EXAMPLE –role PRIMARY
srvctl add service –db RAM_STANDBY –service EXAMPLE_RO –role PHYSICAL_STANDBY

srvctl start service –db RAM_STANDBY –service EXAMPLE_RO

Assuming RAM_PRIMARY is currently acting as primary in the configuration, we never had to start EXAMPLE_RO on RAM_PRIMARY so that we will be able to start it on RAM_STANDBY. Why does the documentation say otherwise?

2) What's the whole deal about service definition and redo stream? What's the connection?

and Connor said...

There is a correlation between the service definitions stored in the cluster layer and *within* the database.

SQL> desc dba_services
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- -------------------
 SERVICE_ID                                                                                                                 NUMBER
 NAME                                                                                                                       VARCHAR2(64)
 NAME_HASH                                                                                                                  NUMBER
 NETWORK_NAME                                                                                                               VARCHAR2(512)
 CREATION_DATE                                                                                                              DATE
 CREATION_DATE_HASH                                                                                                         NUMBER
 FAILOVER_METHOD                                                                                                            VARCHAR2(64)
 FAILOVER_TYPE                                                                                                              VARCHAR2(64)
 FAILOVER_RETRIES                                                                                                           NUMBER(10)
 FAILOVER_DELAY                                                                                                             NUMBER(10)
 MIN_CARDINALITY                                                                                                            NUMBER
 MAX_CARDINALITY                                                                                                            NUMBER
 GOAL                                                                                                                       VARCHAR2(12)
 DTP                                                                                                                        VARCHAR2(1)
 ENABLED                                                                                                                    VARCHAR2(3)
 AQ_HA_NOTIFICATIONS                                                                                                        VARCHAR2(3)
 CLB_GOAL                                                                                                                   VARCHAR2(5)
 EDITION                                                                                                                    VARCHAR2(128)
 COMMIT_OUTCOME                                                                                                             VARCHAR2(3)
 RETENTION_TIMEOUT                                                                                                          NUMBER
 REPLAY_INITIATION_TIMEOUT                                                                                                  NUMBER
 SESSION_STATE_CONSISTENCY                                                                                                  VARCHAR2(128)
 GLOBAL_SERVICE                                                                                                             VARCHAR2(3)
 PDB                                                                                                                        VARCHAR2(128)
 SQL_TRANSLATION_PROFILE                                                                                                    VARCHAR2(261)
 MAX_LAG_TIME                                                                                                               VARCHAR2(128)
 GSM_FLAGS                                                                                                                  NUMBER
 PQ_SVC                                                                                                                     VARCHAR2(64)
 STOP_OPTION                                                                                                                VARCHAR2(13)
 FAILOVER_RESTORE                                                                                                           VARCHAR2(6)
 DRAIN_TIMEOUT                                                                                                              NUMBER


If you look at the DDL for that view, you'll see it is based on an internal table called SERVICE$. So that entry in that table must exist in the primary for it to exist in the standby.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up

Ram Dittakavi, September 15, 2017 - 5:42 am UTC

Thanks Connor.

Can you please clarify my 1st question as well? How am I able to start the RO service in standby without starting it ever on primary in the first place?

Follow up

Ram Dittakavi, October 04, 2017 - 7:08 am UTC

Thanks Connor.

Can you please clarify my 1st question as well? How am I able to start the RO service in standby without starting it ever on primary in the first place?
Connor McDonald
October 06, 2017 - 5:38 am UTC

The issues was that adding a service to CRS did not execute the DBMS_SERVICE.CREATE_SERVICE package to put the service definition into the database's metadata until the service was started for the first time.

So as long as you started the service at least *once*, then it will be in data dictionary.

Follow up

Ram Dittakavi, October 12, 2017 - 6:29 am UTC

Hi Connor,

We cannot start a service on a database unless an entry is available in service$ ?

But, as I mentioned earlier, i was able to start the _RO service on standby even before it was started once on primary.
Connor McDonald
October 13, 2017 - 3:43 am UTC

But, as I mentioned earlier, i was able to start the _RO service on standby even before it was started once on primary.

I tried a few internal environments and I could not start a service on a RO standby (unless I had done it at least once at some stage in the past on the primary).

If you're seeing different behaviour, I think you'll need to log a call with Support with a full test case - because that should not be possible.

Follow up

Ram Dittakavi, October 19, 2017 - 6:13 am UTC

Thanks a lot, Connor.

Suppose, we have flashback logs pertaining to a guaranteed restore point (GRT) and then FRA gets 100% full.

Suppose, FRA is 2G.

Case1: GRT logs occupy 1.5G

Now, the DMLs are allowed after purging the logs that are there in the remaining 0.5G (ofcourse 200MB at a time) and this repeats?

Case2: GRT logs occupy 2G

Now, what happens?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database