Tuesday, November 26, 2013

SAP Transaction Codes (T-CODE) for Basis Admins

If you are a Basis Admin/ABAP developer you must have some knowledge of SAP Transaction codes(T-CODE).

Each function in SAP has a Transaction code (or t-code) and it consists of letters, numbers or both. By suing the Transaction code you can go to task and start the faction in a single step.

Before you learn about SAP Transaction codes, you need to understand transaction Control in SAP.

Transaction Control in SAP - Below commands are commonly used

n + Transaction code – To end current system task and go to new task
/o + Transaction – To create new session and go to new task without closing prior session
/o  – To list all existing or open sessions
/n  To terminate the transaction ( F 15 or Shift +F3 )
/i  To terminate the current session
/nend  – To terminate all separate sessions and log off.
/h To turn on Debugging mode

Below are the few transaction codes I have used and come across during the Basis Administration.

SAP User/Group/Role/Profile Administration SM01 – Lock Transactions
SM04 – Overview of Users
SU1 – Maintain Own user Addresses
SU3 – Maintain users Own Data
SU01 – User Maintenance
SU02 – Maintain Authorization Profiles
SU03 Maintain Authorizations
SU05- Maintain Internet Users
SU10 – User Mass Maintenance
SU12 – Mass Changes to user Master Records
SU20 – Maintain Authorization Fields
SU21 – Maintain Authorization Objects
SU22 – Authorization object usage in Transaction
SU24 – Authorization object check under Transactions
SU25/SU26 – Upgrade tool for profile generator
SU53 – Evaluate user Authorization check
SUIM – User information System
SUGR – Maintain user Groups
SUMM– Global User Manager
SUPC – Role Profiles
PFCG – Role Maintenance

SAP Jobs, Events and Batches
SM36 – To Define Background job
SM37 – To view Background job Overview
SM50 – Work Process Overview (Alternatively you can use SM66)
SM66 –System Wide work process over view
SM51 – To Display list of SAP Systems/Active application servers
SM34 – View cluster maintenance
SM49 – Execute External Operating system Commands
SM69 – Maintain external Operating system commands
SM62 – Define and Display Events
SM64 – Release of an event or administration of events

SAP System/Client Administration, Analysis , Run Time, Logs
SCC4 – Client Administration
SCC5 – Delete Client
SCC8 – Client Export
SCCL – Local Client copy
SCC9 – Remote Client Copy
ST01 – System Level trace
ST06 – Operating System Monitor
ST22 – ABAP dump analysis
SM21 – Online System Log Analysis
AM13 – Administrative Update requests
SE30 – Runtime analysis

SAP Database Administration, Backup/Recovery, Performance, Logs
DB02 – Tables and Indexes Monitor
DB13 - DBA Planning Calendar (Schedule Backups, Maintenance jobs)
DB12 – Overview of Database Backup Logs and Status
DB14 – Display DBA Operation Logs
DB15 – Data archiving from Database tables.
DB16/DB17 – Database system Check
DB24 – Administration/Monitoring of Database Operations
DB 26 –Display and history of Database parameters
DBCO – Maintain Database connection information
DBACOCKPIT - Start DBA Cockpit
SM12 – Display and delete locks
ST04 – Database performance Monitor
ST06 – Database Tune Summary
ST06 – Monitor Memory Resources
ST05 – Performance Trace

SAP Object Level, Paths, Connections
SUC3 – SAP table History/change log
SCMP – Table comparison.
SM58 – Transactional RFC
SM59 – RFC Destinations/Connections
AL11 – SAP file Directories
AL22 – Dependent objects display
FILE – Cross-Client File Names/Paths
SCU0/OY19 – Customizing Cross-System Viewer
SM56 – Number range buffers
SNRO – Number range Objects

Satishbabu Gunukula, Oracle ACE

Friday, November 1, 2013

OPATCH prereq failed with checkConflictAgainstOHWithDetail not executed

Recently I was working on applying a PSU One-Off Patch, before applying patch it is necessary to get conflict resolution with existing patch information.

I was running below command to get one-off patches conflict with the PSU and received error

$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./9352164

Invoking OPatch

Oracle Interim Patch Installer version
Copyright (c) 2007, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /home/oracle/product/10.2.0/db_1
Central Inventory : /home/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version :
OUI version :
OUI location : /home/oracle/product/10.2.0/db_1/oui
Log file location : /home/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2013-10-29_15-58-39PM.log
Invoking prereq "checkconflictagainstohwithdetail"
The location "./9352164/README.html" is not a directory or a valid patch zip file.
Prereq "checkConflictAgainstOHWithDetail" not executed
PrereqSession failed: Invalid patch location

OPatch failed with error code 73

You may see below error when you check the log file

INFO:Invoking prereq "checkconflictagainstohwithdetail"
INFO:The location "./9352164/psu_root.sh" is not a directory or a valid patch zip file.
INFO:Prereq "checkConflictAgainstOHWithDetail" not executed
SEVERE:OUI-67073:PrereqSession failed: Invalid patch location.
INFO:Finishing PrereqSession at Tue Oct 29 16:22:07 PDT 2013
INFO:Stack Description: java.lang.RuntimeException: Invalid patch location.
INFO:StackTrace: oracle.opatch.opatchprereq.PQSession.parseBaseDirListFile(PQSession.java:2118)
INFO:StackTrace: oracle.opatch.opatchprereq.PQSession.checkconflictagainstohwithdetail(PQSession.java:1424)
INFO:StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO:StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
INFO:StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
INFO:StackTrace: java.lang.reflect.Method.invoke(Method.java:324)
INFO:StackTrace: oracle.opatch.PrereqSession.process(PrereqSession.java:251)
INFO:StackTrace: oracle.opatch.OPatchSession.main(OPatchSession.java:1937)
INFO:StackTrace: oracle.opatch.OPatch.main(OPatch.java:619)

Solution: You will see this error when you don’t have a latest Opatch version. Go to My Oracle support and download the latest patch.

Irrespective of the Oracle version (9i,10g,11g) you will see this issue when you don’t have latest Opatch version.

Steps to install the newer version of OPATCH
1. Copy the patch into ORACLE_HOME
2. Rename the old “Opatch” directory
$ mv OPatch OPatch_bak
3. Unzip the file patch
4. Check new OPatch version
$ opatch version
You should be able to see new Opatch version.

Refer Metalink Notes:-
Master Note For OPatch (Doc ID 293369.1)
How To Download And Install The Latest OPatch Version [Article ID 274526.1]

Satishbabu Gunukula, Oracle ACE

Wednesday, October 16, 2013

INVISIABLE Columns and MULTIPLE Indexes on Same Set of Columns in Oracle 12C

As you know Oracle 11g introduced Virtual Column and Invisible indexes. Now Oracle 12c introduced new enhancements in this area.

1. Invisible Column
2. Multiple Indexes on the same set of Columns

Invisible Column:
In Oracle 12c you can able to add a column to table in the invisible fashion. The invisible column will not show up in DESCRIBE statement and also column won’t appear in queries for ex: - SELECT * query
You can add an INVISIABLE COLUMN or you can modify an existing COLUMN as INVISIBLE.

SQL> CREATE TABLE test1 (a number, b varchar2 (10), c date);
Table created.

Table altered.

SQL> DESC test
Name Null? Type

As you see the column “D” is not visible. Let’s insert the data into table.

SQL> INSERT INTO test VALUES (1,'Test1',sysdate,100);
1 row created.

SQL> commit;
Commit complete.

A   B       C
--- ------ ---------- 
1 Test1 10-OCT-13

We are able to insert the data into INVISIBLE column, but not able to see the data using “SELECT *” query. Let’s try to query the data referring the columns explicitly in SELECT statement.

SQL> SELECT a,b,c,d from test;
A  B        C                 D
-- ------- ------------ --------
1  Test1  10-OCT-13 100

Now we are able to see the data from INVISIBLE column

NOTE: - In order to INSERT or SELECT data in INVISIBLE column, you must explicitly use the INVISIBLE column name

Let’s change the column to be visible and query the data.

SQL> ALTER TABLE test modify d visible;
Table altered.

SQL> SELECT * from test;
A B         C                 D
-- ------- -----------  ----------
1  Test1  10-OCT-13 100

Multiple Indexes on the same set of Columns
Prior Oracle12c, you cannot able to create another index on same column name or set of columns which already have index. In Oracle 12c, you can able to create multiple indexes the same set of columns as long as some characteristic is different and qualifying characteristics are:

· B-tree versus bitmap
· Different partitioning strategies
· Unique versus nonunique

Creating multiple indexes on the same set of columns enables transparent and seamless application migrations without the need to drop an existing index and re-create it with different attributes.

SQL> CREATE INDEX test_index1 ON TEST(a,b);
Index created.

ERROR at line 1:
ORA-01408: such column list already indexed

Here I am trying to create a BITMAP index, but it is failed. Note that one type of index is usable/visible at any given point of time.

Index created.

Now index created successfully.

ERROR at line 1:
ORA-14147: There is an existing VISIBLE index defined on the same set of

First we need to make sure that the existing index is INVISIBLE then only you can able to make the other index visiable

Index altered.

Index altered.

Satishbabu Gunukula, Oracle ACE

PACKT Publishing offers 50% off on eBooks and Videos

Hi Everyone,

PACKT Publishing offers 50% off on all eBooks and Videos until Oct 21th 2013 Midnight. It is a limited time offer and take an advantage of this Columbus Day offer to buy your favorite books for half the price.



Satishbabu Gunukula

Wednesday, October 9, 2013

SAP HANA Training & Certification (C_HANATEC_1) Experience

I was excited to see how HANA in-memory database works and finally I have attended SAP HANA Training i.e. HA100, HA200 courses from SAP America Education.

The HA100 – SAP HANA – Introduction (2-day course)
This course provides an overview on the capabilities of SAP HANA.

I felt that they would have covered some more technical details in this course. From this course you will get a chance to exercise feel and look of HANA Studio and levels of Modeling. I don’t recommend this course, unless until your company offering free training. Now days there is plenty of documentation/ Free Videos/Webinar available, I would recommend going over those to get the overview of SAP HANA.

Here are some good links 

Introduction to Software Development on SAP HANA

SAP HANA Academy

The HA200 – SAP HANA – Installation & Operations (5-day course, previously it is a 3 day course)
This course covers some important tasks of the daily work of an SAP HANA Administrator. It also provides details about troubleshooting and assuming the high-availability of SAP HANA System.

From this course you can expect some level of technical information about Architecture, administration, operations, backup & recovery and monitoring addressing primarily technology consultants and administrators.

Note that the SAP HANA Student Guides/Course materials are not enough to prepare for SAP HANA Certification. I would highly recommend to go over below documentation links as it has very detailed, in-depth technical information.

Here are some good links

Help on SAP HANA
http://help.sap.com/hana - it has lot of useful guides

SAP HANA Administration Guides

SAP HANA Security Guide

SAP HANA Server Installation Guide

Most of the SAP courses offer both Instructor Led in Physical and Virtual Classroom courses, I always prefer Instructor Led Physical as you will have direct interaction with Instructor.


I know Certification is very important part and most of us want to complete the SAP HANA Certification as it has some demand in the market. If you are seriously planning to go for certification, then don’t expect that you will get questions from SAP HANA Training Guides/Books of HA100, HA200 like other Vendors guides.

First decide which path you want to go

1. SAP Certified Application Associate - SAP HANA 1.0
2. SAP Certified Technology Associate - SAP HANA 1.0

Check below link for SAP HANA Certification Paths

I would highly recommend reading the documentation from above links and getting some hands on practice before you go for SAP HANA Certification.

SAP Offering free 30 day SAP Hana Trail, and it is very useful.

Few vendor’s offers SAP HANA Systems on Cloud and you can use their services for Practice

I have used the above documentation and hands on experience and to complete SAP Certified Technology Associate - SAP HANA 1.0(C_HANATEC_1) certification.


If this is not your fist certification with SAP then you might already know that you will get the questions from list of “Topics”. From each topic some percentage of questions will be asked, you should be able to see that information in above links.

For C_HANATECH_1 Certification, the no of questions will be 80, you must get 60% to pass the Exam and the duration is 180 min (3 hrs). From my experience this is really good amount of time to complete the exam and also to go-over flagged questions. Note that PASS percentage (60%) will NOT be decided by each topic, it will be on overall exam.

Don’t expect that you will get straight questions from book or documentation. I got many questions addressing practical experience and real-life scenarios.

1. System Security and Authorization- There is no straight questions from Security, most of the questions that I got from this section are “scenario” based. They will explain “scenario” and “Error Message” you need to correct the security issue. Some hands on experience needed in this section.

2. Installation – In this topic don’t expect that you will get the questions from HANA Couse Material. If you go through the documentation that I provided then you will be in a very comfortable position.

3. System Architecture Design - If you don’t have much hands on experience then you must concentrate on this Topic. I felt that it is easy to clear 100% questions from this section without hands on experience.

4. System Architecture Implementation – For non-experienced users this is the next easy topic. I feel that the documentation links that I provided are good enough.

5. Data Provisioning – In this section you will see 8%-12% of questions, but related to “Data Services” (Install, Admin, Architecture, Modeling, Monitoring...etc.) itself I have received around 20-25 questions. Don’t neglect “Data Services”.

I hope this is useful to you. “Best of luck for your HANA Certification…!”

Satisbabu Gunukula, Oracle ACE

Monday, September 30, 2013

ORA-00600: internal error code, arguments: [krcrfr_nohist]

Users may see below error in alert_<SID>.log file.

Errors in file /oracle/ORAC/saptrace/diag/rdbms/ORAC/ORAC/trace/ORAC_ora_6240.trc (incident=211514):
ORA-00600: internal error code, arguments: [krcrfr_nohist], [2596956098], [49126131], [],  
ORA-00600: internal error code, arguments: [krcrfr_nohist], [2596956098], [49126131], [],
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 27 21:44:47 2013
Errors in file /oracle/ORAC/saptrace/diag/rdbms/ORAC/ORAC/trace/ORAC_ora_7583.trc (incident=211585):
ORA-00600: internal error code, arguments: [krcrfi_nohist], [2596956688], [2594394265], [], [], [], [], [], [], [], [], []

Users may see this error on databases where Block change tracking enabled. The corrupted blocks in the Block Change Tracking are causing the ORA-600 [krcrfr_nohist], which is a mismatch in the Block Change Tracking information.

This applies to Oracle and later versions. You might be hitting a BUG:13701312 and check with Oracle Support to see any patch available.

Workaround: Disable and enable change tracking.

SQL> alter database disable block change tracking;
SQL> alter database enable block change tracking [using file '<file-name>'];

You may see below errors after ORA-00600

minact-scn: got error during useg scan e:12751 usn:1
minact-scn: useg scan erroring out with error e:12751
Suspending MMON action 'Block Cleanout Optim, Undo Segment Scan' for 82800 seconds

The above warning is raised while scanning UNDO segment. It indicates that undo segment scan has failed with ORA-12751 error.

The ora-12751 indicates that some part of MMON Operation is either taking too long to complete and taking too much CPU. Due to “Suspending MMON” you may not able to see AWR reports during the issue period.

ORA-12751 "cpu time or run time policy violation" in the MMON Slave Process File during Feature Usage Statistics [FUS] (Doc ID 1291296.1)
Alert log shows: "Minact-scn: Useg Scan Erroring Out With Error E:12751" Warning (Doc ID 1478691.1)

Satishbabu Gunukula, Oracle ACE

Friday, August 9, 2013

Reason: Cannot apply patch due to fatal error & CheckActiveFilesAndExecutables failed

We have seen below error when user applying latest SBP patch on Oracle Database .

$ env ORACLE_HOME=$IHRDBMS $IHRDBMS/MOPatch/mopatch.sh -v -s SAP_112036_201306_LINX8664.zip

Running prerequisite check "CheckMultipleTranslationPatches" ... UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /oracle/RACP/112_64/cfgtoollogs/opatch/opatch2013-07-24_17-44-07PM_1.log

OPatch failed with error code 73
Processing patch "SAP_112036_201306_LINX8664.zip!16056266!16056266"...failed.
Reason: Cannot apply patch due to fatal error.

In mopatch or opatch log file, user will not find enough information except “Reason: Cannot apply patch due to fatal error”. The log file mention in the mopatch log will have reason for failure.

Contents from log file /oracle/RACP/112_64/cfgtoollogs/opatch/opatch2013-07-24_17-44-07PM_1.log

[Jul 24, 2013 5:37:13 PM] Following executables are active :
[Jul 24, 2013 5:37:13 PM] Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Jul 24, 2013 5:37:13 PM] OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.Prerequisite check "CheckActiveFilesAndExecutables" failed. 

Opatch or Mopatch needs to modify some files which are still being used by some process. As you see one of the Oracle library is still active.

Find out the process which is still actively using the library /oracle/RACP/112_64/lib/libclntsh.so.11.1 and kill or stop the process.

$ /sbin/fuser /oracle/RACP/112_64/lib/libclntsh.so.11.1
/oracle/RACP/112_64/lib/libclntsh.so.11.1: 9785m

$ ps –ef |grep 9785
oraracp 9785 1 0 Jul24 ? 00:02:39 /oracle/RACP/112_64/bin/tnslsnr LISTENER_RACP -inherit
oraracp 19813 7919 0 17:25 pts/2 00:00:00 grep 9785

$ lsnrctl stop LISTENER_RACP
$ kill -9 9785

The listener process is still running and causing the issue. We have stopped the listener process and reapplied the patch successfully.


Satisbabu Gunukula, Oracle ACE

Wednesday, August 7, 2013

Database error : (CS) "Java Class not found in classpath : com.mysql.jdbc.Driver"(WIS 10901)

The users will see below error if they don’t configure JDBC connection properly to connect MYSQL database.

Database error : (CS) "Java Class not found in classpath : com.mysql.jdbc.Driver" . (WIS 10901)

Follow below troubleshooting steps

1. Locate jdbc.sbo file and make sure that you have added “ClassPath” with correct MySQL Connector jar file.

<Path>/home/boxi/ bobj/enterprise_xi40/dataAccess/connectionServer/jdbc/mysql-connector-java-5.1.25-bin.jar</Path>

2. Update environment variable CLASSPATH with correct jar file path.

3. Make sure that the MySQL Connector jar file is right version, means for 64bit O/S or app use 64bit jar file.

Common Error in ODBC:- 

1. The below error message is very common when using ODBC(.odbc.ini) connection

[01000][unixODBC][Driver Manager]Can't open lib '/home/boxi/bobj/enterprise_xi40/linux_x64/odbc/lib/libmyodbc5a.so' : /home/boxi/bobj/enterprise_xi40/linux_x64/odbc/lib/libmyodbc5a.so: wrong ELF class: ELFCLASS32

Cause: The file is not the right version

Possible Solution: The file is not 64-bits version of the libmyodbc5a.so, install the right version. Use 32bit version of the libmyodbc5a.so, if using 32bit.

Download MySQL Connector/ODBC

Satishbabu Gunukula, Oracle ACE

Error (1023040): msg from remote site and (1051293) Login fails due to invalid login credentials

Users may receive below error while running Hyperion Financial Reports and also may see similar error message in Smart-view

Error Execuring query: Error: Internal Essbase JAPI error: [Cannot perform cube view operation. Essabase Error (1023040): msg from remote site [[Thu jul 25 10:11:13 2013]Local////Error(1051293) Login fails due to invalid login credentials]]

For troubleshooting check the logs under diagnostics ($APPLICATION_CONFIG_HOME/ Middleware/user_projects/epmsystem_linux/diagnostics/logs).

When looking into the Essbase Application log found below error message…

Error executing formula for [529100_OVH]: status code [1051293] in function [@_XREF]
An error [1051293] occurred in Spreadsheet Extractor.
Transaction [ 0x89002e( 0x51ef11b9.0x3e7a ) ] aborted due to status [1051293].

By looking all the error messages, it looks like some issues with the user credentials trying to read essbase cube. When we look into the cube location alias found out the actual issue.

The user account that is using under “Location Alias” is a Microsoft Active Directory Account which is expired. Updated the “Location Alias” with Admin credentials and RESOLVED the issue.

It is always advisable to use admin credentials instead of individual users to avoid these MSAD issues in future.

To update Location Alias follow the steps.

1. Login into Essbase Admin Services console
2. Select the Reveneu/Expense cube à Edit ‘ Location Alias’
3. Enter the corrent credetials or Admin credentials.

Satishbabu Gunukula, Oracle ACE

Thursday, July 25, 2013

cannot open shared object file: No such file or directory

This is very generic error and you will see this error with many situations.

I have received this error when connecting to Mysql using isql

$ isql -v MySQL test_user testpass
[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/libmyodbc.so' : /usr/lib/libmyodbc.so: cannot open shared object file: No such file or directory
[ISQL]ERROR: Could not SQLConnect 

Cause: The Mysql cannot find the required library

Possible Solution: Locate the file libmyodbc.so, if you don’t find the file then you might be using a different version. Find the lib file that you are using ‘libmyodbc*’ and create a link.

     For ex:- ln –s <Path to file> < Link Name>
     $ ln –s /home/mysql/lib/libmyodbc3-3.51.07.so /usr/lib/libmyodbc.so

In many causes users will see this error, if they don’t include the library path in LD_LIBRARY_PATH

Users might receive below error, when connecting using “odbcinst”. This is also one of the common error.

$ odbcinst –q -s
odbcinst: SQLGetPrivateProfileString failed with . 

Cause: The environment variables are not set properly

Solution: For Linux, edit .bash_profile and add following environment variables 

export ODBCSYSINI=/etc
export ODBCINI=/etc/odbc.ini

In caseof CSH,

setenv ODBCSYSINI /etc
setenv ODBCINI /etc/odbc.ini


Configure a Connector/ODBC DNS on Unix
Problems with connecting other platforms to MySQL with ODBC
Download MySQL Connector/ODBC

Satishbabu Gunukula, Oracle ACE

Monday, July 22, 2013

Thread 1 cannot allocate new log & Checkpoint not complete

Users will see these messages when Oracle wants to reuse the redolog file, but checkpoint position is still in the log, Oracle must wait until the checkpoint completes.

Cause: In this situation either DBWR writes slowly or log switch happens before the log is completely full or log file is small.

Mon Apr 15 07:20:42 2013
Thread 1 advanced to log sequence 5021
Current log# 1 seq# 5021 mem# 0: /oracle/ORCL/redoA/redo01.log
Mon Apr 15 07:21:15 2013
Thread 1 cannot allocate new log, sequence 5022
Checkpoint not complete

If you have many updates in the system, you might need more redo groups. If you have fewer redo groups then adding more redo groups will help.

Use below syntax to add more redo groups

ALTER DATABASE ADD LOGFILE GROUP <Group No> ('<Redo log member 1 path’ ,'<redo log member 2 path>') size 500M;

If you have smaller redo log and if you see many log switches then increasing the redo size might help.

Step1: Switch logfile to make group 1 ‘INACTIVE’

SQL> Alter system switch logfile;
SQL> select group#, status from v$log;

---------- ----------------

Step2:- Drop the log group1 which is ‘INACTIVE’ and recreate with bigger size.

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1 ('/db01/ORCL/redoA/log1_m1.dbf',' /db01/ORCL/redoB/log1_m2.dbf') size 100M reuse;

Repeat step 1 and 2 until you drop and recreate all redo logs with bigger size.

It is a recommended to have 4-5 log switches per hour. You can use below Script to find the log switches on hourly basis.

set lines 120;
set pages 999;
SELECT to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
GROUP by to_char(first_time,'YYYY-MON-DD');

Click here to learn about "Private Strand Flush Not Complete"  message in alert.log

Satishbabu Gunukula, Oracle ACE

Move Datafile between DikGroups in ASM

This is one of the common issues where users make mistakes while adding new data file or tablespace and creates in different disk group.

Follow the steps to move data file between disk groups

Here we are copying a Datafile from FLASH to DATA1 Disk Group.

1. Use RMAN to copy the data file from FLASH to DATA disk group.

RMAN> BACKUP AS COPY DATAFILE ‘+FLASH/datafile/usertbs7.588.475363994’ FORMAT ‘+DATA1’;

Or you can also specify the data file number instead of full path


2. Offline the datafile that you want to move to new disk group.

SQL> ALTER DATABASE DATAFILE ‘+FLASH/datafile/usertbs7.588.475363994’ OFFLINE;

3. Switch the data file to copy

RMAN> switch datafile ‘+FLASH/datafile/usertbs7.588.475363994’ to copy;

4. Recovery the datafile

RMAN> RECOVER DATAFILE ‘+DATA1/datafile/usertbs7.588.475363994’ ;

5. Bring the data file online.

SQL> ALTER DATABASE DATAFILE ‘+DATA1/datafile/usertbs7.588.475363994’ ONLINE;

6. Delete old datafile from ASM disk group.

RMAN> DELETE DATAFILECOPY ‘+FLASH/datafile/usertbs7.588.475363994’;

7. Check the Datafile Status

Check the status of data file by running the below query, you should be able to see the status as "AVAILABLE".


Satishbabu Gunukula, Oracle ACE