ORA-27492 Oracle scheduler job doesn’t run

Scheduled a job inside database but it doesn’t run.

Try this

SQL> exec dbms_scheduler.run_job(‘job1’,false);

ORA-27492: unable to run job “<job_name>”: scheduler unavailable

Oracle recommended following solution in Doc ID 1190580.1

1- Log in as SYS

2- Startup Database in upgrade mode:
SQL> startup upgrade

3- Activate the scheduler.
SQL> exec dbms_ijob.set_enabled(true);

4- Restart the Database
SQL> shutdown immediate
SQL> startup

5- Re-run the Job

Reference

DBMS_SCHEDULER.RUN_JOB (
   job_name                IN VARCHAR2,
   use_current_session     IN BOOLEAN DEFAULT TRUE);
Posted in Misc, Oracle Database | Tagged , , , | Leave a comment

Install emcli

I used emctl in monitoring target machine. However, it is less powerful compare to emcli which exists in OMS server by default. Furthermore, it has a bug which prevent creating blackout on pluggable database. So it is time to install emcli in those database servers  managed by Oracle EM 13c.

Install emcli

$ wget --no-check-certificate https://my_OMS_server:7802/em/public_lib_download/emcli/kit/emclikit.jar
$ export ORACLE_HOME=/u01/12.2.0/dbhome_1
$ export JAVA_HOME=$ORACLE_HOME/jdk
$ $JAVA_HOME/bin/java -jar emclikit.jar -install_dir=/emcli_dir/emcli

Setup emcli

$ ./emcli setup -dir=/emcli_dir/emcli -url=https://my_OMS_server:7802/em -user=sysman

Run emcli

$ export EMCLI_STATE_DIR=/emcli_dir
$ ./emcli create_blackout -name=test1 -add_targets="my_pdb:oracle_pdb" -reason="test" -jobs_allowed -schedule="duration:-1"
Blackout "test1" created successfully

 

 

Posted in oem, Oracle Database, Uncategorized | Tagged , , , , | Leave a comment

Remove OEM agent and relevant monitoring targets

Once I decommissioned Oracle databases from specific server. It is time to clean up EM 13c agent and all monitoring targets on top of it.

At first, shutdown agent.
$ emctl stop agent

Now, it is time to remove them.
$ emcli login -username=sysman
Enter password : ***
Login successful
$ emcli sync
Synchronized successfully

$ emcli delete_target -name=”myhost:3872″ -type=”oracle_emd” -delete_monitored_targets -async
myhost deleted successfully

Posted in oem, Oracle Database, Uncategorized | Tagged , , , | Leave a comment

Export Oracle database with Data Pump API DBMS_DATAPUMP

This is the alternative way to unload/load data against Oracle database. It could help in scenario DBA prefer to use SQL script or has to utilize PL/SQL tool.

Example of export specified database schemas in parallel.

SQL>
create or replace procedure system.my_expdp_schemas(
   schemas_list varchar2 default null,
   directory varchar2 default 'x1',
   dumpfile varchar2 default 'expdp_x_%U.dmp',
   logfile varchar2 default 'expdp_x.log',
   parallel varchar2 default 2) 
is
   l_dp_handle       NUMBER;
   l_status             varchar2(200);
BEGIN
   l_dp_handle := DBMS_DATAPUMP.open('EXPORT','SCHEMA');
   DBMS_DATAPUMP.add_file(l_dp_handle,dumpfile,upper(directory));
   DBMS_DATAPUMP.add_file(l_dp_handle,logfile, upper(directory),
      filetype=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
   DBMS_DATAPUMP.metadata_filter(l_dp_handle,'SCHEMA_LIST',
      schemas_list);
   DBMS_DATAPUMP.set_parallel(l_dp_handle,parallel);
   DBMS_DATAPUMP.start_job(l_dp_handle);
   DBMS_DATAPUMP.wait_for_job(l_dp_handle,l_status);
   dbms_output.put_line( l_status );
END;
/
Procedure created.

SQL> 
set serveroutput on
SQL> 
exec my_expdp_schemas(q'['SCHEMA1','SCHEMA2']');
COMPLETED

PL/SQL procedure successfully completed.

It also help when run data pump on pluaggable database.

SQL>
connect / as sysdba
alter session set container=pdb;
exec my_expdp_schemas(q'['SCHEMA1','SCHEMA2']');
Posted in Oracle Database | Tagged , , | Leave a comment

Enable DEBUG mode in EM 13c trace file

Important Note: it doesn’t need downtime in OMS.

— Receive exiting OMS logging mode
$ ./emctl get property -name “log4j.rootCategory” -module logging
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
SYSMAN password:
Value for property log4j.rootCategory at Global level is WARN, emlogAppender, emtrcAppender

— Enable DEBUG mode
$ ./emctl set property -name “log4j.rootCategory” -value “DEBUG, emlogAppender, emtrcAppender” -module logging
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
SYSMAN password:
Property log4j.rootCategory has been set to value DEBUG, emlogAppender, emtrcAppender for all Management Servers
OMS restart is not required to reflect the new property value

— Reset it to WARNING mode
$ ./emctl set property -name “log4j.rootCategory” -value “WARN, emlogAppender, emtrcAppender” -module logging
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
SYSMAN password:
Property log4j.rootCategory has been set to value WARN, emlogAppender, emtrcAppender for all Management Servers
OMS restart is not required to reflect the new property value

Posted in oem, Oracle Database | Tagged , , , , , , | Leave a comment

Copy files from remote to remote through local host

Well, I just realized I have to copy oracle data pump files from Data Center A to Data Center B and they can’t reach each other. But I can reach both of them from my local machine, named C. In other words:  I can ssh C->A and C->B but neither A->B or B->A. I need to copy files from A->B. How?

And the answer is SSH tunnel with remote port forwarding, then run either scp or rsync command.

# I have special ssh port 2222 in Data Center A and got ssh key as well

[userC@machineC]$ ssh -R localhost:10000:remote_host_B:22 userA@remote_host_A -p 2222 -i ~/.ssh/myKeyA

[userA@remote_host_A ~]$ scp -P 10000 file_to_copy userB@localhost:new_name

OR

[userA@remote_host_A ~]$ rsync -e “ssh -p 10000” -avz file_to_copy userB@localhost:new_folder

 

Posted in Linux, Oracle Database | Tagged , , , , | Leave a comment

Run Python script in Oracle database (2)

Well, Oracle database has another way to run Python script, not need to involve Java this time. External table support external runnable thing (such as OS command, C program, script) with key word preprocessor! actually, it is a better deal when I need those Python script to feed Oracle database.

As oracle document said. ”

If the file you want to load contains data records that are not in a format supported by the ORACLE_LOADER access driver, then use the PREPROCESSOR clause to specify a user-supplied preprocessor program that will execute for every data file. Note that the program specification must be enclosed in a shell script if it uses arguments.

The preprocessor program converts the data to a record format supported by the access driver and then writes the converted record data to standard output (stdout), which the access driver reads as input.

This feature started from 10.2.0.5.

I found 2 good examples in following URLs
-Linux shell script https://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf
-Windows bat file
http://www.oracle-developer.net/display.php?id=513

So I got my example to feed Oracle database from Python script output.

#!/usr/local/bin/python3
import sys
from faker import Faker
fake=Faker('en_CA')

try:
x = max(1,int(sys.argv[1]))
except:
x = 1

for i in range(x):
a1=(''.join(fake.email())).replace('\n',', ')
a2=(''.join(fake.phone_number())).replace('\n',', ')
print('|'.join([a1,a2]))

And it needs a simple shell script to hide this Python script plus parameters.

/tmp/python/script/myDemoX.py 100

The key part is external table.

create directory Python_script as '/tmp/python/script';
create directory Python_conf as '/tmp/python/conf';
create directory Python_log as '/tmp/python/log';
grant read,execute on directory Python_script to system;
grant read on directory Python_conf to dbuser;
grant read,write on directory Python_log to dbuser;

DROP TABLE dbuser.myDemo_ext purge;
CREATE TABLE dbuser.myDemo_ext
(
email VARCHAR2(100),
phone VARCHAR2(20)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY Python_conf
ACCESS PARAMETERS
(
RECORDS DELIMITED by NEWLINE
BADFILE Python_log: 'myDemo.bad'
DISCARDFILE Python_log: 'myDemo.discard'
LOGFILE Python_log: 'myDemo.log'
PREPROCESSOR Python_script:'myDemoX.sh'
FIELDS OPTIONALLY ENCLOSED BY '"' LRTRIM MISSING FIELD VALUES ARE NULL (
email char(30) TERMINATED BY '|' ,
phone char(30) TERMINATED BY ''
)
)
LOCATION ('file')
)
REJECT LIMIT UNLIMITED;

# don't forget create a dummy file

$ touch /tmp/python/conf/file

SQL> create table mydemo as select * from mydemo_ext;

Table created.

SQL> select * from mydemo;

EMAIL PHONE
-------------------------------------------------- --------------------
william68@gmail.com 642-274-9739
lauren22@diaz.com 665-515-7129 x645
thomasnicolas@frank.net (568) 409-4511
zmunoz@hotmail.com (700) 240-4337
birdmichelle@yahoo.com 543 449 7777
davidrobinson@blankenship.org 749.171.8616
hillkristen@gmail.com 137-263-6943

..

It is easier than Java method I talked before.

Posted in Oracle Database, Python | Tagged , , | Leave a comment