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