Some DB Qrys

SQL>1
DATA FILE DETAILS UNDER THE TABLESPACE
==================================================================================================
select tablespace_name, file_name, file_id
from dba_data_files
where tablespace_name = 'PSAPSR3';



SQL>3
DATA FILE USAGE AND FREE SPACE  CALCULATION
===================================================================================================
select d.tablespace_name, d.file_id, d.file_name, d.bytes allocated, nvl(f.bytes,0) free
from (select tablespace_name, file_id, file_name, sum(bytes) bytes from dba_data_files
group by tablespace_name, file_id, file_name) d,
(select tablespace_name, file_id, sum(bytes) bytes from dba_free_space group by tablespace_name, file_id) f
where d.tablespace_name in ('PSAPSR3')
and d.tablespace_name = f.tablespace_name(+)
and d.file_id = f.file_id(+)
order by tablespace_name, file_id;


SQL>3
THE FOLLOWING SQL SCRIPT WHICH SHOW YOU THE ALLOCATED, FREE AND USED SPACE IN EACH OF DATAFILES.
 ==================================================================================================

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name


SQL>4
LARGE INDEX AND SEGMENTS
======================================================================================================
SELECT * FROM ( SELECT OWNER, SEGMENT_NAME, BYTES/1024/1024/1024 SIZE_GB
FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'INDEX'
ORDER BY BYTES/1024/1024/1024  DESC ) WHERE ROWNUM <= 10
 
    SQL>5
USE BELOW SCRIPT TO DETERMIN WHICH TABLE HAS A LOT OF UNUSED SPACE.
=======================================================================================================
select a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) MBS,
round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED
from dba_segments a, dba_tables b where a.owner=b.owner
and a.owner not like 'SYS%' and a.segment_name = b.table_name
and a.segment_type='TABLE' group by a.owner, a.segment_name,
a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)
having round(bytes/1024/1024,0) >100 order by round(bytes/1024/1024,0) desc ; 


  SQL> 6
 BELOW SCRIPT WILL RETRIVE THE DATA FILE AND CREATION OR ADDED DATE;
 ======================================================================================================
select a.file_name,a.tablespace_name,b.creation_time,
a.BYTES /1024/1024/1024 Size_GB from dba_data_files a,v$datafile b
where a.file_id=b.file# order by tablespace_name;


SQL> 7
 BELOW SCRIPT WILL RETRIVE THE DATA FILE SIZE AND FREE SIZE %USAGE;
===================================================================
COL     TABLESPACE_NAME FORMAT a16
SELECT A.TABLESPACE_NAME, A.STATUS,
ROUND(C.SIZE_GB/1024/1024/1024,2) SIZE_GB,
ROUND((C.SIZE_GB-B.FREE_GB)/1024/1024/1024,2) USAGE_GB,
ROUND(B.FREE_GB/1024/1024/1024,2) FREE_GB,
ROUND(100*(1-(FREE_GB/SIZE_GB)),2) PCT_USAGE
FROM (SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES) A,
(SELECT tablespace_name,
SUM(BYTES) FREE_GB
FROM DBA_FREE_SPACE
GROUP BY tablespace_name)B,
(SELECT TABLESPACE_NAME, SUM(BYTES) SIZE_GB
FROM dba_data_files
GROUP BY tablespace_name) C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME;




SQL> 8
 BELOW SCRIPT WILL SHOW YOU THE DATABASE SESSION UPDATE PROCESS;
===================================================================

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1;
SQL> 9 BUNDLE PATCH STATUS
===================================================================select action_time, version, bundle_series, comments
from dba_registry_history
where bundle_series = 'WINBUNDLE'
order by action_time desc;


SQL> 10  Execute the following query to see if your database was started with a PFILE or SPFILE:
===================================================================SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';

Java portal to bw hana JCO error / Connection failed. Make sure that Single Sign-On is configured correctly

Error> occured  >Sun Mar 29 11:11:28,123<   
RfcException:
    message: Connect to message server host failed
Connect_PM  TYPE=B MSHOST=xxxxxxx.LOCAL GROUP=PUBLIC R3NAME=XX MSSERV=sapmsXXX  PCS=1
ERROR       partner xxxxxxxxxx:sapmsXXX' not reached
TIME        Sun Mar 29 11:11:28 2015
RELEASE     721
COMPONENT   NI (network interface)
VERSION     40
RC          -10
MODULE      nixxi.cpp
LINE        3285
DETAIL      NiPConnect2: 192.168.00.00:3600
SYSTEM CALL connect
ERRNO       10061
ERRNO TEXT  WSAECONNREFUSED: Connection refused
COUNTER     1
    Return code: RFC_FAILURE(1)
    error group: 102
    key: RFC_ERROR_COMMUNICATION
==============================

Solution:
 
The error saying that 3600 port is not communicating:192.168.00.00:3600(port name sapmsXXX) 
We have two solution apply here

• 1-first go to the host and telnet the port whether its working or not. If not working try to open that port.

 

• 2- If you cannot able to open the port, go  service file and search the sapmsXXX you can see the port 3600 just change the port number 3609 or some other which is working.( on same 36xx series)

And do the telnet with server name  3609 it will work with sapmsXXX. so careful about duplication of port. using the same service.
 
This solution I did on JCO connection issues, not to other network issue.

After changing the PORT it works fine for me.

Thanks
Yoonus
 

Register TP program

Register RFC program

rfcexec -aXXXXX_PORTAL_XXX -g XXXXXX -xsapgw00

-a = program name
-g = gateway host
 

By Default $TMP Package

Solved Solution:::

 

Some time we faced this issue, All the newly created object Like DSO, Info Object, Info Cube etc. are by default are going to $TMP package.

Even we have already a "Z" package in SAB BI.

 

So if we Perform a small setting in BI RSA1 T code then we can solve this problem.

 

Go to RSA1 and click on BI Content Tab.

 

Now in BI Content tab go to tool Menu, Click on Edit > Transport > Switch off Standard Setting.

Know more about Switch On and Off Standard Setting.

 

Switch On Standard - If we use this option then after all the development  and changes a pop will come at a time to save development in a new request. 

 

Switch off Standard - If we use this setting then all the objects will by default save on the $TMP Local package. Then we need to collect all objects in Transport Connection > Click on Object type Folder then Collect required object and Give "Z" then transport.

 

 

  

 

Thanks,

 

Yoonus

SSO- Configuration

How to configure the SSO (Single sign on) for Portal?  What are the steps needs to be taken?

 

Single Sign On with Portal to SAP Backend Systems 
  
Single Sign On (SSO) is good documented in the SAP world. This guide to give you a complete working example of how you can enable SSO in your environment.

Step 1 - Setting the logon method as Single Sign on 

1.1 Log in to your Portal as a System Administrator. 

1.2 Choose System Administration --> System Configuration --> System Landscape

1.3 Find the system you want to assign Single Sign on to and open it

1.4 Choose User Management as Property Category

1.5 Set Logon Method to SAPLOGONTICKET

What we have done now is to set the system you want to use as a Single Sign On logon method. Do this to each system you want to connect.

Step 2 - Create a Portal Certificate 

1.1 Log in to the Visual Administrator

1.2 Choose Server --> Services --> KeyStorage --> TicketKeystore

1.3 Delete SAPLogonTicketKeypair-cert and SAPLogonTicketKeypair

1.4 Choose Create (Create button in the Entry field) and  
type in the following information: 
a. mark Store Certificate  
b. Common Name: Your <SID> (just example) 
c. Entry Name: SAPLogonTicketKeypair  
d. Store Certificate: Mark it 
e. Key Length: 1024 
f. Algorithm: DSA 
g. Press Generate

Now you will have two entries in the TicketKeyStore:

SAPLogonTicketKeypair 
SAPLogonTicketKeypair-cert

Step 3 - Export the Portal certificate 

3.1 Choose Server --> Services --> KeyStorage --> TicketKeystore

3.2 Choose SAPLogonTicketKeypair-cert and press Export (Export button in the Entry field)

a. Fill in a name of the Certificate 
To keep track of your certificate, call it the SID of the Portal

b. Choose either X.509 or Base64 Encoded Format

Step 4 - Import the Portal certificate to the Backend System 

4.1 Log in to the Backend System 
In my example, I log in to ERP 2004

4.2 Run transaction STRUSTSSO2

4.3 Press Import Certificate (Button in the Certificate field)

a. Open the generated certificate from step 3 with the right file format that you choosed in step 3.4

4.5 Press Add to Certificate List button (Button in the Certificate field)

4.6 Press Add to ACL button (Button in the Certificate field)

a. Enter the <SID> of your Portal

b. Enter Client 000

4.7 Press Save

Step 5 - Export the Backend certificate to your Portal

5.1 You are still in the transaction STRUSTSSO2. Doubleclick the Owner Certificate and choose Export and store in on the file system

5.2 Log into Visual Administrator 
Choose Server --> Services --> KeyStorage --> TicketKeystore and press Load and choose the Certificate

5.3 Set the Backend System as "ACL" in the Portal 
Choose Server --> Services --> Security --> Provider --> Ticket

Choose the Authentication tab and add the following on the com.sap.security.core.server.jass.EvaluateTicketLoginModule:

trustedsys<Number>=<ABAP_SID>, <CLIENT> (for example, ABA, 200)

trustediss<Number>=<ISSUER_DISTINGUISHED_NAME> (for example, CN= ABA)

trusteddn<Number>=<SUBJECT_DISTINGUISHED_NAME> (for example, CN=ABA)

You have set up a trusted relationship between your portal and the backend system. To do so with several system, run this guide again from step 4