During one migration activity we must copy all defined services associated with pluggable database from one cluster to another one. This task is not dificult, you can list the service in one side and add the services on your new cluster. But, we are humans and we can forget to add some service or even occurs some types during the creation of services, so, to be more accurate and avoid issues we decided to write a small shell script, I called gen_add_service.sh to add generate dinamically commands to add the services from the source GI to destination GI.

The script by itself is not rocket science, but some tricks can help you to solve some other  difficulties that I have during the construction of this small piece of code.

To reproduce this environment, I will create some services to PDB associated with it.

Now, after start, let’s check the service again..

[oracle@ora18c3 ~]$ srvctl status service -d db01

Service pdb1_svc1 is running

Service pdb1_svc2 is running

Service pdb1_svc3 is running

Service pdb2_svc1 is running

Service pdb2_svc2 is running

Service pdb2_svc3 is running     

[oracle@ora18c3 ~]$ srvctl config service -d db01 -s pdb1_svc1

Service name: pdb1_svc1

Cardinality: SINGLETON

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type:

Failover method:

Failover retries:

Failover delay:

Failover restore: NONE

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Pluggable database name: pdb1

Hub service:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Drain timeout:

Stop option:

Session State Consistency: DYNAMIC

GSM Flags: 0

Service is enabled

Now, let me explain the issues that I’ve got to wrote this script:

for SERVICE in `srvctl status service -d ${DBNAME} | awk -F” ” ‘{print $2}’`

do

   srvctl config service -d ${DBNAME} -s ${SERVICE} | grep “Service name\|Pluggable database name” | column -c 500 >> /tmp/pdbs.txt

done

cat /tmp/pdbs.txt | awk -F” ” ‘{print “srvctl add service -d ‘$DBNAME’ -s ” $3 ” -e SELECT -m BASIC -j LONG -P BASIC -pdb ” $7 ” -r ‘$DBNAME’1 -a ‘$DBNAME’2″}’

1 ) To list the service names, we used srvctl status serice -d <<DBNAME>> and then used srvctl config service to detailed output for each service.

Solution: Used a for to list and then grep all services and pdb names.

2) The lines Service Name and Pluggable database name are in separated lines on output.

Solution: To solve this, I user grep  “Service name\|Pluggable database name”

3) The output using grep with multiple values break the lines and I need to put all outputs on same line.

Solution: Used the column command to aggregate the output in just one line

4) Even with the aggregation, after some characters the output of my file /tmp/pdbs.txt got wrong results due to break in the line.

Solution: Used column -c 500

5) The last obstacle was the awk to print linux variables

Solution: To solve this and print variables is just use single quotes on variable names and with the command that I need to print at the screen.

[oracle@ora18c3 ~]$ sh gen_add_service.sh

Enter dbname to generate add services cmd

db01

srvctl add service -d db01 -s  -e SELECT -m BASIC -j LONG -P BASIC -pdb  -r db011 -a db012

srvctl add service -d db01 -s pdb1_svc1 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb1 -r db011 -a db012

srvctl add service -d db01 -s pdb1_svc2 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb1 -r db011 -a db012

srvctl add service -d db01 -s pdb1_svc3 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb1 -r db011 -a db012

srvctl add service -d db01 -s pdb2_svc1 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb2 -r db011 -a db012

srvctl add service -d db01 -s pdb2_svc2 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb2 -r db011 -a db012

srvctl add service -d db01 -s pdb2_svc3 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb2 -r db011 -a db012

I just put on the script whatever I wanted to formula the desired output to add my services on the new cluster. In my laptop I don’t have the RAC to add the services with multiple instances, but I hope that you got the idea and maybe use the tricks that I used to solve issues on your environment.

I hope this script help you on your next clone.

Best Regards,

Rodrigo Mufalani (OCM)

Oracle ACE Member  –  https://mufalani.wordpress.com