| Comparing DB parameters [message #580047] |
Tue, 19 March 2013 13:16  |
 |
BeefStu
Messages: 208
Registered: October 2011
|
Senior Member |
|
|
I recently discovered that there was a difference in my QA and prod environments,
which I have since rectified.
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE 1
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
I found a script that allows me to compare values from v$parameter
set pagesize 1000
col name format a28
col local format a20
col remote format a20
select local.name, local.value local,
remote.value remote,
substr(local.isdefault, 1,1) || '/' ||
substr(remote.isdefault,1,1) "Default"
from v$parameter local,
v$parameter@remote_link remote
where local.name=remote.name
and lower(local.value)<>lower(remote.value)
order by local.name
/
Is there other SQL code or another methode out there that would help me
find differences in my DB's such as the method_opt setting, which don't appear in v$parameter.
Thanks to all who answer.
|
|
|
|
|
|
|
|
|
|
| Re: Comparing DB parameters [message #580686 is a reply to message #580057] |
Tue, 26 March 2013 18:01  |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
I have one that does it in one swoop.
ECSCDAS1S > @vp_sp_dblink
Enter value for remote_tns: cscdap1p
Enter value for local_tns: cscdas1s
Table ALAN_REMOTE created.
270 rows selected from ecscdap1p@cscdap1p.
270 rows inserted into ALAN_REMOTE.
270 rows committed into ALAN_REMOTE at ecscdas1s@cscdas1s.
DB NAME VALUE
------ ------------------------- -----------------------------------------------
LOCAL cluster_database_instance 2
REMOTE cluster_database_instance 4
LOCAL db_cache_size 0
REMOTE db_cache_size 5368709120
LOCAL db_flashback_retention_ta 1440
REMOTE db_flashback_retention_ta 7200
LOCAL db_name CSCDAS
REMOTE db_name CSCDAP
LOCAL db_unique_name CSCDAS
REMOTE db_unique_name CSCDAP
LOCAL dg_broker_config_file1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1
REMOTE dg_broker_config_file1 +CCDA_DATA_01/CSCDAP/DR1CSCDAP.DAT
LOCAL dg_broker_config_file2 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2
REMOTE dg_broker_config_file2 +CCDA_DATA_01/CSCDAP/DR2CSCDAP.DAT
LOCAL dg_broker_start FALSE
REMOTE dg_broker_start TRUE
LOCAL dispatchers (PROTOCOL=TCP) (SERVICE=CSCDASXDB)
REMOTE dispatchers (PROTOCOL=TCP) (SERVICE=CSCDAPXDB)
LOCAL fal_client
REMOTE fal_client CSCDAP
LOCAL fal_server
REMOTE fal_server DR_CSCDAP
LOCAL instance_name CSCDAS1
REMOTE instance_name CSCDAP1
LOCAL local_listener (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=
REMOTE local_listener (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=T
LOCAL log_archive_config
REMOTE log_archive_config DG_CONFIG=(CSCDAP,DR_CSCDAP)
LOCAL parallel_server_instances 2
REMOTE parallel_server_instances 4
LOCAL remote_listener stgcdadb-scan:1521
REMOTE remote_listener PRDCDADB-SCAN:1521
LOCAL service_names CSCDAS1, CDA_RO_SERVICE, CMS_PV_WR_SERVICE, CDA
REMOTE service_names CSCDAP1, CMS_WR_SERVICE, CMS_PV_WR_SERVICE, CDA
LOCAL shared_pool_reserved_size 312056217
REMOTE shared_pool_reserved_size 524288000
LOCAL spfile +CCDA_DATA_01/cscdas/spfilecscdas.ora
REMOTE spfile +CCDA_DATA_01/CSCDAP/SPFILECSCDAP.ORA
The full sql follows.
set pages 50
column name format a25
column startup_name format a40
column dblink_name format a40
set feedback off
set termout off
set wrap off
drop table alan_vp purge;
drop table alan_remote purge;
set termout on
@vp_sp_dblink_copy &remote_tns &local_tns
create table alan_vp as
select * from alan_remote
minus
select NAME,upper(value) value from v$parameter;
select 'LOCAL' db,NAME,value from v$parameter
where name in (select name from alan_vp)
and upper(name) not like '%DEST%'
union
select 'REMOTE' DB,name,value from alan_vp
where upper(name) not like '%DEST%'
order by name,db;
set termout off
drop table alan_vp purge;
drop table alan_remote purge;
set termout on
set feedback on
Where the called "vp_sp_dblink_copy.sql" looks like the following:
copy from e&1/my_password@&1 TO e&2/my_password@&2-
create alan_remote using select NAME,upper(value) value from v$parameter-
where value is not null and name not in ('control_files')-
;
The only thing that is hard coded it the userid in the last sql. Notice that I put the letter "e" infront of the tns name to form my userid. I do this because I set my prompt to the username and this alerts me as to which instance I am in. This helps me to avoid changing production by mistake. You can just replace the "e&1" and the "e&2" with another parameter or hard code the userids that you signon with.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
