Generate dinamically commands to add the services in GI
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.
[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb1_svc1 -pdb pdb1
[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb1_svc2 -pdb pdb1
[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb1_svc3 -pdb pdb1
[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb2_svc1 -pdb pdb2
[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb2_svc2 -pdb pdb2
[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb2_svc3 -pdb pdb2
As you probably know, when you create the services you need to start them after..
[oracle@ora18c3 ~]$ srvctl status service -d db01
Service pdb1_svc1 is not running.
Service pdb1_svc2 is not running.
Service pdb1_svc3 is not running.
Service pdb2_svc1 is not running.
Service pdb2_svc2 is not running.
Service pdb2_svc3 is not running.
Now, let’s start the services, after the creation…
[oracle@ora18c3 ~]$ srvctl start service -s pdb1_svc1 -d db01
[oracle@ora18c3 ~]$ srvctl start service -s pdb1_svc2 -d db01
[oracle@ora18c3 ~]$ srvctl start service -s pdb1_svc3 -d db01
[oracle@ora18c3 ~]$ srvctl start service -s pdb2_svc3 -d db01
[oracle@ora18c3 ~]$ srvctl start service -s pdb2_svc2 -d db01
[oracle@ora18c3 ~]$ srvctl start service -s pdb2_svc1 -d db01
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
When we check the service, I will just pickup one, as an example to analyze it
[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
