11.2.0.3 cluvfy comp healthcheck

Starting with Oracle Grid Infrastructure 11g release 2 (11.2.0.3) and later, you can use the CVU healthcheck command to check your Oracle Clusterware and Oracle Database installations for their compliance with mandatory requirements and best practices guidelines, and to ensure that they are functioning properly.  In addition, If you want to perform best practices checks on these databases, then you must create the cvusys user on each database and grant that user the CVUSAPP role with the SELECT privileges needed to perform the best practice checks. The SQL script is cvusys.sql in the CVU_home/cv/admin/ directory to facilitate the creation of this user. Use this SQL script to create the cvusys user on all the databases that you want to verify using CVU.

-rw-r--r-- 1 oracle oinstall 2841 Apr 27  2011 cvusys.sql
-rwxr-xr-x 1 oracle oinstall 1011 Apr 27  2011 cvu_config*

SQL> @cvusys.sql
DROP USER cvusys
          *
ERROR at line 1:
ORA-01918: user 'CVUSYS' does not exist

DROP ROLE cvusapp
          *
ERROR at line 1:
ORA-01919: role 'CVUSAPP' does not exist

Enter password for user cvusys
'Creating user cvusys...'
User created.

Grant succeeded.

Role created.

Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
................
................
................
................
Grant succeeded.


Usage:

cluvfy comp healthcheck [-collect {cluster|database}] [-db db_unique_name] [-bestpractice|-mandatory] [-deviations] [-html] [-save [-savedir directory_path]
cluvfy comp healthcheck
Verifying OS mandatory requirements
Verifying OS Best Practice
Verifying Clusterware mandatory requirements
Verifying Clusterware Best Practice
Verifying Database "wesbdr"
Please specify password for user "cvusys" : 

******************************************************************************************
Summary of environment
******************************************************************************************
System recommendations
******************************************************************************************
******************************************************************************************
Clusterware requirements
******************************************************************************************
******************************************************************************************
Clusterware recommendations
******************************************************************************************
Database recommendation checks for "wesbdr"
******************************************************************************************

RMAN backup statistics

  v$RMAN_BACKUP_JOB_DETAILS contains information about backup durations, I/O rates.  It's a good starting point from which you can begin turning the backup.
col input_bytes_display format a18
col output_bytes_display format a18
col time_taken_display format a18
set linesize 132
set pagesize 50
SELECT session_recid, input_bytes_display, output_bytes_display,time_taken_display, end_time
FROM v$rman_backup_job_details
ORDER BY end_time

SESSION_RECID INPUT_BYTES_DISPLA OUTPUT_BYTES_DISPL TIME_TAKEN_DISPLAY END_TIME
------------- ------------------ ------------------ ------------------ ---------
         7049     6.99G            965.00M          00:09:38           13-FEB-11
         7057     6.94G            948.13M          00:08:51           14-FEB-11
         7065    10.28G              1.81G          00:11:22           15-FEB-11
         7073     9.96G              1.73G          00:30:26           16-FEB-11
         7081     6.85G            784.77M          00:10:24           17-FEB-11
         7089     6.82G            784.52M          00:08:34           18-FEB-11
         7097     6.93G            814.08M          00:09:03           19-FEB-11
         7105     7.00G            830.98M          00:08:50           20-FEB-11
         7113     6.97G            822.40M          00:09:00           21-FEB-11
         7121     6.97G            821.77M          00:10:11           22-FEB-11
         7129     6.98G            822.81M          00:10:40           23-FEB-11


 Based on the result above, we should investigate what happened on 16-FEB-11 that backup took longer than usual.  As basic RMAN tuning, we have two recomendations for improving RMAN backup

  • Use incremental backup with block change tracking enabled.
  • Adjust multipexing of backup sets by adjusting filesperset (limit the number of datafiles in each backupset), maxopenfiles (limit the number of files that can be opened for reads simutaneously), diskratio (read datafiles from specified number of disks)

ORA-01157, ORA-01110.

You’ve mistakenly added a datafile to a file system instead of ASM and you see ORA-01157 or ORA-01110.  In 10gR2, you see ORA-01157 and ORA-01110 errors and the database instance is not opened.  In Oracle 11gR2 RAC, you can open all Oracle RAC instances; however the errors ORA-01157 and ORA-01110 are still reported in alert logs, so you you still need to resolve the errors by moving it from a file system to ASM.


Total System Global Area 1073741824 bytes
Fixed Size                  2089576 bytes
Variable Size             230690200 bytes
Database Buffers          838860800 bytes
Redo Buffers                2101248 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 139 - see DBWR trace file
ORA-01110: data file 139: '/apps/oracle/product/10.2.0/DB04/dbs/DG_DBA_DD501'

Scenarios 1:   Datafile is belonged to SYSAUX tablespace.  You can bring SYSAUX tablespace offline and using RMAN to move the file to ASM.

Step 1 – check for file name and file number
select file_name, file_id, tablespace_name from dba_data_files where tablespace_name = 'SYSAUX';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME
---------- ------------------------------
+DG_DATA/LAX/datafile/sysaux.279.768593301
         2 SYSAUX

/tmp/DG_DATA
        27 SYSAUX

Step 2
SQL> alter tablespace SYSAUX offline;
Tablespace altered.

Step 3
LAX1 > rman target /
RMAN> copy datafile 27 to '+DG_DATA';

Starting backup at 22-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=937 instance=LAX1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00027 name=/tmp/DG_DATA
output file name=+DG_DATA/LAX/datafile/sysaux.305.778612925 tag=TAG20120322T172203 RECID=2 STAMP=778612925
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 22-MAR-12

Step 4
RMAN> switch datafile 27 to copy;
datafile 27 switched to datafile copy "+DG_DATA/LAX/datafile/sysaux.305.778612925"

 Step 5
SQL> alter tablespace sysaux online;
Tablespace altered.

 Senarios 2:  Database is in noarchivelog mode.  The datafile is a regular data / index type files..

Follow above step 1, 3, 4

Step 6
SQL > alter database datafile 27 online


Scenarios 3:  Database is in Archivelog mode.  The data file is a regular data / index type files...

Follow step # 1, 3, 4, 7, 8

Step 7—Recover the datafile

RMAN> run {
 allocate channel d1 device type disk;
 allocate channel d2 device type disk;
 recover datafile 27;
 }

allocated channel: d1
channel d1: sid=1522 instance=EDOCPRD1 devtype=DISK

allocated channel: d2
channel d2: sid=1582 instance=EDOCPRD1 devtype=DISK

Step 8
SQL > alter database datafile 27 online


So Many Open File Descriptors /shm/dev

This only happens in Automatic Memory Management when memory_max_target and memory_target are set.

ls -ltr /dev/shm | wc -l
79

Why 79?  checking the memory_max_target = 316M.  The granule size is 4MB, therefore 316MB/4MB = 79. The number of files in /dev/shm = MEMORY_TARGET / granule size


Counting the number of ASM processes
ps -ef | grep -i +ASM1 | wc -l
37

lsof /dev/shm | wc -l
2923  ==> why so many?

There are 37 background processes were created, each background process opens 79.  Therefore, 37 x 79 = 2923

TCP and UDP parameters

The  default maximum Linux TCP buffer sizes are too small.Oracle recommendations for related TCP and UDP parameters are following:

net.core.rmem_max = 4194304 
net.core.rmem_default=262144
net.core.rmem_max= for 11g: 4194304 For 10g: 2097152 (With RDS use at least 4194304)
net.core.wmem_default=262144
net.core.wmem_max=1048576 (With RDS use at least 2097152)
ip_local_port_range 9000 65500

Oracle Trace Analyzer --TRCANLZR or TRCA


Oracle Trace Analyzer, TRCANLZR or TRCA, is a SQL trace profiling tool for analyzing your trace files.  Here are the major sections of a TRCA report that provide you a lot of useful diagnostic information than tkprof.

·         Response Time Summary
·         Overall Time and Totals
·         Non-Recursive Time and Totals
·         Recursive Time and Totals
·         Top SQL
·         Non-Recursive SQL
·         SQL Genealogy
·         Individual SQL
·         Overall Segment I/O Wait Summary
·         Hot I/O Blocks
·         Gaps in Trace
·         ORA errors in Trace
·         Transactions Summary
·         Non-default Initialization Params
·         Trace Header
·         Tool Data Dictionary
·         Tool Execution Environment
·         Tool Configuration Parameters

You need to download the tool from Oracle Support.  Once you’ve downloaded it, the setup is very easy by executing the /trca/install/trcreate.sql.

SETUP:

cd /software/tools/trca/install

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @tacreate.sql
Uninstalling TRCA, please wait
TADOBJ completed.
SQL>
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL> REM If this DROP USER command fails that means a session is connected with this user.
SQL> DROP USER trcanlzr CASCADE;
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> SET ECHO OFF;
TADUSR completed.
TADROP completed.

Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key

Optional Connect Identifier (ie: @PROD): @LABPOC1


Define the TRCANLZR user password (hidden and case sensitive).

Specify TRCANLZR password:
Re-enter password:


Set up TRCANLZR temporary and default tablespaces

Below are the list of online tablespaces in this database.
Decide which tablespace you wish to create the TRCANLZR tables
and indexes.  This will also be the TRCANLZR user default tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for tools data is not supported.

Wait...


TABLESPACE_NAME                FREE_SPACE_MB
------------------------------ -------------
TOOLS                                     55
TOOLS_OLD                                 98
LAB_I                                   830
USERS                                   1130
LAB_D                                 65632

Above is the list of online tablespaces in this database.
Decide which tablespace you wish to create the TRCANLZR tables
and indexes.  This will also be the TRCANLZR user default tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for tools data is not supported.

Tablespace name is case sensitive.


Default tablespace [UNKNOWN]: TOOLS_OLD

DEFAULT_TABLESPACE
------------------------------
TOOLS_OLD

Choose the TRCANLZR user temporary tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.

Wait...

TABLESPACE_NAME
------------------------------
TEMP

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP

TEMPORARY_TABLESPACE
------------------------------
TEMP

Type of TRCA repository

Create TRCA repository as Temporary or Permanent objects?
Enter T for Temporary or P for Permanent.
T is recommended and default value.

Type of TRCA repository [T]:


TACUSR completed.
No errors.
SQLT$STAGE: write test file tasqdirset.txt
sqlt$_trca$_dir_set.open_write_close: ORA-29283: invalid file
operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file
operation
SQLT$STAGE: created
SQLT$STAGE: READ,WRITE access granted to SQLTXPLAIN
SQLT$STAGE: READ,WRITE access granted to TRCANLZR
SQLT$STAGE: write test file tasqdirset.txt
SQLT$STAGE: read test file tasqdirset.txt
…..
TRCA$INPUT2: read test file tasqdirset.txt
TRCA$INPUT2: get attributes for file tasqdirset.txt
TRCA$INPUT2: /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
Connected.

TAUTLTEST completed.

no rows selected

TACOBJ completed.
tool_owner: "TRCANLZR"
role_name: "PUBLIC"
Creating Package Specs TRCA$G
No errors.
Creating Grants on Packages

Tool Version
----------------
11.4.4.2

Install Date
----------------
20120320

Directories
--------------------------------------------------------------------------------------------------------------------------------
TRCA$INPUT1(VALID)      /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
TRCA$INPUT2(VALID)      /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
TRCA$STAGE(VALID)       /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
user_dump_dest          /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
background_dump_dest    /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace

Libraries
--------------------------------------------------------------------------------------------------------------------------------
VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE TRCA$E /* $Header: 224270.1 tacpkge.pks 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE TRCA$G /* $Header: 224270.1 tacpkgg.pks 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE TRCA$P /* $Header: 224270.1 tacpkgp.pks 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE TRCA$R /* $Header: 224270.1 tacpkgr.pks 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE TRCA$T /* $Header: 224270.1 tacpkgt.pks 11.4.3.1 2011/06/17 csierra $ */
VALID PACKAGE TRCA$X /* $Header: 224270.1 tacpkgx.pks 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE BODY TRCA$I /* $Header: 224270.1 tacpkgi.pkb 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE BODY TRCA$E /* $Header: 224270.1 tacpkge.pkb 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE BODY TRCA$G /* $Header: 224270.1 tacpkgg.pkb 11.4.4.2 2012/02/02 csierra $ */
VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE BODY TRCA$X /* $Header: 224270.1 tacpkgx.pkb 11.4.2.7 2011/04/08 csierra $ */
TACPKG completed.

Taking a snapshot of some Data Dictionary objects, please wait...

17:48:23 => refresh_trca$_dict_from_this
17:48:23 -> purge_trca$_dict
17:48:23 dict_state_before_purge
17:48:23 -----------------------
17:48:23 -> print_dict_state
17:48:23 dict_refresh_days :
17:48:23 dict_refresh_date :
17:48:23 dict_database_id  :
…..
17:48:45 <- print_dict_state
17:48:45 <= refresh_trca$_dict_from_this

PL/SQL procedure successfully completed.
Snapshot of some Data Dictionary objects completed.

TAUTLTEST completed.
TACREATE completed. Installation completed successfully.
SQL>

TRACING:

alter session set events ‘10046 trace name context forever, level 12’;
alter system set tracefile_identifier=’mytrace’;
<Your SQL statement here…>
alter session set events ‘10046 trace name context off’;


SQL> START trcanlzr.sql /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace/LABPOC1_ora_23740_mytrace.trc

Parameter 1:
Trace Filename or control_file.txt (required)


Value passed to trcanlzr.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace/LABPOC1_ora_23740_mytrace.trc

Analyzing /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace/LABPOC1_ora_23740_mytrace.trc

To monitor progress, login as TRCANLZR into another session and execute:
SQL> SELECT * FROM trca$_log_v;

... analyzing trace(s) ...



Trace Analyzer completed.
Review first trcanlzr_error.log file for possible fatal errors.
Review next trca_e43354.log for parsing messages and totals.

Copying now generated files into local directory


TKPROF: Release 11.2.0.3.0 - Development on Tue Mar 20 17:59:31 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


  adding: trca_e43354.html (deflated 89%)
  adding: trca_e43354.log (deflated 84%)
  adding: trca_e43354.tkprof (deflated 82%)
  adding: trca_e43354.txt (deflated 84%)
  adding: trcanlzr_error.log (deflated 81%)
test of trca_e43354.zip OK
deleting: trcanlzr_error.log
Archive:  trca_e43354.zip
  Length     Date   Time    Name
--------    ----   ----    ----
   107234  03-20-12 17:59   trca_e43354.html
    15891  03-20-12 17:59   trca_e43354.log
     8367  03-20-12 17:59   trca_e43354.tkprof
    52391  03-20-12 17:59   trca_e43354.txt
--------                   -------
   183883                   4 files

File trca_e43354.zip has been created

TRCANLZR completed.

SESSION_TRANSFORM


SESSION_TRANSFORM procedure modifies or customizes the output generated by the dbms_metadata.get_ddl procedure.  If you like to extract DDL structure for a table without storage, constraint, reference constraint for a table, you can set these parameters to false.  See example below…

SQL> begin dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', false);
  2   dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'REF_CONSTRAINTS',false);
  3  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR',TRUE);
  4  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS', false);
  5  end;
  6  /

PL/SQL procedure successfully completed.
SQL> select dbms_metadata.get_ddl('TABLE','POLICY') from dual;