How to create a line-by-line comparison between the parameters used in two different databases

Here is a simple way to create a line-by-line comparison between two databases over a database link.

First make sure there is tns Connectivity between the database you execute the script in, and the remote database.

Then, create a datatabase link:

-- create db link from prod to test
create public database link testdb
connect to system
identified by ****
using 'testdb';

-- make sure that the link works before you proceed:
select host_name from v$instance@testdb;

The following script will spool a text file to your current directory, which can be investigated for differences between the two databases' initialization parameters:

set trimspool on
spool parameter_comparison.lst
set lines 200
col name format a40
col "test value" format a50
col "prod value" format a50
select prod.name,prod.value "prod value",test.value "test value"
from v$system_parameter prod full outer join v$system_parameter@testdb test
on prod.name = test.name;

exit

What are the different types of database links used in Oracle?

Connected user
A local user accessing a database link in which no fixed username and password have been specified.
Note that connected users does not have to be the user who created the link, but is any user who is accessing the link.

Example:
CREATE PUBLIC DATABASE LINK sales USING 'sales_db';

Fixed user
A user whose username/password is part of the link definition.

CREATE PUBLIC DATABASE LINK sales CONNECT TO scott IDENTIFIED BY tiger USING 'sales_db';

If you are logged in locally as scott and try to use the database link, a connection as scott is made on the remote side, too.
If the user scott doesn't exist in the remote database, an error will be thrown.

In some cases the database link must contain the entire connect string:

CREATE PUBLIC DATABASE LINK mydatabaselink
CONNECT TO scott
IDENTIFIED BY tiger
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=remoteserver1.domain.com)(PORT=1521))(CONNECT_DATA= (SID=proddb01)))';

Current user
The link uses the userid/password of the current user to log onto the remote database.

CREATE DATABASE LINK sales CONNECT TO CURRENT_USER USING 'sales';

Note: Current user links are only available through the Oracle Advanced Security option.


Database links are either private or public, authenticated or non-authenticated.
Specify PUBLIC to create a public database link available to all users.

If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects.