Category Archives: Reports

Reports

View Oracle hidden parameters

Hidden parameters in Oracle always start with an underscore.

Résultats de recherche d'images pour « hidden »

It is not possible for the DBA to see the hidden parameters with the SQL*Plus command “show parameter” or by querying v$parameter. Unless the hidden parameter is explicitly set in spfile/init.ora file.

How Can I list all Hidden Parameters set in The database?

As they are explicitely set in the init file, you can create a report that shows all the hidden parameters using the v$parameter view.
The following sql statemant lists undocumented parameters but can also be used to list documented parameters, that can be set in the spfile or init.ora file:

col name for A45
set lines 120
col value for A40
set pagesize 100
select name, value from v$parameter where name like '\_%' escape '\';

 

How can I list all hidden parameters available?

If you want to list all hidden parameters available for your version along with a description:

select 
ksppinm,
ksppdesc 
from 
x$ksppi
where 
substr(ksppinm,1,1) = '_';

How can I set the value of a hidden parameter?

You can change a hidden parameter, the same way as you would any other init.ora parameters but you need to put double quotes for the parameter name:

alter system set "_pga_max_size"=5G scope=spfile sid='*';

 

A Good DBA needs to know what hidden parameters are set in the database and their values. Especially during upgrade, database migrations or performance tuning problems.

Oracle has hundreds of initialization parameters, which are hidden and undocumented. Many savvy Oracle professionals are known to commonly adjust the hidden parameters to improve the overall performance of their systems.

Disclaimer: It is not recommended to change hidden parameter without consent of Oracle Support since Oracle can make your system unsupported. You can be responsible for data corruption, performance degradation because of bad SQL plans or other problem. the undocumented init parameters are only used in emergencies or to fix a bug. Some of these parameters are Operating system specific and used in unusual recovery situations. Hence, these parameters should be manipulated carefully and preferably not without recommendation from an Oracle Database Administrator.

 

Author: Vincent Fenoll Oracle DBA

Reports, Troubleshooting

Ultimate Database Health Check (DBMS_HM)

Leave a comment

The Oracle DBA wants to run Health Checks command with Health Monitor upon his database.

With Oracle 12c/18c, these checks can be done  on a regular basis daily/monthly:
– DB Structure Integrity Check
– CF Block Integrity Check
– Data Block Integrity Check
– Redo Integrity Check
– Transaction Integrity Check
– Undo Segment Integrity Check
– Dictionary Integrity Check
– ASM Allocation Check

Perhaps you have datafile, dictionary, block, undo, redo, or another corruption in your database? You might actually be running just fine and not even know it.

Oracle Database 12c/18c includes a framework called Health Monitor for running diagnostic checks on your database.

 

How to run a health check on the Oracle database?

BEGIN
   DBMS_HM.run_check ('Dictionary Integrity Check', 'report_dictionary_integrity');
END;
/

or

BEGIN
   DBMS_HM.RUN_CHECK (check_name     => 'Transaction Integrity Check',
                      run_name       => 'my_transaction_run',
                      input_params   => 'TXN_ID=22.87.1');
END;

Viewing the first report in text format with DBMS_HM (HTML & XML format are also available):

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.get_run_report ('report_dictionary_integrity') FROM DUAL;

Listing all the Health Check executed (Health Monitor View):

SELECT run_id,
       name,
       check_name,
       run_mode,
       status,
       src_incident,
       num_incident,
       error_number
  FROM v$hm_run;

Viewing the list of checks that can be done on your database

 SELECT name
  FROM v$hm_check
 WHERE internal_check = 'N';

Health checks accept input parameters, some are mandatory while others are optional.

 

Displaying parameter information for all health checks

  SELECT c.name check_name,
         p.name parameter_name,
         p.TYPE,
         p.DEFAULT_VALUE,
         p.description
    FROM v$hm_check_param p, v$hm_check c
   WHERE p.check_id = c.id AND c.internal_check = 'N'
  ORDER BY c.name;

Periodic database health checks help keep your database running smoothly without corruption and prevent more serious conditions from developing later.

Health Monitor checks and examine the several parts of the Oracle database stack. This tool detects data dictionary corruptions, datafile corruptions. It will check logical or physical logical block corruptions and rollback (undo) or redo corruptions.

The health checks generate reports of their findings and, in many cases, recommendations for resolving problems.

For a Healthy database!

Vincent Fenoll – Oracle OCP Database administrator in Montreal

Datapump, DDL, Security

Generate user DDL with dbms_metadata.get_ddl user

Download Brave : Secure, Fast & Private Browser with Adblocker

The oracle DBA can use these 2 scripts to generate DDL statements for a user with their roles, system and object privileges.

For Oracle 18c / 12c / 11g / 10g:

clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '

spool &&outfile..gen

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off

More information for the package dbms_metadata, function get_ddl user in the official Oracle 18c/12c documentation :
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_METADATA.html

For Oracle <10 (runs well too with 10g, 11g, 12.2 and 18c):

clear screen

accept uname prompt 'Display the DDL for this specific user: '
accept outfile prompt  ' Output filename : '

col username noprint
col lne newline

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

spool &&outfile..gen

prompt  -- generate user ddl
SELECT username, 'CREATE USER '||username||' '||
       DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
              'IDENTIFIED BY VALUES '''||password||''' ') lne,
       'DEFAULT TABLESPACE '||default_tablespace lne,
       'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
  FROM DBA_USERS
 WHERE USERNAME LIKE UPPER('%&&uname%')
    OR UPPER('&&uname') IS NULL
 ORDER BY USERNAME;

SELECT username, 'ALTER USER '||username||' QUOTA '||
       DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
       ||' ON '||tablespace_name||';' lne
  FROM DBA_TS_QUOTAS
 WHERE USERNAME LIKE UPPER('%&&uname%')
    OR UPPER('&&uname') IS NULL
 ORDER BY USERNAME;

col grantee noprint

select grantee, granted_role granted_priv,
       'GRANT '||granted_role||' to '||grantee||
       DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
  from dba_role_privs
 where grantee like upper('%&&uname%')
         UNION
select grantee, privilege granted_priv,
       'GRANT '||privilege||' to '||grantee||
       DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
  from dba_sys_privs
 where grantee like upper('%&&uname%')
 order by 1, 2;

spool off

Another use of this procedure is to copy a user account from one Oracle instance to another. With the same password, grants and roles without using the expdp/impdp tools.

Another method to retreive the Data Description Language for an Oracle user with all roles and Privileges:

With datapump (impdp) you can use the parameter sqlfile=My_file.sql you can easily get DDL from dumpfile:
http://www.oracle-scripts.net/standard-datapump-use/

Author: Vincent Fenoll
Compatibility: Oracle 18c, 12c, 11g

Reports, Troubleshooting

Flash Recovery Area (FRA)

Leave a comment

Download Brave : Secure, Fast & Private Browser with Adblocker

Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it’s usage, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

To Enable  or Disable the FRA, it’s in another post here.

-- Use (MB) of FRA
set lines 100
col name format a60

select 
   name,
  floor(space_limit / 1024 / 1024) "Size MB",
  ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest;

-- FRA Occupants
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA
show parameter db_recovery_file_dest

-- Size, usage, Reclaimable space used 
SELECT 
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM 
  V$RECOVERY_FILE_DEST A,
  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
  SPACE_LIMIT, 
  SPACE_USED , 
  SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;

-- Or change the FRA to a new location (new archives will be created to this new location):
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';

FLASH RECOVERY AREA architecture

It is important to monitor regularly space usage in the fast recovery area. The Oracle DBA must make sure that the FRA is large enough to contain backups and other recovery-related files.

If the PERCENT_FULL value (V$RECOVERY_FILE_DEST) is frequently close to 100% after several recent backups; consider allocating more space for your flash recovery area or decrease the retention time of your backups.

Oracle Database provides two views to monitor fast recovery area space usage, V$RECOVERY_FILE_DEST and V$RECOVERY_AREA_USAGE.

A best practice is to be generous on FRA size!

Compatibility: Oracle 18c, 12c, 11g, 10g
Author: Vincent FENOLL – Oracle DBA Montreal

DDL, Reports

How to extract DDL for scheduler jobs owned by SYS?

Leave a comment

We can’t export scheduler jobs which are owned by SYS user, even in Oracle 18c and 12c. If you try to get DDL for scheduler jobs owned by SYS you’ll hit ORA-31603 error.

The solution of this problem is to copy the scheduler job to another user (e.g. vfenoll) and then extract the DDL with dbms_metadata.

 

Here is the steps with an example:

1-  create the temporary user
create user vfenoll identified by tempuser1234;

2-  build the copy statement
set head off lines 130
select ‘exec dbms_scheduler.copy_job(”SYS.’ || job_name || ”’, ”VFENOLL.’ || job_name || ”’);’ from dba_scheduler_jobs where owner=’SYS’;

3-  select the jobs you want to copy (probably at the end of the list) and execute the copy_job for your selection
[…]
exec dbms_scheduler.copy_job(‘SYS.PURGE_FGA_TRAILS’, ‘VFENOLL.PURGE_FGA_TRAILS’);
[…]

4-  Build the DDL statements
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF SERVEROUT OFF VER OFF

exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘SQLTERMINATOR’, true);

exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘PRETTY’, true);

spool gen_sys_scheduler_jobs.sql
select ‘select dbms_metadata.get_ddl(”PROCOBJ”,”’ || job_name || ”’,”VFENOLL”) from dual;’ from dba_scheduler_jobs where owner=’VFENOLL’;
spool off

5-  extract the DDL for scheduler jobs
set long 2000 head off
spool sys_scheduler_jobs.sql
@gen_sys_scheduler_jobs.sql
spool off
[…]

BEGIN
dbms_scheduler.create_job(‘”PURGE_FGA_TRAILS”‘,
job_type=>’PLSQL_BLOCK’, job_action=>
‘BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(2, TRUE); END;’
, number_of_arguments=>0,
start_date=>NULL, repeat_interval=>
‘FREQ=HOURLY;INTERVAL=24’
, end_date=>NULL,
job_class=>'”DEFAULT_JOB_CLASS”‘, enabled=>FALSE, auto_drop=>TRUE,comments=>
‘Audit clean job = ”PURGE_FGA_TRAILS”’
);

COMMIT;
END;
/

[…]
spool off

— Format the output, change enabled=>FALSE   to TRUE, remove the COMMITs.

You can also recreate the jobs in Cloud Control with the all the informations you gathered.

 

6-  Drop the temporary user with the jobs
drop user vfenoll cascade;

 

It’s never a good idea to create jobs (or other objects) in SYS, prefer another schema :)

Here is another post to extract the DDL of a user with dbms_metadata.get_ddl user.  Get the script for the user creation also for the roles & object grant & system grants provided to this user.

 

Author: V. Fenoll Oracle DBA Montreal

Compatibility: Oracle 18c, 12c, 11g, 10g