Difference between dbms_output.put, dbms_output.put_line, dbms_output.new_line

Objective: Behavior of dbms_output.put, dbms_output.put_line, dbms_output.new_line

Applies to Oracle Database.

dbms_output.new_line need to be used followed by dbms_output.put to display the buffer value to the output screen or to be printed (OR) would need to use dbms_output.put_line instead of dbms_output.put.

SQL> set serveroutput on;

SQL> begin
  2  dbms_output.put('hello');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> begin
  2  dbms_output.put_line('hello');
  3  end;
  4  /

SQLPLUS Output:

hello

PL/SQL procedure successfully completed.

SQL> begin
  2   dbms_output.put('hello');
  3  dbms_output.new_line;
  4  end;
  5  /

SQLPLUS Output:

hello

PL/SQL procedure successfully completed.

dbms_output.put by itself will not display or print to the output screen.  It needs to pair up with dbms_output.new_line to successfully display the output.

Things To Check For Performance issue on Exadata system

Objective: To check on the performance on Exadata.

Applies to Engineered Systems X2, X3 and other Database machines.

Diagnostic Steps:

1. Check on the date and time of issue.  Check before and after events if any around that time.

2. Database instance alert log from all the nodes.

3. AWR (Automatic Workload Repository) reports collected for 30 - 60 minutes statistics from all the nodes around the time of the event.

4. Get Hanganalyze dump, collect the trace when the Performance event or any blocking or bottle neck issues occur.  Trace should be collected when the event occurs.  Collecting hanganalyze trace after the Performance issue occurs will not record any significant information.

Collect the hanganalyze trace during the time you are seeing the block or hang or performance issue.

sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
exit

5. OS Watcher data from all the nodes.

For the particular day of oswatcher logs:

# cd /opt/oracle.oswatcher/osw/archive
# find . -name '*YY.MM.DD*' -print -exec zip /tmp/Exa_`hostname`-12.zip {} \;

Please note 'YY.MM.DD' is the actual day of the oswatcher logs.

For Example: 13.01.03 if the Performance event occurs on January 3, 2013.

6. Identify the Machine where it occurs Exadata quarter rack, a half rack, or a full rack system.  Check for any recent changes at Hardware and Software level.

For example: Exadata Full rack X2-2 system (or) X2-8 system with any recent changes to Database, Patch, Patch bundle, OS patch, Network changes etc.

- What RDBMS version are the Compute Nodes running?
- What Exadata Bundle Patch are the Compute Nodes running?
- What software version are the Storage Cells running? (This can be obtained by running "imageinfo" on the cells)?

Recompilation of APPS (E-Business) Objects

Objective: To recompile or validate APPS (E-Business) objects that are invalid.

Applies to Oracle E-Business Suite (11i or R12).

Case: Recompilation of APPS schema is done through AD Admin Utility during Oracle APPS upgrade or migration process or when custom packages are used in APPS schema or when any object becomes invalid.

Steps:

1. Recompile the APPS schema through adadmin

2. Clear the cache

3. Bounce the Apache server

4. Re-try the process

5. Run the following Queries to check on invalids if any:

For Example: APPS  is the Schema name and AP is the Module name.

SELECT owner,object_type,object_name, status
FROM DBA_OBJECTS
WHERE status='INVALID'
and owner in ('APPS', 'AP')
group by owner,object_name,object_type, status;

SELECT owner,object_type,object_name, status
FROM DBA_OBJECTS
WHERE status='INVALID'
order by 3,2;

How To Read Engineered System Exacheck Report (Oracle Exadata Assessment Report)?

Objective: To read and understand exacheck report.

Report: Oracle Exadata Assessment Report

Applies to Exadata, Exalogic and other Engineered Systems.

Normally when you run or execute exacheck script, you would get a zip archive file.  

For example: exachk_essmdb01_084414_094940.zip

Upon unzipping archive file to a separate directory, you will find number of out files, rep files, sql files, xml files.  The files that are of interest are exachk_essmdb01_084414_094940.html and exachk_versions.html 

1. exachk_ess...html file shows up all the required information.
Sample Images of exachk_essblahblah.html file: 

2. exachk_versions.html shows up all the software and firmware versions.
Sample Images of exachk_versions.html file:

Reference / Read More:

Oracle Enterprise Manager System Monitoring Plug-In Metric Reference Manual for Oracle Exadata Storage Server
Release 1.2.4.0.0, Part Number E13105-05
Chapter 2 Oracle Exadata Storage Server Reports
URL: http://docs.oracle.com/cd/E11857_01/em.111/e13105/sage_reports.htm

Oracle White Paper: Oracle Maximum Availability Architecture
URL: http://www.oracle.com/technetwork/database/features/availability/exadata-consolidation-522500.pdf

Oracle Enterprise Manager System Monitoring Plug-In Installation Guide for Oracle Exadata Storage Server
Release 1.1.4.0.0, 1.2.4.0.0, and 1.2.4.2.0, Part Number E14591-10
Section: Oracle Enterprise Manager
URL: http://docs.oracle.com/cd/B16240_01/doc/install.102/e14591/pisge.htm

Oracle Enterprise Manager System Monitoring Plug-In Installation Guide for Oracle Exadata Storage Server
Release 1.0.3.0.0, Part Number E12651-04
Section: Oracle Enterprise Manager
URL: http://docs.oracle.com/cd/B16240_01/doc/install.102/e12651/pisag.htm

To Identify And Resolve Database Objects Time stamp Mismatch

Objective: To check mismatch in Database Object Timestamp and to resolve such mismatches.

Applies to Oracle 10g Release 2, and Oracle 11g.

Script to check mismatch in Database Object Timestamp.

The following query should return no rows when object Timestamp are in sync.

      select do.obj# dobj, po.obj# pobj , p_timestamp, po.stime p_stime
      from sys.obj$ do, sys.dependency$ d,  sys.obj$ po
      where P_OBJ#=po.obj#(+)
      and D_OBJ#=do.obj#
      and do.status=1 /*dependent is valid*/
      and po.status=1 /*parent is valid*/
      and po.stime!=p_timestamp /*parent timestamp not match*/
      order by 2,1;

If there are any object time stamp mismatch;

Run the utlirp.sql script to invalidate all plsql objects and then run utlrp.sql to recompile them all.  Scripts utlirp.sql and utlrp.sql can be found in $ORACLE_HOME/rdbms/admin and if you open up these script files you can find instructions.

Normally upon execution of utlirp.sql/utlrp.sql all the out of sync Timestamp objects should be in sync.

Doing so should avoid any ORA-4068, ORA-4065, ORA-06508.

Oracle 11g Release 1 and Release 2 - How The Instance Process Works?

Objective: To understand how the Oracle processes a query.

Example Query: select * from employees

Applies to Oracle Database 11g or later.

Image:

Oracle 11g Database Instance 

Oracle 11g Release 1 and Release 2 Data Dictionary Views

Views Area:

DB Creation
Users and Resources
RMAN Recovery Catlog (RMAN - Recovery Manager)
TBS Management (TBS - Tablespace)
Roles and Privileges
Control Files
Storage Parameters
Auditing
Rollback Segments
Data Pump
Dispatchers
Redo Log Files
Archived Redo Log Files
Security
Undo Management
Tuning

Data Guard - Rename ASM DB Directory, Storage, DISK

Objective: To rename Automatic Storage Management (ASM) Database Directory, Storage and DISK when Dataguard is actively used.  The purpose of this is so that after migrating to Exadata servers the new primary will have the original sub directory structure that the old primary had.

Applies to RAC ASM, Engineering Systems (Exadata X2, X3) and other Architectures where ASM is at use.

Solution:

Couple of suggestions.

First approach is takes simple effort when compared to the later.

1. Do the migration to the new Exadata Standby servers first using the standby db_unique_name as you normally would.  Switch over to it as the new primary and when ready break the association to the old primary database which is currently the standby.  Then build a new standby in the Exadata primary servers where db_unique_name = db_name for the new primary in the Exadata standby servers.  Then do a switch over so that the primary database has db_unique_name = db_name and the standby has db_name and db_unique_name with different values.

This approach allows you to use Data Guard Broker and all Data Guard functions as you normally would.

2. Build a standby in the new primary Exadata servers for the migration without setting db_unique_name at all.  Then the original none Exadata primary with have db_unique_name defaulting to db_name and so will the standby.  This means you will not use Data Guard Broker because it requires db_unique_name and must do switch overs manually in Sqlplus.

Therefore your parameters would be set as follows.

Primary:

db_name =   PROD
db_unique_name is not set, so leave it out of the pfile/spfile
log_archive_dest_2 = 'service = standby ASYNC valid_for = (ONLINE_LOGFILES, PRIMARY_ROLE)'  <--  and db_unique_name is not used in this parameter.
fal_server = standby

If you are not using any references to db_unique_name do not set log_archive_config at all.   When you do not set log_archive_config it will not allow you to set db_unique_name in the log_archive_dest_2 parameter.

Standby:

db_name =   PROD
db_unique_name is not set, so leave it out of the pfile/spfile
log_archive_dest_2 = 'primary = standby ASYNC valid_for = (ONLINE_LOGFILES, PRIMARY_ROLE)'  <--  and db_unique_name is not used in this parameter.
fal_server = primary

If you are not using any references to db_unique_name do not set log_archive_config at all.   When you do not set log_archive_config it will not allow you to set db_unique_name in the log_archive_dest_2 parameter.

Log shipping to the standby works fine.  Some times if an archive is not received in the standby the Fetch Archive Log (FAL) GAP processing does not fetch the missing redo log though.  So worst case you have to do some manual GAP resolution by copying a missing archive to the standby and register it.

alter database register logfile ' full path file name ' ;

Option 1: 

For example;

primary db_unique_name PROD

standby db_unique_name PRODDG

1.  Create a standby for the existing primary database (to be migrated) on the standby Exadata servers using db_unique_name PRODDG.  Create the broker configuration. 

The storage for the standby will be in +DISKGROUP/PRODDG   sub directory

2. Switch over to the new Exadata standby as the primary database

3. When ready to disconnect from the old primary (current standby) remove it from the broker configuration and disconnect it.  You now have standalone primary database in Exadata.  

The storage for this new primary will be in +DISKGROUP/PRODDG   sub directory

4. Create a standby database using db_unique_name PROD in the Exadata primary database servers. 

The storage for the standby will be in +DISKGROUP/PROD  sub directory

   

5. Add this new Exadata standby to the broker configuration.

6. Do the swichover to the new Exadata standby as the primary database. 

Now the end result is Primary Database is in the Exadata Primary servers and the storage for Primary 

will be in +DISKGROUP/PROD  sub directory

The standby will be in the Exadata standby servers and the storage for it will be in the 

+DISKGROUP/PRODDG  sub directory

This approach is better because you can use broker to help manage.  And you get the standby built in the process.  Unless there is some reason you cannot run the Primary in the Disaster recovery (DR) site this is the best choice. 

Option 2: 

The second option is considered a workaround and should also work fine.

But may take some manual intervention to keep it in sync until you do the switchover to become the primary.

And then may take some manual intervention to keep the old primary (current standby) in sync.

Or it may not give you any problems at all.  There are lot of Businesses that run Data Guard this way. 

Reference:

URL: http://www.oracle.com/technetwork/database/features/availability/exadata-consolidation-522500.pdf
Title: Best Practices For Database Consolidation On Exadata Database Machine, An Oracle White Paper, May 2013

How To Clear ILOM FMA Faults And Reset The SP ?

Objective: To clear ILOM FMA faults and to reset the SP.

Applies to Engineered Systems (Exadata X2, X3) and other machines where ILOM FMA are used.

Solution:

1. Log into the node ILOM and once you are at the SP prompt

 % ssh -l root <IP address of Service Processor>

2. List all known faults in the system.

Example:

-> show /SP/faultmgmt
* Enter the fault management shell to obtain pertinent information about the fault.
-> start /SP/faultmgmt/shell

3. Start the Fault Management:

Are you sure you want to start /SP/faultmgmt/shell(y/n)? y
faultmgmtsp>

* Use the 'fmadm faulty' command to identify the faulty component/FRU.

4. Example of clearing a fan fault:

Show fault:
faultmgmtsp> fmadm faulty

Will list the /SYS/MB fault  with UUID
------------------- ------------------------------------ -------------- --------
Time                UUID                                 msgid          Severity
------------------- ------------------------------------ -------------- --------
2012-12-15/21:53:29 68a0c563-e609-e8fb-9fae-c03c46867474 SPX86-8002-2J  Critical

Fault class : fault.chassis.domain.boot.power-off-unexpected

FRU         : /SYS/MB
             (Part Number: 511-1213-06)
             (Serial Number: 0328MSL-1106BA12XY)

Description : Power to server is not available due to a malfunctioning component detected by CPLD.

Use the above UUID to clear the fault.

5. Clear the fault:

faultmgmtsp> fmadm repair 68a0c563-e609-e8fb-9fae-c03c46867474

show faults again / repeat till empty:
faultmgmtsp> fmadm faulty

Exit out of Fault Manager Shell:
faultmgmtsp> exit

After clearing the actual fault, please continue to reset the SP.

6. Reset the SP:

->reset /SP

Legend:

ILOM - Integrated Lights Out Manager
FMA - Fault Management Architecture
SP - Service Processor

You are all set!  All your faults are clear now.

Reference / Read More:

URL - http://docs.oracle.com/cd/E20815_01/html/E20894/gjuqk.html
Title - How to Clear Faults Using the Oracle ILOM Command-Line Interface

URL - http://docs.oracle.com/cd/E20689_01/html/E20695/z40000971312677.html
Title - Access the SP (Oracle ILOM)

URL - http://docs.oracle.com/cd/E20815_01/html/E20894/gjshy.html
Title - How to Reset the Oracle ILOM SP Using the Web Interface

Sun Server X2-8 (formerly Sun Fire X4800 M2) Diagnostics Guide, Sun Server X2-8 (formerly Sun Fire X4800 M2) Documentation Library
Section: How to Clear Faults Using the Oracle ILOM Command-Line Interface
URL: http://docs.oracle.com/cd/E20815_01/html/E20894/gjuqk.html
Applies to Exadata as well.

Oracle Solaris Administration: Common Tasks, Oracle Solaris 11 Information Library
Section: Fault Management Overview
URL: http://docs.oracle.com/cd/E23824_01/html/821-1451/gliqg.html
Applies to Exadata FMA as well.

Writing Device Drivers, Oracle Solaris 11 Information Library
Section: Oracle Fault Management Architecture I/O Fault Services
URL: http://docs.oracle.com/cd/E23824_01/html/819-3196/fmaiofs.html
Applies to Exadata FMA as well.

How And What To Check On Oracle ASM Instance?

Objective: To check on Oracle Automatic Storage Management (ASM) instance.

Version: Applies to Oracle 11g or later.

Solution:

spool asm.html
set markup html on
set echo on
set pagesize 200
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select 'this asm report was generated at: ==> ' , sysdate " " from dual;
select 'hostname associated with this asm instance: ==> ' , machine " " from v$session where program like '%smon%';
select * from v$asm_diskgroup;
select * from v$asm_disk order by group_number,disk_number;
select * from v$asm_disk_iostat order by group_number,disk_number;
select * from v$asm_client;
select * from v$asm_attribute;
select * from v$asm_operation;
select * from gv$asm_operation;
select * from v$asm_alias;
select * from v$asm_file;
select * from v$version;
show parameter asm
show parameter cluster
show parameter instance_type
show parameter instance_name
show parameter spfile
show parameter
show sga
spool off
exit

Above script will create a .html file.  If you open up the .html file you will find extensive information populated from the ASM related views.