Oracle re-create or recover parameter file
..
set linesize 300 pagesize 300
column num noprint
col value for a100
col name for a35
select num, '*' "thread#", name, value from v$parameter where num in (select num from v$parameter
WHERE (isdefault = 'FALSE' or ismodified <> 'FALSE') and name NOT LIKE 'nls%'
MINUS
select num from gv$parameter gvp, gv$instance gvi
where 1=1
and num in ( select distinct gvpa.num from gv$parameter gvpa, gv$parameter gvpb where gvpa.num = gvpb.num
and gvpa.value <> gvpb.value
and (gvpa.isdefault = 'FALSE' or gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%'
)
and gvi.inst_id = gvp.inst_id
and (gvp.isdefault = 'FALSE' or gvp.ismodified <> 'FALSE')
and gvp.name not like 'nls%')
union
select num, to_char(thread#) "thread#", name, value from gv$parameter gvp, gv$instance gvi
where 1=1
and num in (select distinct gvpa.num from gv$parameter gvpa, gv$parameter gvpb
where 1=1
and gvpa.num = gvpb.num
and gvpa.value <> gvpb.value
and (gvpa.isdefault = 'FALSE' or gvpa.ismodified <> 'FALSE')
and gvp.name not like 'nls%'
)
and gvi.inst_id = gvp.inst_id
and (gvp.isdefault = 'FALSE' or gvp.ismodified <> 'FALSE')
and gvp.name NOT LIKE 'nls%'
order by 1, 2;
=============
Oracle re-create or recover parameter file
SELECT
initcap(p2.value) ||' startup with '|| DECODE(p1.value, NULL,'pfile', 'spfile') || ' on '|| to_char(startup_time,'dd/mon/yyyy hh24:mi:ss') "Instance startup info"
FROM v$parameter p1, v$parameter p2, v$instance
WHERE p1.name = 'spfile'
and p2.name='db_name';
-- NUMB
col name format a25
col value format a20
-- type is_defaul is_se is_system is_modified is_ad
col description format a20
set linesize 200 pagesize 200
select
nam.indx+1 numb,
nam.ksppinm name,
val.ksppstvl value,
nam.ksppity type,
val.ksppstdf is_default,
decode(bitand(nam.ksppiflg/256,1),1,'True','False') is_session_modifiable,
decode(bitand(nam.ksppiflg/65536,3),1,'Immediate',2,'Deferred' ,3,'Immediate','False') is_system_modifiable,
decode(bitand(val.ksppstvf,7),1,'Modified',4,'System Modified','False') is_modified,
decode(bitand(val.ksppstvf,2),2,'True','False') is_adjusted,
nam.ksppdesc description
from x$ksppi nam,
x$ksppsv val
where nam.indx = val.indx
/
select
nam.indx+1 numb,
nam.ksppinm name,
val.ksppstvl value,
nam.ksppity type,
val.ksppstdf is_default,
decode(bitand(nam.ksppiflg/256,1),1,'True','False') is_session_modifiable,
decode(bitand(nam.ksppiflg/65536,3),1,'Immediate',2,'Deferred' ,3,'Immediate','False') is_system_modifiable,
decode(bitand(val.ksppstvf,7),1,'Modified',4,'System Modified','False') is_modified,
decode(bitand(val.ksppstvf,2),2,'True','False') is_adjusted,nam.ksppdesc description
from x$ksppi nam,x$ksppcv val
where nam.indx = val.indx
;
/
===
set head off
set feed off
set pages 0
--spool $ORACLE_ADMIN/pfile/initPROD920.ora.sav
SELECT
'######################################' ||CHR(10)||
'# file : init'||upper(value)||'.ora #' ||CHR(10) ||
'# Date : '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')|| ' #' ||CHR(10)||
'######################################' ||CHR(10) ||CHR(10)
FROM v$parameter
WHERE name = 'db_name';
SELECT '# '|| DESCRIPTION ||CHR(10) ,NAME||'='|| DECODE(VALUE,'TRUE',VALUE,'FALSE',VALUE,
DECODE(SIGN(INSTR(VALUE,' ')),1,'( "'||REPLACE(VALUE,', ','",'||CHR(10)||' "')||'" )', '"'||VALUE||'"'))||DECODE(upper(ISDEFAULT),'TRUE',' # (Default value) - ',NULL)|| CHR(10)
FROM v$parameter
WHERE ISDEFAULT = 'FALSE'
ORDER BY num;
SELECT '############### END ################' FROM DUAL;
--spool off;
======
col param_name format a40 heading "Parameter Name"
col param_value format a38 heading "Parameter Value" word_wrap
col isdefault format a7 heading "Default|Value"
col isses_modifiable format a10 heading "Session|Modifiable"
col issys_modifiable format a10 heading "System|Modifiable"
col ismod heading "Is|Modified"
-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name from v$database;
-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today from dual;
-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;
set termout on
set feedback off
set head on
select name param_name,value param_value,isdefault,ismodified ismod,isses_modifiable,issys_modifiable from v$parameter
order by param_name;
spool off
ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on
===
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
set linesize 300 pagesiz 300
col NAME for a45
col BVAL for a20
col EVAL for a20
select e.parameter_name name
, b.value bval
, decode(b.value, e.value, NULL, e.value) eval
from dba_hist_parameter b
, dba_hist_parameter e
where b.snap_id(+) = :BgnSnap
and e.snap_id = :EndSnap
and b.dbid(+) = :DID
and e.dbid = :DID
and b.instance_number(+) = 1
and e.instance_number = 1
and b.parameter_hash(+) = e.parameter_hash
and ( nvl(b.isdefault, 'X') = 'FALSE'
or nvl(b.ismodified,'X') != 'FALSE'
or e.ismodified != 'FALSE'
or nvl(e.value,0) != nvl(b.value,0)
)
and e.parameter_name not like '\_\_%' escape '\'
order by e.parameter_name;
----
set linesize 600 pagesize 300
col sid for a20
col name for a20
col value for a70
col host_name for a15
select sid,SYS_CONTEXT('USERENV', 'HOST', 15) host_name ,name,value from v$spparameter where isspecified='TRUE'
--and name='event'
order by 2;
==========
set linesize 300 pagesize 20
col NAME for a20
col VALUE for a20
col DESCRIPTION for a70
select name, value, isdefault, isses_modifiable, issys_modifiable,isinstance_modifiable, isdeprecated, description from v$parameter where upper(name) = 'PARALLEL_MAX_SERVERS';
set linesize 400 pagesize 300
col name for a30
SELECT name,
CASE
WHEN type = 1 THEN 'Boolean'
WHEN type = 2 THEN 'String'
WHEN type = 3 THEN 'Integer'
WHEN type = 4 THEN 'Parameter file'
WHEN type = 5 THEN 'Reserved'
WHEN type = 6 THEN 'Big integer'
END AS parameter_type,
default_value
FROM v$parameter
WHERE ispdb_modifiable = 'TRUE'
AND isdeprecated = 'FALSE'
ORDER BY name;
=================
SELECT name,
CASE
WHEN type = 1 THEN 'Boolean'
WHEN type = 2 THEN 'String'
WHEN type = 3 THEN 'Integer'
WHEN type = 4 THEN 'Parameter file'
WHEN type = 5 THEN 'Reserved'
WHEN type = 6 THEN 'Big integer'
END AS parameter_type,
default_value
FROM v$parameter
WHERE ispdb_modifiable = 'TRUE' AND isdeprecated = 'FALSE'
ORDER BY name;
