Migrating your GDS catalog database to new server – 12c

We know very much about the backup of databases and restoring the database in same server or different server, but when it comes to catalog database to manage global data services the procedure is entirely different, in short we create new database and then export and import of the catalog metadata. This article explains how to migrate the GDS catalog from one database server to another new database.

Migration Plan

Let’s consider the catalog database is hosted in server and you decided to decommission the server and then it is situation catalog database need to build from scratch, but Oracle provided simplified procedure to export logical backup and import after few prerequisites on new database. This procedure can be done if you are migrating to different database in same server or another server.

When we say the backup it means not the entire database backup, we backup only the metadata of GDS catalog which consists of the catalog information with private keys , public keys, Global service manager(GSM) information, databases, services, pools, regions and so on. The best deal is we can import this logical backup in any new database instead of performing backup of entire database regularly. Now consider we have taken logical backup of metadata and in order to import the logical backup we must have fresh backup and that can be created using DBCA or manually. This is brief introduction and actual demo part and the explanation yet to come for more information.

Tip: Perform the logical backup of GDS metadata regularly

Exporting the Logical backup

This step we are going to perform in the existing catalog database, in this example consider the existing catalog database as GCAT and the fresh catalog database as NCAT. Before exporting we will check what the current configuration of databases and services is.

[oracle@ORA-C2 admin]$ gdsctl gsmadm/oracle@gcat
GDSCTL: Version 12.1.0.2.0 – Production on Thu Jul 28 19:56:39 NZST 2016

Copyright (c) 2011, 2014, Oracle. All rights reserved.

Welcome to GDSCTL, type "help" for information.

GDSCTL>set gsm -gsm sagsm
GDSCTL>databases
Database: "canada" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: westcan
Service: "app_engine" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Service: "nvision_report" Globally started: Y Started: N
Scan: N Enabled: Y Preferred: Y
Registered instances:
psfin%1
Database: "india" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: apac
Service: "nvision_report" Globally started: Y Started: Y
Scan: N Enabled: Y Preferred: Y
Registered instances:
psfin%11

GDSCTL>services
Service "app_engine.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "psfin%1", name: "ORC1", db: "CANADA", region: "westcan", status: ready.
Service "nvision_report.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "psfin%11", name: "ORC1", db: "INDIA", region: "apac", status: ready.

GDSCTL>

Before exporting the backup the initial step is to validate the current setup is going to work after we import into new database. So let’s perform validating the backup.

[oracle@ORA-C2 nassyam]$ gdsctl

GDSCTL: Version 12.1.0.2.0 – Production on Thu Jul 28 05:09:58 NZST 2016

Copyright (c) 2011, 2014, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

Current GSM is set to GSMORA

GDSCTL>validate catalog -gsm sagsm -database canada -catpwd oracle -dbpwd oracle
Validation results:

Total errors: 0.
GDSCTL>

GDSCTL>validate catalog -gsm sagsm -database india -catpwd oracle -dbpwd oracle
Validation results:

Total errors: 0.
GDSCTL>

Once it is validated successfully then we can perform the backup of the metadata of whole GDS configuration like below.

GDSCTL>export catalog /home/oracle/nassyam/exp_gcat.dmp

GDSCTL>

To know the syntax of command we can always consider using HELP command.

GDSCTL>help export catalog

Syntax

– EXPORT CATALOG [-database catalog_db_name] file_name

– IMPORT CATALOG [-database catalog_db_name] [-catpwd gsmcatusrpwd]

Purpose

Imports|exports the GDS catalog database to|from the specified file.

Usage Notes

If -database is not specified, the GDS catalog that the current GSM is associated with will be used.

-catpwd will be provided in case we need to remove databases which are not found in imported catalog

Keywords and Parameters

-database   the GDS catalog database credentials.

file_name    the local file name to import from or export to.

 -catpwd the password of gsmcatuser.

Examples

GDSCTL> export catalog GSMenv.bak

GDSCTL>

With the above command we have performed backup and we can check the backup file and the format of file is readable, here we can see all the metadata how the catalog is created and what all the databases, services are configured in this configuration

[oracle@ORA-C2 nassyam]$ ls -ltr /home/oracle/nassyam/exp_gcat.dmp

-rw-r–r–. 1 oracle oinstall 4271 Jul 28 05:10 /home/oracle/nassyam/exp_gcat.dmp

[oracle@ORA-C2 nassyam]$ file /home/oracle/nassyam/exp_gcat.dmp

/home/oracle/nassyam/exp_gcat.dmp: ASCII text, with very long lines

[oracle@ORA-C2 nassyam]$ cat /home/oracle/nassyam/exp_gcat.dmp

catalog -prvk_enc_str "28212C" -private_key "5FF063156E646F426C65654FE93AE79461646E73646368E76D1251E16E396D736161EDE46FB68399C1F9FEF39E8A996D848F8DD21E8AAF5D05204DE6350145AA3B34159DC61E9B3DAB7C1D5AA3598CB8D8A7863CCF594F0BB016B532599A00DCCD0C6E282026B531556BF19E1816CF0C66AD091424D22868294E3D71CD6C6D8275112328A5E5A952C3A21B68BE09AB52CF5B583DB196D6F6141A17D31C0949C87362CEDBB8616F646F7363E2EC3BB2953EC08F35D52AC7C1AB865F6929E33D456BC71A9D04C1D9F0945A8F19154F8C81C1E05A305CC1B352F23D2FE7489786F5A8220D335BEFCB2CAC609ED6FAFC14671C2324624642C489F70AAEFDE1322C2999AA2D872E7987F907DDF0A8B8F45A1A26228836797AA5AB21767CD47E8CB33FBA38A248AD0B96E84B737A37467020639A91DA263AE6A8A201F2468D5BDCB78A6BD8B5AA79D2E71A7AC173863EC0B3814C5B7642F7C91106476E004BC8C4BDD3FF7FEA281976C5DBD90AEBC6AF804DB163226CA977A898B7BCBF0CFC4A615F7EE2DFC5FE2305C66FB9D293C647898BD5CEB22E404442F2E631B1D68AFCC3A250FF80CC4EB6A2E5F4BBAC61D0D74D1057100D52612C212BDDFE883D7302AD205724CC2A7873DCD0850F2E4A914CC051D4971051907AC577F0B70CFDFEDFE6660BB1E0851E47FB81A691E5EFABFE85740AFAFEE6B92A612C7CC17C8E3F31C793ADA91EFC39C65A8F68196A310A698A80F30B81DCE5F53FC7413C67C78640D925F9D4DBCB6694766E912EF7A06A5D921B3799B128E75166EC612D65CEF6AEB662195763383C3869CD1D36C0A267919DBC9167DEA2D48B11516D526571443AE7544918315D6C9A48DD5B7FFDAC078950EFEBFE54CA8549DED60FDA64" -autovncr ON -name "oradbcloud" -public_key "30819F300D06092A864886F70D010101050003818D0030818902818100C4E2FAAD9C9181FFE9F508EBFDECB172EFC02F644321835A7324C957517AEFA77DF758C40E7C39CF3CE3CAB9C4EA59A02B2E68DC73DA4038F96CB9A27E0F4B4C43DA4334089DFB7764AE6F0AC8666645B1440D463C5C12A10902F014724F4DCA97C831AFC7741ADF6AC737A029395EDDF3B98475797BB6737B28AB1F07A1A9D90203010001" -next_db_num 41 -script_version "12.1.0.2.0" -version "2"

add invitednode -name "192.168.0.90"

add invitednode -name "192.168.0.80"

add region -region "regionora" -num 0

add region -region "apac" -num 1

add region -region "westcan" -num 2

add gdspool -broker_config  -gdspool "psfin"

add gdspool -gdspool "dbpoolora"

add gsm -region apac -localons 6123 -num 1 -oracle_home "/u01/app/oracle/product/12.1.0/gsmhome_1" -hostname "ORA-C2.localdomain" -remoteons 6234 -gsm "sagsm" -remote_endpoint "(ADDRESS=(HOST=ORA-C2.localdomain)(PORT=1555)(PROTOCOL=tcp))" -endpoint "(ADDRESS=(HOST=ORA-C2.localdomain)(PORT=1555)(PROTOCOL=tcp))"

add database -region apac -connect "india" -encpwd "1B6C2D15EACC223DA311967378269F6946C835B969917F952DBA323F58C9DEE8EAA2405CF4633B91A0D2C94E47CEE49A7EC71CC6DE5967601AEAA9EE0BA3134A249673441D45576E7B5F576298D4E0405B6AC7AEB4AF0982E6447A23E22B274164DAB1F4C0ECDBEA888067B6CFAA48B7D4295C34525D4DFF6F899B9C1CFA2F6D" -database_num 11 -status D -cpu_threshold 75 -num_assigned_instances 10 -disk_threshold 20 -pwd "1" -ons_port 0 -name "india" -gdspool "psfin"

add database -region westcan -connect "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA-C1.localdomain)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=ORC1)))" -encpwd "1B6C2D15EACC223DA311967378269F6946C835B969917F952DBA323F58C9DEE8EAA2405CF4633B91A0D2C94E47CEE49A7EC71CC6DE5967601AEAA9EE0BA3134A249673441D45576E7B5F576298D4E0405B6AC7AEB4AF0982E6447A23E22B274164DAB1F4C0ECDBEA888067B6CFAA48B7D4295C34525D4DFF6F899B9C1CFA2F6D" -database_num 1 -status D -cpu_threshold 75 -num_assigned_instances 10 -disk_threshold 20 -pwd "1" -ons_port 0 -name "canada" -gdspool "psfin"

add service -lag -1 -notification true -role PHYSICAL_STANDBY -gdspool "psfin" -any_lag  -status S -commit_outcome false -rlbgoal SERVICE_TIME -network_name "nvision_report.psfin.oradbcloud" -dtp false -tafpolicy NONE -clbgoal LONG -service "nvision_report" -locality ANYWHERE -policy AUTOMATIC -failovermethod NONE -failovertype NONE -preferred_all

add service -lag -1 -notification true -gdspool "psfin" -any_lag  -status S -commit_outcome false -rlbgoal SERVICE_TIME -network_name "app_engine.psfin.oradbcloud" -dtp false -tafpolicy NONE -clbgoal LONG -service "app_engine" -locality ANYWHERE -policy AUTOMATIC -failovermethod NONE -failovertype NONE

add preferred -status E -state S -service "nvision_report" -gdspool "psfin" -database "india"

add preferred -status E -state S -service "nvision_report" -gdspool "psfin" -database "canada"

add preferred -status E -state S -service "app_engine" -gdspool "psfin" -database "canada"

[oracle@ORA-C2 nassyam]$

By thus we have performed all the steps related to the backup, the logical backup file can be copied to any location of server where the new catalog database exists.

Importing the Logical backup

Before importing the logical backup few more prerequisites to be performed on new database.

1)      Create new database (NCAT)

2)      Unlock and change password of GSMCATUSER

3)      Unlock and grant GSMADMIN_ROLE to GSMADM user

4)      Add TNS string of NCAT to GSM Network home, so that connection string can be used

SQL> select username,account_status from dba_users where username='GSMCATUSER';

USERNAME ACCOUNT_STATUS
————— ——————————–
GSMCATUSER EXPIRED & LOCKED

SQL> alter user gsmcatuser identified by oracle;

User altered.

SQL> alter user gsmcatuser account unlock;

User altered.

SQL> create user gsmadm identified by oracle;

User created.

SQL> grant gsmadmin_role to gsmadm;

Grant succeeded.

SQL> select username,account_status from dba_users where username in ('GSMCATUSER','GSMADM');

USERNAME ACCOUNT_STATUS
————— ——————————–
GSMADM OPEN
GSMCATUSER OPEN

SQL>

After changes with the users, roles we can create new TNS service for the database created and then append into the $GSM_HOME/network/admin

NCAT =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = ORA-C2.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = NCAT)

    )

  )

By adding the TNS service we can easily connect from GSM home to the new catalog database like below.

[oracle@ORA-C2 admin]$ gdsctl gsmadm/oracle@ncat

GDSCTL: Version 12.1.0.2.0 – Production on Thu Jul 28 21:35:36 NZST 2016

Copyright (c) 2011, 2014, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

GDSCTL>

After copying the exported metadata file, now we can import using the below command

GDSCTL>import catalog -database ncat /home/oracle/nassyam/exp_gcat.dmp
username:gsmadm
password:
Catalog is created
GDSCTL>

After importing now we can check databases and services are running from GSM using the new catalog database below.

GDSCTL>set gsm -gsm sagsm

GDSCTL>databases

Database: "canada" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: westcan

   Service: "app_engine" Globally started: Y Started: Y

            Scan: N Enabled: Y Preferred: Y

   Service: "nvision_report" Globally started: Y Started: N

            Scan: N Enabled: Y Preferred: Y

   Registered instances:

     psfin%1

Database: "india" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: apac

   Service: "nvision_report" Globally started: Y Started: Y

            Scan: N Enabled: Y Preferred: Y

   Registered instances:

     psfin%11

GDSCTL>services

Service "app_engine.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE

   Instance "psfin%1", name: "ORC1", db: "CANADA", region: "westcan", status: ready.

Service "nvision_report.psfin.oradbcloud" has 1 instance(s). Affinity: ANYWHERE

   Instance "psfin%11", name: "ORC1", db: "INDIA", region: "apac", status: ready.

GDSCTL>

In few cases if the synchronization was not proper then we have to perform sync manually based on the configuration and the syntax can be gathered below.

GDSCTL>help sync

Ambiguous text "sync", valid completions are:

synchronize brokerconfig*

synchronize database*

sync brokerconfig*

sync database*

GDSCTL>

Now we are into purely new catalog database (NCAT) and functioning well with the databases and services, after this test we can drop the old catalog database any time.

[oracle@ORA-C2 admin]$ sqlplus sys/oracle@nvision_report as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 28 21:54:36 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select database_role from v$database;

DATABASE_ROLE

—————-

PHYSICAL STANDBY

SQL>

Summary

We’ve seen how to perform validation of backup and performing the metadata backup and also how to importing catalog metadata into the fresh database, apart from that we performed post validation by checking the databases and services are running.