Home » RDBMS Server » Server Administration » Comparing DB parameters (Ofracle 11.2.0.3.)
Comparing DB parameters [message #580047] Tue, 19 March 2013 13:16 Go to next message
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 #580054 is a reply to message #580047] Tue, 19 March 2013 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$parameter does not contain method_opt setting.
Why prevent you from using your previous query with DBMS_STATS?
SQL> col db1 format a40
SQL> col db2 format a40
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') db1, 
  2         DBMS_STATS.GET_PARAM@###('METHOD_OPT') db2
  3  from dual
  4  /
DB1                                      DB2
---------------------------------------- ----------------------------------------
FOR ALL COLUMNS SIZE AUTO                FOR ALL COLUMNS SIZE AUTO


Regards
Michel
Comparing DB parameters [message #580056 is a reply to message #580054] Tue, 19 March 2013 13:42 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Thanks for the response. I was looking for something more generic that would find me ALL the differences in one swoop. So for arguements sake, lets assume I was not
familiar with DBMS_STATS how would I find that difference? or any other differences,
which are not in v$parameter.
Re: Comparing DB parameters [message #580057 is a reply to message #580056] Tue, 19 March 2013 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search in all catalog views and packages and tell us.

Regards
Michel
Re: Comparing DB parameters [message #580686 is a reply to message #580057] Tue, 26 March 2013 18:01 Go to previous message
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.
Previous Topic: Database sessions
Next Topic: Problem with moving tables from older to the new DB
Goto Forum:
  


Current Time: Thu Mar 28 18:55:29 CDT 2024