How to pass AWS Certified Database Specialty (DBS-C01)

I got transcript recently while I took beta exam this January and passed at around 80% 🙂 how to pass this AWS Certified Database Specialty ? My answers

  1. Architecting and operating AWS databases
  2. Watch all AWS “deep dive” videos on YouTube
  3. Read through all AWS database online document

Note: different database has different weight of course.

Exam time is beyond 3 hours, it is enough for giving answers without any rush.

Good news is AWS provided 10 sample questions now (it was not there before!) at https://d1.awsstatic.com/training-and-certification/docs-database-specialty/AWS-Certified-Database-Specialty_Sample-Questions_v1.1.pdf

That is it, hope it helps.

Telerik.Web.UI.WebResource.axd

 

Posted in AWS | Tagged , | Leave a comment

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

Run Python script in Oracle database (1)

There are couple of options to run Python script in Oracle database. Why? reuse Python scripts to create data feeding Oracle.

Oracle database support Java stored procedure and I can call Java method from PL/SQL. Well, Java can also run Python script, and in couple of ways. Put these two things together, I can reuse Python code for sure. I would go through this obvious option in this post.

At first, I got a script use Python faker to generate one line random data. Why not use Java faker directly? it is overkill when you generate tiny data only from it.

#!/usr/bin/python3

import sys
from faker import Faker
fake=Faker('en_CA')

a1=(''.join(fake.email())).replace('\n',', ')
a2=(''.join(fake.address())).replace('\n',', ')
print('|'.join([a1,a2]))

Second, Java can use ProcessBuilder or RunTime to run Python script. ProcessBuilder is newer and more flexibility is there. For more details about difference between these 2 class. Check out https://stackoverflow.com/questions/6856028/difference-between-processbuilder-and-runtime-exec.

import java.io.*;

public class myDemo{

public static String data() throws IOException
{
ProcessBuilder pb = new ProcessBuilder("/tmp/mysript.py");
Process p = pb.start();

String out = "";
BufferedReader in = new BufferedReader(new InputStreamReader(p.getInputStream()));
String thisLine = "";
while ((thisLine = in.readLine()) != null) {
out = out + thisLine;
}

return out;
}
}

It is time to load this java program into Oracle database. The loadjava tool creates schema objects from files and loads them into a schema. Schema objects can be created from Java source, class, and data files. loadjava can also create schema objects from SQLJ files.

It needs following SQL database privileges to load classes:

CREATE PROCEDURE and CREATE TABLE privileges to load into your schema.

CREATE ANY PROCEDURE and CREATE ANY TABLE privileges to load into another schema.

SQL> grant create session,create table,create procedure to dbuser;

Grant succeeded.

[oracle@localhost class]$ loadjava -u dbuser/password@myDB myDemo.class

Before I can do further things inside database, I have to grant execute privileges on Python scripts.

SQL> exec dbms_java.grant_permission(upper('dbuser'),'SYS:java.io.FilePermission','/tmp/myDemo.py', 'execute' );
PL/SQL procedure successfully completed.

It is time to create Java procedure in Oracle database now and run it finally.

SQL> conn dbuser/password@myDB
Connected.

SQL> create or replace function myDemo return varchar2 as language java name 'myDemo.data() return java.lang.String';
/

Function created.

SQL> var out varchar2(200)
SQL> call myDemo() into :out;

Call completed.

SQL> print out;

OUT
--------------------------------------------------------------------------------
markmiller@lee-riley.info|1-289-233-3919

 

It works as expected, Some efforts though. Check out calling java from PL/SQL (https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjdev/calling-Java-from-PL-SQL.html#GUID-499ABE6B-4391-43C8-A527-74A6C7B0A0FF)

 

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

RDP to windows host in cloud

This is the question I was always asked recently. Well, I assume you already get at least one bastion host in cloud to protect access to cloud resource. Then build SSH tunnel for RDP access on port 3389, it is all.

It is easier for Linux machine, I have to say it 🙂

Get SSH tunnel ready at first.

$ ssh  -L 3389:windows_host:3389 opc@bastion_host -i YOUR_SSH_KEY_FILE

Then RDP to your windows host by Remmina Remote Desktop Client.
Remmina_linux

For windows machine, say I have Virtualbox installed already:-) so that I can set up SSH tunnel inside my Linux VM !

$ ssh  -L LINUX_VM_IP_ADDRESS:3389:windows_host:3389 opc@bastion_host -i YOUR_SSH_KEY_FILE

Then I can RDP to target windows host using my Linux VM IP address. 192.168.56.101 is virtualbox host-only IP address in this case.

RDP_cloud

BTW. It is helpful to read through oracle document “Bastion Hosts” (https://cloud.oracle.com/iaas/whitepapers/bastion_hosts.pdf) OR AWS “Architecture” (https://docs.aws.amazon.com/quickstart/latest/linux-bastion/architecture.html).

 

Posted in Oracle Cloud | Tagged , , , | Leave a comment