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.