Problem with database open ORA-19804, ORA-19809, ORA-03113

1. Try to login to database with SYS AS SYSDBA user. If the instance is idle, run the startup command.

2. If ORA-03113 occured, check the latest error in log file, C:\Oracle\diag\rdbms\SERVICE_NAME\SERVICE_NAME\alert\log.txt

3. Error description: 

ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim X bytes disk space from X bytes limit

4. Login to database with SYS AS SYSDBA user and run startup mount command.

5. Check the current recovery file size limit with query: select space_limit/1024/1024 as limit, space_used /1024/1024 as used from v$recovery_file_dest;

6. Change the limit with command alter system set db_recovery_file_dest_size = 30G scope=both; run  COMMIT; 

7. Run shutdown and startup commnds.

SAP ABAP - save table results to csv file with SQ02/SQ01

1.  Use path visible in transaction AL11
2. Create SQ02 Query to read f.e. MSEG table
3.  Put the script in the following sections of extras->code tab:

DATA:

DATA: C_SEMI(1) TYPE C VALUE ';'.
DATA: L_LINE TYPE STRING.
DATA: T_FILE(999) TYPE C.
DATA: L_TEMP TYPE STRING.

START OF SELECTION:

T_FILE = 'C:\101movement.txt'.

OPEN DATASET T_FILE FOR OUTPUT IN TEXT MODE
ENCODING DEFAULT
WITH SMART LINEFEED.
CONCATENATE
       'Material'
       'Quantity'
       'Movement'
       INTO L_LINE
       SEPARATED BY C_SEMI.
TRANSFER L_LINE TO T_FILE.

RECORD PROCESSING:

CLEAR L_LINE.
CLEAR L_TEMP.

L_TEMP =  MSEG-MENGE.


* Row creation with output to csv
  CONCATENATE
            MSEG-MATNR
            L_TEMP
            MSEG-BWART
            INTO L_LINE
            SEPARATED BY C_SEMI.
  TRANSFER L_LINE TO T_FILE.
ENDLOOP.

END OF SELECTION (after List):

close dataset T_FILE.

Oracle - create public database link to oracle db

 1. Change sqlnet.ora file from SQLNET.AUTHENTICATION_SERVICES= (NTS) to SQLNET.AUTHENTICATION_SERVICES= (NONE)

2. Create public database link with command:

CREATE PUBLIC DATABASE LINK DBLINK1

    CONNECT TO REMOTE_USER_NAME IDENTIFIED BY password

    USING '(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.2 )(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL)

    )

  )';

COMMIT;

3. Test link with command:

select * from table_name@DBLINK1;

4. Drop link with command:

DROP  DATABASE LINK DBLINK1;

COMMIT;

Oracle - calculate time difference with time_zone adjustment

Task: Calculate difference between CREATEDON  column (with UTC time_zone) and CURRENT_DATE (with local time zone from database) 

Table contents:

select CREATEDON, CURRENT_DATE from TABLE_NAME;

Query:

with time_difference as

(

select

cast(FROM_TZ(CAST(CREATEDON AS TIMESTAMP), 'UTC') at time zone (SELECT DBTIMEZONE FROM DUAL)  as date) AS Created,

cast(FROM_TZ(CAST(CURRENT_DATE AS TIMESTAMP), (SELECT DBTIMEZONE FROM DUAL)) AS DATE) AS CurrentDate

from TABLE_NAME

)

select 

to_char(Created,'YYYY-mm-dd HH24:MI') as Created,

to_char(CurrentDate ,'YYYY-mm-dd HH24:MI') as CurrentDate,

round((CurrentDate - Created) * 24 * 60) as DifferenceInMinutes

from time_difference;

Result:

Windows server 2016 - access rights for running batch jobs

To assign access rights manually (to both users and groups) use 

secpol.msc → Local Policies → User Rights Assignment → Log on as batch job.

Add group or user.

Oracle APEX (apex_23.1) - installation procedure with Oracle 19.16.0.0.0

1. Download REST services https://www.oracle.com/database/sqldeveloper/technologies/db-actions/download/ or go to C:\Oracle\product\19.0.0.0\dbhome_1\ords
3. Change ords.war => apex.war
5. 
   
6. 
   
7. 
   
8. Download APEX from https://www.oracle.com/tools/downloads/apex-downloads/
9. Extract files f.e. in C:\temp\ folder (keep the folder tree) 
   
10. Run command line and navigate to folder where apex directory was extracted (C:\Temp\apex_23.1_en\apex>)s
11. Run SQLPlus on server with Oracle database, connect with` SYS as SYSDBA (previously check if Environment variable for ORACLE_SID is configured) 
    
    
    
    
    
    
    
12. Check database status with query SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE; (should be 'ACTIVE')
13. Check parameter SHOW PARAMETER MEMORY_TARGET (should be at least '300M')
14. Check parameter SHOW PARAMETER WORKAREA_SIZE_POLICY (should be set to 'AUTO') 
    
    
    
15. Create new tablespace for APEX CREATE TABLESPACE apex DATAFILE SIZE 100M AUTOEXTEND ON NEXT 1M;
16. Run installation script @apxrtins.sql tablespace_apex tablespace_files tablespace_temp images @apexins.sql APEX APEX TEMP /i/ 
    
    
    
17. Unlock apex_public user ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK
18. Change the password for public user ALTER USER APEX_PUBLIC_USER IDENTIFIED BY "new_password";
19. Change password for admin user by running @apxchpwd.sql
20. Change password policy for APEX_PUBLIC_USER profile alter profile DEFAULT limit password_life_time UNLIMITED;
21. Check the port with query  SELECT DBMS_XDB.gethttpport FROM DUAL; If it is set to 0 change it with  EXEC DBMS_XDB.sethttpport(8080);

SQL Server - database migration to lower version of Microsoft SQL Server (backup version is incompatible with this server)

Restore Failed: The database was backed up on a server running version xx.xx.xxxx. That version is incompatible with this server, which is running version xx.xx.xxxx.

1. Go to the source server with Microsoft SQL Management Studio
2. Select database to be exported and choose: Export Data-Tier Application 
3. Save file to local disc
4. Go to destination server, choose databases and Import Data-Tier Application
   
   
    
5. Import previously created .bacpac file 
   
   
   
6. Set database name and location and press finish 
   
   
   

Baselinker - download orders & products with API getOrders() using excell vba

1. Get a key

How to:

https://baselinker.com/pl-PL/pomoc/wiedza/api/

I will XXXXXXX as a example of key = api token

2. Create a macro with POST method

• I will use JSON converter in order to retreive data from https://github.com/VBA-tools/VBA-JSON
• Method description https://api.baselinker.com/index.php?method=getOrders
• Limit of the API request is 100 (recurring function is used to pass it)
• You need to include a reference to "Microsoft Scripting Runtime" in you VBA project

Sub PostAPI(ByRef httpr, ByVal ldate)

myurl = "https://api.baselinker.com/connector.php"

httpr.Open "POST", myurl, False

httpr.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

params = "token=XXXXXXX&method=getOrders&parameters={" & Chr(34) & "date_from" & Chr(34) & ":+1" & ldate & "}"

httpr.send params

' Message box to uncomment in order to check error or result

' MsgBox (httpr.responseText)

End Sub

Sub Baselinker()

'

' PostREST Makro

'

Dim Json As Object

Dim httpRequest, ldate

Set httpRequest = CreateObject("msxml2.xmlhttp")

Sheets(1).Cells(1, 1).Value = "order_id"

Sheets(1).Cells(1, 2).Value = "date_confirmed"

Sheets(1).Cells(1, 3).Value = "delivery_address"

Sheets(1).Cells(1, 4).Value = "name"

Sheets(1).Cells(1, 5).Value = "price_brutto"

i = 2

o = 1

r = 1

While (r > 0)

    

        Call PostAPI(httpRequest, ldate)

        Set Json = JsonConverter.ParseJson(httpRequest.responseText)

        o = 1

        

        For Each Order In Json("orders")

        

         For Each Product In Order("products")

            Sheets(1).Cells(i, 1).Value = Order("order_id")

            ' Conversion of unix date format

            Sheets(1).Cells(i, 2).Value = DateAdd("s", Order("date_confirmed"), "1/1/1970 00:00:00")

            Sheets(1).Cells(i, 3).Value = Order("delivery_address")

            Sheets(1).Cells(i, 4).Value = Product("name")

            Sheets(1).Cells(i,5).Value = Product("price_brutto")

            

            ' save the last date

            ldate = Right(Order("date_confirmed"), 9)

            i = i + 1

            

            Next Product

        o = o + 1

    Next Order

    

    If o = 101 Then

        r = 1   

    Else

        r = 0

    End If

    

Wend

End Sub

How to read table from remote SAP system - reading HU from SAP EWM from SAP ECC

How to read table from remote SAP system - reading HU from SAP EWM from SAP ECC

Sample code to read HU number VLENR from table /SCWM/ORDIM_C in SAP EWM from another SAP.

DATA: T_OPTION TYPE RFC_DB_OPT.
DATA: T_OPTIONS TYPE TABLE OF RFC_DB_OPT WITH HEADER LINE.
DATA: T_FIELDS  TYPE TABLE OF RFC_DB_FLD WITH HEADER LINE.

DATA: T_DATA    TYPE TABLE OF TAB512     WITH HEADER LINE.

P_DEST =  'RFC name of the EWM system'.

* Field we are going to read
T_FIELD-fieldname = 'VLENR'.
append T_FIELD TO T_FIELDS.

* Filter over warehouse task field
CONCATENATE 'TANUM = 100012344'.
append T_OPTIONS TO T_OPTIONS.

* Connection to EWM in order to read table /SCWM/ORDIM_C

CALL FUNCTION 'RFC_READ_TABLE'
DESTINATION P_DEST
  EXPORTING
    QUERY_TABLE                = '/SCWM/ORDIM_C'
*   DELIMITER                  = ' '
*   NO_DATA                    = ' '
*   ROWSKIPS                   = 0
    ROWCOUNT                   = 999
  TABLES
  OPTIONS                    = T_OPTIONS
    FIELDS                   = T_FIELDS
    DATA                     = T_DATA.
* EXCEPTIONS
*   TABLE_NOT_AVAILABLE        = 1
*   TABLE_WITHOUT_DATA         = 2
*   OPTION_NOT_VALID           = 3
*   FIELD_NOT_VALID            = 4
*   NOT_AUTHORIZED             = 5
*   DATA_BUFFER_EXCEEDED       = 6
*   OTHERS                     = 7
          .
IF SY-SUBRC = 0.

 * result to be retrieved from T_DATA

ENDIF.