Saturday, 5 January 2019

Alter database open fails with ORA-00600 kcratr_nab_less_than_odr

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[16], [54566], [54579], [], [], [], [], [], [], []


SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=
b.group# and b.status='CURRENT';

MEMBER
--------------------------------------------------------------------------------

    GROUP# STATUS
---------- ----------------
E:\APP\TARIG\ORADATA\ORCL\REDO01.LOG
         1 CURRENT


SQL>Shutdown abort ;

Take a OS Level back of the controlfile (This is to ensure we have a backup of current state of controlfile)

SQL>Startup mount ;

SQL> shutdown abort;
ORACLE instance shut down.
SQL> Startup mount ;
ORACLE instance started.

Total System Global Area 3390558208 bytes
Fixed Size                  2180464 bytes
Variable Size            1929382544 bytes
Database Buffers         1442840576 bytes
Redo Buffers               16154624 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 19769913 generated at 01/03/2019 16:07:47 needed for thread 1
ORA-00289: suggestion :
E:\APP\TARIG\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2019_01_05\O1_MF_1_16_%U_.ARC
ORA-00280: change 19769913 for thread 1 is in sequence #16


Specify log: {=suggested | filename | AUTO | CANCEL}
E:\APP\TARIG\ORADATA\ORCL\REDO01.LOG
Log applied.
Media recovery complete.

Hit Enter


SQL>Alter database open resetlogs ;

Saturday, 29 December 2018

RMAN-06035: wrong version of recover.bsq, expecting 11.2.0.1, found 11.2.0.4

RMAN-00554: initialization of internal recovery manager package failed RMAN-03000: recovery manager compiler component initialization failed RMAN-06035: wrong version of recover.bsq, expecting 11.2.0.1, found 11.2.0.4



This error message means you are not picking up the correct rman executable as you might have multi Oracle home installed.

Windows: Delete files older than 7 days using a BAT file

REM Remove files backup older than 7 days
forfiles /p "F:\Concept\dumps" /s /m *.* /c "cmd /c Del @path" /d -7

Tuesday, 25 December 2018

Send an email from APEX Cloud CLOUD_SCHEDULER.create_job

create or replace procedure "PROC_PURGE_MISCELLANEOUS"
is
v_days number(15);
v_legal_id   number(15);
V_COUNTER NUMBER(15):=0;
v_security_group_id number(20);

BEGIN
 SELECT workspace_id   into  v_security_group_id   FROM apex_applications where rownum < 2;


apex_util.set_security_group_id(p_security_group_id =>v_security_group_id    );

for i in (select VARIABLE_VALUE c_days , to_number(DESCRIPTION) C_LEGAL_EMPLOYER_ID   from   CONFIGURATION  where  VARIABLE_CODE like 'PURGE%') loop

select  count(1) into V_COUNTER from EMPREQ_REQUESTS  where LEGAL_EMPLOYER_ID = i.C_LEGAL_EMPLOYER_ID and CREATION_DATE < sysdate - i.c_days;
--delete from EMPREQ_REQUESTS  where LEGAL_EMPLOYER_ID = i.C_LEGAL_EMPLOYER_ID and CREATION_DATE < sysdate - i.c_days;
end loop;

  APEX_Mail.Send( 'm.almasri@Tahaluf.ae', 'm.almasri@Tahaluf.ae',
                  'Number of records got deleted are :'||V_COUNTER,
                  '',
                  'Purging job ....' );
  APEX_Mail.Push_Queue;
END;



--  Send an email from APEX Cloud
BEGIN
  APEX_Mail.Send( 'm.almasri@Tahaluf.ae', 'm.almasri@Tahaluf.ae',
                  'This is some test plain text',
                  'This is some test HTML',
                  'Test Email Subject' );
  APEX_Mail.Push_Queue;
END;
/



-- Creating a job which will run daily at 22:00
BEGIN
  CLOUD_SCHEDULER.create_job (
    job_name        => 'JOB_PURGE_MISCELLANEOUS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN  PROC_PURGE_MISCELLANEOUS; END;',
    repeat_interval  => 'FREQ=DAILY',   
    start_date => TO_TIMESTAMP_TZ('2018-12-24 22:00:00.000000000 ASIA/MUSCAT','YYYY-MM-DD HH24:MI:SS.FF TZR'),
    enabled         => TRUE);

END;
/


-- Drop existing JOB
BEGIN
  CLOUD_SCHEDULER.DROP_JOB (
     job_name => 'JOB_PURGE_MISCELLANEOUS',
     force    => TRUE);
END;
/


-- Check the status of running jobs
SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'JOB_PURGE_MISCELLANEOUS';


ORA-20001: This procedure must be invoked from within an application session.

If we schedule a dbms_job that sends an email in Apex, we could encounter the following error:

ORA-20001: This procedure must be invoked from within an application session.

If you are using Apex_mail to send an email, the error will be of the form:

ORA-20001: This procedure must be invoked from within an application session. ORA-06512: at "APEX_050000.WWV_FLOW_MAIL", line 371 ORA-06512: at "APEX_050000.WWV_FLOW_MAIL", line 497 ORA-06512: at "APEX_050000.WWV_FLOW_MAIL", line 529 ORA-06512: at "APEX_050000.WWV_FLOW_MAIL_API"

This is because the context is not set and we are trying to send an email via APEX from DBMS_JOB.

We can set the context, we need to set the security group ID.

select workspace_id from apex_applications where application_id = :p_app_id;
 
You would know your application ID.
 
apex_util.set_security_group_id(p_security_group_id => workspace_id);
 
You can put the above in a loop for multiple workspaces

Thursday, 6 December 2018

Install jq in Redhat Linux



wget -O jq https://github.com/stedolan/jq/releases/download/jq-1.6/jq-linux64
chmod +x ./jq
cp jq /usr/bin

Friday, 23 November 2018

Mysql temporary root password



# grep "temporary password" /var/log/mysqld.log
2018-11-23T07:09:49.796912Z 1 [Note] A temporary password is generated for root@localhost: 8)13ftQG5OYl

Number of Visitors