SAP BASIS & LINUX: dezembro 2017

SQL Oracle 1 e SAP - Dicas

--Long RAW TABLES--------------------------------------------------------------------

select
owner c1,
table_name c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
num_rows c6,
chain_cnt c7,
chain_cnt/num_rows c8,
tablespace_name c0
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name in
(select table_name from dba_tab_columns
where
data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
)
and
chain_cnt > 0
order by chain_cnt desc
;

--LONG RAW TABLES--------------------------------------------

select table_name,data_type from dba_tab_columns
where
data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB');

--LONG Raw tables and tablespaces status--------------------------------------

select table_name, tablespace_name, num_rows, cluster_owner, PCT_USED, LAST_ANALYZED, DEPENDENCIES, (BLOCKS * 8192) from dba_tables where tablespace_name = 'TABLESPACE' order by num_rows desc;

--ORACLE JOBS PROGRESS----------------------------------------------------------

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1;

--ORACLE LONGOPS----------------------------------------------------------------

select * from v$session_longops

--Searching for a SID session---------------------------------------------------

select
*
from
v$session
where
sid = SID;

--Searching for a background job exhibiting some details------------------------

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
ORDER BY SID;

--Killing a session-------------------------------------------------------------

alter system kill session 'SID,SERIAL';

--DATAPUMP JOBS-----------------------------------------------------------------

select * from dba_datapump_jobs;

--Showing tables from a tablespace----------------------------------------------

select * from dba_tables where TABLESPACE_NAME ;

desc sys.dba_free_space

--Free space of a tablespace----------------------------------------------------

SELECT TABLESPACE_NAME,SUM(FREEMB),SUM(CONTIGUOMB)
FROM
(select tablespace_name,FILE_ID
, round(sum(bytes)/1024/1024,2) FreeMb
, round(max(bytes)/1024/1024,2) ContiguoMb
from sys.dba_free_space where tablespace_name='TABLESPACE'
group by tablespace_name,FILE_ID
having round(max(bytes)/1024/1024,2) > 70)
GROUP BY tablespace_name
ORDER BY tablespace_name;

--Free space of a tablespace----------------------------------------------------

select
a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from
(select
tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from
dba_data_files
group by
tablespace_name ) a,
(select
tablespace_name,
sum(bytes) tot_used
from
dba_segments
group by
tablespace_name ) b
where
a.tablespace_name = b.tablespace_name (+)
and
a.tablespace_name not in
(select distinct
tablespace_name
from
dba_temp_files)
and
a.tablespace_name not like 'UNDO%'
AND a.tablespace_name like '%TABLESPACE%'
order by 1;

--Space of a table--------------------------------------------------------------

select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='USER'.'TABLE';

--Size of a table---------------------------------------------------------------

SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB
FROM DBA_SEGMENTS DS
WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES) and SEGMENT_NAME like 'TABLE'
GROUP BY DS.TABLESPACE_NAME,
SEGMENT_NAME;

--Abort a Redef table job-------------------------------------------------------

BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE(
uname => 'USER',
orig_table => 'Table Origem',
int_table => 'Tabela destino#$');
END;

--Verifica paralelismo de um indice

select degree from dba_indexes where index_name LIKE 'indice';

Links úteis SAP IQ

Links úteis SAP IQ

https://launchpad.support.sap.com/#/incident/pointer/002075129400003642812017

https://launchpad.support.sap.com/#/notes/1910965

https://help.sap.com/viewer/a893f37e84f210158511c41edb6a6367/16.0.11/en-US/a87fbd6184f2101599e284403f769f66.html

https://blogs.sap.com/2014/04/30/sap-sybase-iq-how-to-restore-your-backups-to-another-system/

http://www.rocket99.com/techref/8746.html

https://help.sap.com/saphelp_iq1608_iqbackup/helpdata/en/a6/236d0484f2101591fabb9064294f1d/frameset.htm

Troubleshooting SAP IQ e BACKUP/RESTORE

Troubleshooting IQ

Verificar situação dos discos / RAW Devices

Verificar catalogo de arquivos no IQ: SELECT * FROM SYS.SYSDBFILE;

Verificar Header dos arquivos: iqheader

Comandos úteis:

Conectar no banco
dbisql -c "uid=dba;pwd=senha" -host localhost -port 2741 -nogui

Verificar header do backup:

db_backupheader

Procedures e SQL:

Espaço:
sp_iqdbspace

sp_iqfile

Alterar ou deletar dbfiles:

alter dbspace DBSPACE drop file "nome_arquivo"'

select Value from sp_iqstatus() where name like '%Main IQ Blocks Used%';

Backup:

backup database full to @BackupFileName

Verificar status do banco com filtro:

select Value from sp_iqstatus() where name like '%Main IQ Blocks Used%';
go

Restore do sistema:

Pré requisitos: os dbfiles do sistema devem ter o mesmo tamanho do banco original na mesma quantidade. O sistema destino deve ter a mesma versão da origem ou versão superior.

Iniciar em modo utility_db: start_iq -n utility_db -iqmc 40000 -iqtc 60000

Executar Restore:

dbisql -nogui -c "uid=DBA;pwd=senha;eng=utility_db;dbn=utility_db"

RESTORE database 'caminho_do_backup/DATABASE.db'
FROM 'arquivos_de_backup.dmp sem stripe';

Upload de nova versão de EPM no download center do BPC

Para upload de nova versão de EPM no download center do BPC:

Link: https://help.sap.com/doc/4c5f3dca0ead4cfb9105d18a1efeef24/10.0.28/en-US/EPMofc_10_install_en.pdf

Procedure
1. Do one of the following:
? If your version of SAP NetWeaver BW is prior to the SAP_BW Release 730 SP3, you must implement the
following SAP Notes: 1558149, 1553052, 1578760 and 1547050.
? If your version of SAP NetWeaver BW is 740, 750, 800, 801 or 810, you must implement the following SAP
Note: 2369339.
? If you need to install EPM add-in Support Package 27 or upper versions for the x64 edition, from the
Planning and Consolidation web client, please upgrade Planning and Consolidation accordingly. See SAP
note 2403723.
2. Log on to SAP NetWeaver and execute the RSBPCLD transaction.
3. In the Program field, enter UJ0_FILE_UPLOAD and click Execute (F8).
The Upload window opens.
4. In the Option and version section, select the type of update you want to perform depending on whether or not
the support package is mandatory.
EPM Add-in for Microsoft Office Installation Guide
Installing the EPM Add-in for Microsoft Office C U S T O M E R 19
Option Description
Force update
When updates are defined as "force update", a message pops up on your local machine, prompting you to
install the update. If you do not install the update, you will not be able to use the EPM add-in.
The version XXX of the EPM Add-in is now available. It must be installed before you can use the EPM Add-in
Auto update
When updates are defined as "auto update", a message pops up every time an update is available, prompting
you whether you want to install it now or later.
"Version xxx of the EPM Add-in is now available. Do you want to install it?
User update
When updates are defined as "user update", if you choose the Notify me when updates are
available option, a message pops up every time an update is available, prompting you whether you want
to install it now or later.
"Version xxx of the EPM Add-in is now available. Do you want to install it?
You can choose to select the Do not show this message again option.
5. In the File version field, enter the EPM Add-in version number (for example: 10.0.0.5054).
6. Select the Full Installer type.
7. In the Upload section, click Browse to select the EPM Add-in.exe file and then click Upload.
The Enter Transport Request dialog box opens.
8. Click the New icon.
The Select Request Type dialog box opens.
9. Select Customizing Request and validate.
The Create Request dialog box opens.
20 C U S T O M E R
EPM Add-in for Microsoft Office Installation Guide
Installing the EPM Add-in for Microsoft Office
10. Enter a short description text and click the Save icon.
11. In the Enter Transport Request dialog box, click the Validate icon.
12. Once the transport has been created, open the Transport Management System.
13. In the Import Queue of your system, select the transport you created and click Import.
Next Steps
To perform an upgrade of the EPM add-in on the server, follow the same procedure

Para verificar versões disponíveis :

SE16 -> RSBPC0_AUTO_UPDT ou UJ0_AUTO_UPDATE

Ver link: https://wiki.scn.sap.com/wiki/display/CPM/Understanding+the+UJ0_FILE_UPLOAD+Options+for+EPM+Add-in+Client+For+BPC+10+NW

Carga entre SAP BW e SAP ERP com IDOCS parados na SM58

A carga entre SAP BW e SAP ERP com IDOCS parados na SM58

Sintomas :

IDOCs ficam parados na SM58 e não chegam no BW.

O QOUT fica em status Waiting.

Execução LUW faz IDOC chegar no BW.

Solução:

Procurar por processos bloqueando a execução tRFC e qRFC, normalmente processos com longo tempo de duração na sm66/sm50

Material para consulta:

https://wiki.scn.sap.com/wiki/display/ABAPConn/Outbound+Scheduler+%28SMQS%29+remains+in+status+WAITING

.

Alterar número de processos na SUM após inicio do Update/Upgrade

Como alterar número de processos na SUM após inicio do Update/Upgrade:

Acessar a url http://servidor_sap:1128/lmsl/sumabap/PHM/set/procpar e alterar o número de processos.

DICA - ERROR: Tipo de IDOC XXXXXXX do BW diferente do tipo de IDOC do sistema fonte.

Source system no BW, ao checar aparece o erro:

BW desconhecido sistema fonte

ou

tipo de IDOC XXXXXXX do BW diferente do tipo de IDOC do sistema fonte.

Soluções possíveis:

verificar RFCs, usuários e permissões;

Verificar se entradas das tabelas EDIPORT e EDIPOA estão iguais - nota SAP 110849

Verificar intervalo de numeração de portas disponiveis na transação snum - nota 110849
--------------------------------------------------------------------------------------------------------------------------

Nota 110849:

Symptom
"Error during insert in port table" during creation of source system.

Other Terms
SourceSystem, porttable

Reason and Prerequisites
You get the error message "Error during insert in port table (E0 552)" during creation of a source system. "Please enter a valid receiver port" E0420

Solution
Take a look at table EDIPORT of your BW system and note the next free number for the field "Port". This is the adjacent number of the highest entry like 'A0000000123' for example. In this case e.g. take '124'.
Choose transaction "snum" and object "ediport".
Select "number ranges" from the menu "Goto" and here the button "status".
Change the CURRENT NUMBER of the ranges to the next free number you noted from table EDIPORT.

If the number range of your BW system is correct please check the same in your OLTP system.

Check inconsistency (port) between tables EDIPOA and EDIPORT.
If affected port is only exists in EDIPOA table, delete the entry from EDIPOA.

Also reset the buffering for all servers on system via transaction /$tab