Creating RAC database Service with Physical Standby Role on a database

 

Since the standby database is not open for read/write, service has to be created in primary first and once the redo entries are applied to the standby database, create the service in standby database.

 

Steps:

 

On Primary:

1. Login as oracle user,  set the environment to the target database SID and create the service:

oracle DB1_1 NODE1> srvctl add service -d DB1_B -s SERVICE_NAME -r “DB1_1,DB1_2” -l PHYSICAL_STANDBY -q FALSE -e NONE -m NONE -w 0 -z 0

 

If the above command fails with permission issues (PRCR-1006,PRCR-1071,CRS-2566), go to Step 2, otherwise skip to Step 5.

 

2. Login as root and execute following commands:

# . oraenv

ORACLE_SID = [root] ? DB1_1

# export ORACLE_HOME=/oracle/product/1120/rdbms/a

# df –h /oracle/dbadmin

Filesystem            Size  Used Avail Use% Mounted on

/dev/asm/acfs_vol_1-126                       49G   15G   35G  31% /oracle/dbadmin

 

# /oracle/product/1120/rdbms/a/bin/srvctl modify filesystem -d /dev/asm/acfs_vol_1-126 -u oracle

 

3. Login as crs user and execute below commands:

NODE1> +ASM1

NODE1> df –h /oracle/dbadmin

NODE1> srvctl config filesystem -d /dev/asm/acfs_vol_1-126

 

4. Login as oracle, set the environment to the target database SID and add the service:

oracle DB1_1 NODE1> srvctl add service -d DB1_B -s SERVICE_NAME -r “DB1_1,DB1_2” -l PHYSICAL_STANDBY -q FALSE -e NONE -m NONE -w 0 -z 0

 

5. As oracle user:

oracle DB1_1 NODE1> srvctl start service -d DB1_B -s SERVICE_NAME

 

Verify if the service has started on primary:

 

oracle DB1_1 NODE1> srvctl status service -d DB1_B –s SERVICE_NAME

 

Switch the logfile:

SQL> alter system archive log current;

 

On Standby:

1. Login as oracle user,  set the environment to the target database and execute:

oracle DB1_1 NODE2> srvctl add service -d DB1_A -s SERVICE_NAME -r “DB1_1,DB1_2” -l PHYSICAL_STANDBY -q FALSE -e NONE -m NONE -w 0 -z 0

 

If the above command fails with permission issues (PRCR-1006,PRCR-1071,CRS-2566), go to Step 2, otherwise skip to Step 4.

 

2. Login as root and execute following commands:

# export ORACLE_HOME=/oracle/product/1120/rdbms/a

# df –h /oracle/dbadmin

Filesystem           1K-blocks      Used Available Use% Mounted on

/dev/asm/acfs_vol_1-306                      51380224   5678340  45701884  12% /oracle/dbadmin

# /oracle/product/1120/rdbms/a/bin/srvctl modify filesystem -d /dev/asm/acfs_vol_1-306 -u oracle

 

3. Login as crs user and execute:

+ASM1 NODE2> srvctl config filesystem -d /dev/asm/acfs_vol_1-306

 

4. Login as oracle, set the environment to the target database SID and ensure that the new service has been replicated to standby database:

SQL> SELECT count(1) from DBA_SERVICES WHERE name=’SERVICE_NAME’;

Above query should return 1.

 

On Primary:

Login as oracle user, set the environment to the target database and stop the service as it should not be running here:

oracle DB1_1 NODE1> srvctl service –d DB1_B –s SERVICE_NAME

 

On Standby:

1. Now create the service as oracle user:

oracle DB1_1 NODE2> srvctl add service -d DB1_A -s SERVICE_NAME -r “DB1_1,DB1_2” -l PHYSICAL_STANDBY -q FALSE -e NONE -m NONE -w 0 -z 0

 

2. start the service:

oracle DB1_1 NODE2> srvctl start service -s SERVICE_NAME -d DB1_A

 

check the status of the service:

oracle DB1_1 NODE2> srvctl status service -s SERVICE_NAME -d DB1_A

 

 

TNS entry:

SERVICE_NAME =

(DESCRIPTION_LIST=

(LOAD_BALANCE=off)

(FAILOVER=on)

(DESCRIPTION=        (CONNECT_TIMEOUT=10)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=1)

(ADDRESS_LIST=

(LOAD_BALANCE=on)

(ADDRESS=(PROTOCOL=TCP)(HOST=CLUSTER_A-scan)(PORT=1526)))

(CONNECT_DATA=(SERVICE_NAME= SERVICE_NAME)))

(DESCRIPTION=        (CONNECT_TIMEOUT=10)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=1)

(ADDRESS_LIST=

(LOAD_BALANCE=on)

(ADDRESS=(PROTOCOL=TCP)(HOST=CLUSTER_B-scan)(PORT=1526)))

(CONNECT_DATA=(SERVICE_NAME= SERVICE_NAME))))