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
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
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
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;
Subscribe to:
Posts (Atom)