Execute postgres SQL script on cmd

To need restore database using sql file we need to execute , run the sql file with the help of cmd.

open postgres
    create database.
open cmd
    move to the bin folder of postgres
    cd C:\Program Files\PostgreSQL\9.1\bin

    then execte the command
    C:\Program Files\PostgreSQL\9.1\bin>psql -U postgres -d usda -a -f C:\Users\gurjeet\Desktop\usda.sql

CHANGE .KETTLE PATH to directory itself

 open "spoon.bat"

--just down to kettle_home commented add

set KETTLE_HOME=.

:: **************************************************
:: ** Kettle home                                  **
:: **************************************************

if "%KETTLE_DIR%"=="" set KETTLE_DIR=%~dp0
if %KETTLE_DIR:~-1%==\ set KETTLE_DIR=%KETTLE_DIR:~0,-1%

cd %KETTLE_DIR%
set KETTLE_HOME=.
set PENTAHO_JAVA=javaw
set IS64BITJAVA=0

call "%~dp0set-pentaho-env.bat"

(Highlighted in green color) 

ORA-03113: end-of-file on communication channel

While i am trying to connect with database, connection fails with below error.





SQL> startup
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             411043240 bytes
Database Buffers          360710144 bytes
Redo Buffers                5259264 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 6284
Session ID: 9 Serial number: 5





After error i look into the trace file for error and error as below.





*** 2014-09-23 18:36:23.438
*** SESSION ID:(9.5) 2014-09-23 18:36:23.438
*** CLIENT ID:() 2014-09-23 18:36:23.438
*** SERVICE NAME:() 2014-09-23 18:36:23.438
*** MODULE NAME:(sqlplus.exe) 2014-09-23 18:36:23.438
*** ACTION NAME:() 2014-09-23 18:36:23.438
 
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.

*** 2014-09-23 18:36:24.438
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 41056768 bytes disk space from 4039114752 limit
*** 2014-09-23 18:36:24.448 4132 krsh.c
ARCH: Error 19809 Creating archive log file to 'E:\APP\****\FLASH_RECOVERY_AREA\ZA1\ARCHIVELOG\2014_09_23\O1_MF_1_133_%U_.ARC'
*** 2014-09-23 18:36:24.448 2747 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 1 thread 1: 'E:\APP\****\ORADATA\ZA1\REDO01.LOG'
ORA-16038: log 1 sequence# 133 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'E:\APP\****\ORADATA\ZA1\REDO01.LOG'

*** 2014-09-23 18:36:24.488
USER (ospid: 6284): terminating the instance due to error 16038





Now connect the user,
Start the database at mount state


Startup mount;


Execute some below set of queries
SQL> show parameter alert
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE

SQL> show parameter BACKGROUND_DUMP_DEST
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      e:\app\****\diag\rdbms\za1\za1\trace

SQL> show parameter db_recovery_file_dest_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 3852M


SQL>  select name,
  2     floor(space_limit/1024/1024) "Size_MB",
  3     ceil(space_used/1024/1024) "Used_MB"
  4     from v$recovery_file_dest
  5     order by name
  6     /
NAME                                        Size_MB    Used_MB
---------------------------------------- ---------- ----------
E:\app\****\flash_recovery_area            3852       3851

 

open CMD>

1. RMAN
2. connect /
3. crosscheck archivelog all;
4. delete archivelog all completed before 'sysdate -7';
5. Prompt for Yes or No
6. Y and enter
7. exit


NAME                                        Size_MB    Used_MB
---------------------------------------- ---------- ----------
E:\app\****\flash_recovery_area            3852         36


Above 3rd and 4th command check for the archieve log file and delete the old archieve log file which is older than the week from current date.
So Now we have free space in "flash_recovery_area" .


Now we can connect the database without any hurdel

 

QV16 Create calendar dimension using min max date

In this post i have created the calendar dimension, if we have fact table, from we can get minimum and maximum date. Using these dates we can create calendar dimension which will include quater information , year, month, day, date and others.
 
ODBC CONNECT TO [oracle_scott;DBQ=ORCL ];

Base:
LOAD EMPNO,
    ENAME,
    JOB,
    MGR,
    date(HIREDATE,'DD/MM/YYYY') as HIREDATE,
    SAL,
    COMM,
    DEPTNO;
SQL SELECT *
FROM SCOTT.EMP;


minMaxdate:
Load min(HIREDATE,'DD/MM/YYYY') as minDate, max(HIREDATE,'DD/MM/YYYY') as maxDate Resident Base;

Let vminDate = num(peek('minDate',0,'minDate'));
Let vmaxDate = num(peek('maxDate',0,'maxDate'));

cal1:
load
    IterNo() as num1,
    $(vminDate) + IterNo() - 1 as Num,
    date($(vminDate) + IterNo()-1) as TempDate
AutoGenerate 1 While
$(vminDate)+IterNo()-1 <= $(vmaxDate);

cal2:
load
    Num as DateSeq,
    TempDate as TheDate,
    Month(TempDate) as month,
    num(Month(TempDate)) as MonthSeq,
    Year(TempDate) as yearSeq,
    day(TempDate) as DaySeq
Resident cal1
order by TempDate ASC;

//drop table cal1;

cal3:
LOAD
    DateSeq,
    TheDate as HIREDATE,
    yearSeq,
    month,
    MonthSeq,
    DaySeq,
    MonthSeq + (yearSeq - 1) *12 as MonthSeq1,
    Ceil(MonthSeq/3) as quarter,
    'Q'&     Ceil(MonthSeq/3) as quarter1,
    WeekDay(TheDate) as DayName
Resident cal2
order by TheDate ASC;