Skip to content

How to Compare Two Databases After Migration (1/3) - Comparing Initialization Parameters

  • by
Go Back to Overview -How to Compare Two Databases After Migration (0/3) - An Overview
The initialization parameters of a standby database might deviate from the primary database after a period of time. This could be caused by DBA who might have ever modify some of the initialization parameters on the primary database, but forgot to modify them on the standby database as well.

Here I introduce a block of anonymous PL/SQL code which can find out the deviations between two databases, you can compare the differences between not only the primary and standby database, but also the source and target ones after a data migration.

First of all, you must create a database link on target database before executing the comparing script.
[oracle@targetserver ~]$ sqlplus system@target_db
...
SQL> CREATE DATABASE LINK SOURCE_DB_LINK
  2  CONNECT TO system IDENTIFIED BY system_password
  3  USING 'source_db';

Let's see the content of the comparing script as below:
[oracle@targetserver ~]$ cat -n CompareInitialParameters.sql
     1  SET SERVEROUTPUT ON SIZE 1000000;
     2  DECLARE
     3          TYPE t_rec IS RECORD (NAME VARCHAR2(80));
     4          TYPE t_tab IS TABLE OF t_rec INDEX BY PLS_INTEGER;
     5          v_tab t_tab;
     6          v_time VARCHAR2(18);
     7          v_sql VARCHAR2(200);
     8          v_name VARCHAR2(80);
     9          v_value1 VARCHAR2(4000);
    10          v_value2 VARCHAR2(4000);
    11
    12  BEGIN
    13          SELECT NAME BULK COLLECT INTO v_tab FROM (SELECT NAME FROM V$PARAMETER@SOURCE_DB_LINK INTERSECT SELECT NAME FROM V$PARAMETER);
    14          DBMS_OUTPUT.PUT_LINE('***************************************************************');
    15          DBMS_OUTPUT.PUT_LINE('*                                                             *');
    16          DBMS_OUTPUT.PUT_LINE('*         The differences of initialization parameters        *');
    17          DBMS_OUTPUT.PUT_LINE('*            between source and target databases              *');
    18          DBMS_OUTPUT.PUT_LINE('*                                                             *');
    19          DBMS_OUTPUT.PUT_LINE('***************************************************************');
    20          FOR i IN 1..v_tab.COUNT LOOP
    21                  v_name := v_tab(i).NAME;
    22                  v_sql := 'SELECT VALUE FROM V$PARAMETER@SOURCE_DB_LINK WHERE NAME='''||v_name||'''';
    23                  EXECUTE IMMEDIATE v_sql INTO v_value1;
    24                  v_sql := 'SELECT VALUE FROM V$PARAMETER WHERE NAME='''||v_name||'''';
    25                  EXECUTE IMMEDIATE v_sql INTO v_value2;
    26                  IF v_value1 != v_value2 THEN
    27                          DBMS_OUTPUT.PUT_LINE('Parameter NAME: '||v_name);
    28                          DBMS_OUTPUT.PUT_LINE('  SOURCE VALUE: '||v_value1);
    29                          DBMS_OUTPUT.PUT_LINE('  TARGET VALUE: '||v_value2);
    30                          DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');
    31                  END IF;
    32          END LOOP;
    33  END;
    34  /

Before you execute the comparing script on the target database, you must make sure the source or standby database is either read-only or read-write.

Then execute the comparing script on the target database. The result could be as following:
[oracle@targetserver ~]$ sqlplus system@target_db
...
SQL> @CompareInitialParameters.sql
***************************************************************
*                                                             *
*         The differences of initialization parameters        *
*            between source and target databases              *
*                                                             *
***************************************************************
Parameter NAME: audit_file_dest
        SOURCE VALUE: /oracle/product/10.2.0/db_1/rdbms/audit
        TARGET VALUE: /oracle/base/database/admin/COMPDB/adump
---------------------------------------------------------------
Parameter NAME: audit_trail
        SOURCE VALUE: OS
        TARGET VALUE: DB
---------------------------------------------------------------
Parameter NAME: background_dump_dest
        SOURCE VALUE: /oracle/product/10.2.0/db_1/admin/COMPDB/bdump
        TARGET VALUE: /oracle/base/database/diag/rdbms/compdb/TARGET_DB/trace
---------------------------------------------------------------
Parameter NAME: cluster_database
        SOURCE VALUE: FALSE
        TARGET VALUE: TRUE
---------------------------------------------------------------
Parameter NAME: cluster_database_instances
        SOURCE VALUE: 1
        TARGET VALUE: 2
---------------------------------------------------------------
Parameter NAME: compatible
        SOURCE VALUE: 10.1.0
        TARGET VALUE: 11.2.0.0.0
---------------------------------------------------------------
...

Since the result shows only the differences, you can distinguish the key differences from the minors and focus on them.

How to Compare Two Databases After Migration (2/3) - Comparing Row Counts by Leveraging Statistics

Leave a Reply

Your email address will not be published.