Что такое ords oracle
In the database world technology trend you have surely already heard buzz words like Hadoop and NoSQL. Around those new non-relational databases there is a common open-standard file format massively used to read and write on those new databases call JavaScript Object Notation (JSON).
If you have followed a bit few new web technologies trainings (Vue.js, Angular, React, …) each time they use a back-end database to store information the exchanges are always done through an asynchronous request using promises (Axios for the one I have used in a Vue.js project). The information are, also, always transferred using JSON format. Those exposed services by newest databases flavors are using a REpresentational State Transfer (REST) architecture and expose this is a RESTful web service or RESTful API.
To have figures to display I have decided, this time, to use the default HR sample schema that you can create using script located at:
Idea is to display and interact with employees table using a RESTful API. We would be able to display all employees or a particular one specifying an id in provided url as well as inserting, deleting and updating additional ones.
Testing has been done using a VirtualBox virtual machine running Oracle Linux Server release 7.4 and an Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit.
ORDS Administration
You will need to create an ORDS user with the "Listener Administrator" role to let SQL Developer connect to your ORDS server for administration.
The credentials are stored on the ORDS server in the following location.
With the ORDS user in place, you can now administer ORDS using SQL Developer.
Select the "View > REST Data Service > Administration" menu option.
Connect as you did for the developer pane. All configuration changes are local and need to be uploaded to the ORDS server for them to take effect.
Create a Test Database User
We need a new database user for our testing.
Create and populate a copy of the EMP table.
Assumptions and Comments
This article assumes the following.
- You already have a functioning installation of ORDS.
- The paths for the ORDS configuration match those from the ORDS installation article listed above.
- You have an Oracle database available. In this article I will be using a 12c database, but it works just the same with an 11g database.
- You have a way to call the web services. Some GET web services can be called from a browser, but the other methods require some coding, or a REST client. I used "curl" and the "Advanced REST client" extension for Chrome.
The examples in this article are based around single operations against a single table. I don't consider these "realistic" tests by any means. From a performance perspective, web services that perform DML should call transactional APIs, which may perform multiple operations in a single call, rather than effectively exposing table APIs, like we are here.
Create Basic RESTful Web Service
To let SQL Developer connect to your ORDS server, you will need to create a user through ORDS with the "SQL Developer" role.
The credentials are stored on the ORDS server in the following location.
With the ORDS user in place, you can now proceed to develop web services using SQL Developer.
Select the "View > REST Data Service > Development" menu option.
On the resulting "REST Development" pane, click the connect button.
If you don't already have a connection, click the "+" button.
Enter the connection details, including the "Username" configured on the ORDS server, then click the "OK" button.
Click the "OK" button.
Enter the ORDS credentials and click the "OK" button.
Right-click on the "Modules" tree node and select the "New Module. " menu option.
Enter the module details and click the "Next >" button.
Enter the template details and click the "Next >" button.
Enter the handler details and click the "Next >" button.
Click the "Finish" button.
Expand the tree and click on the GET handler. Click on the "Worksheet" tab. Enter the query associated with the GET handler.
All changes are performed locally, so changes need to be uploaded to the server to take effect. Right-click on the module and select the "Upload. " menu option.
Click the "OK" button.
The web service should now be available from your ORDS.
If you want to save a local copy of the configuraton, right-click on the tree and select the "Save As. " menu option, then name and save the zip file. At the time of writing, this configuration is only a XML representation of the configuration, not the SQL used to create it.
Stored Procedure (XML)
Using a stored procedure is a simple way to produce XML REST web services. To show this, create the following procedure, which uses SQL/XML to generate the XML results.
The following code creates a web service that calls this procedure.
The web service is available using the following URLs.
ORDS installation
I could have used the JDK of the Oracle home but I rated the release a bit too old:
[oracle@server1 ~]$ $ORACLE_HOME/jdk/bin/java -version java version "1.8.0_91" Java(TM) SE Runtime Environment (build 1.8.0_91-b14) Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)
As I’m rock’n’roll I have decided to install and use Java 10:
There is also an $ORACLE_HOME/ords but I have not been able to make it working so decided to replace it with the latest release available at the time of writing this post:
[oracle@server1 ords]$ java -jar ords.war version Oracle REST Data Services 18.1.1.95.1251
I have also chosen the multitenant installation even if I have only on pluggable database, the installation is called Installation Enabling Multiple Releases (Recommended). In the advanced installation process you must supply the container (CDB) information and it will eb deployed in all pluggable databases including the seed one (and also in root one for common objects and accounts).
Note:
I have discovered that the configuration directory is relative to the directory where you have started the installation so simply config for me to be in $ORACLE_HOME/ords/config..
You can have the configuration directory with:
[oracle@server1 ords]$ java -jar ords.war configdir Apr 12, 2018 12:31:09 PM INFO: The config.dir value is /u01/app/oracle/product/12.2.0/dbhome_1/ords/config
Obviously you would need to use nohup command because in interactive mode the process is stopped when you quit the terminal…
To make all PDBs addressable by Oracle REST Data Services (Pluggable Mapping) I have finally used below command. A bit different than Oracle official documentation and my DB_DOMIAN parameter is unset:
[oracle@server1 ords]$ java -jar ords.war set-property db.serviceNameSuffix '' Apr 12, 2018 12:38:47 PM oracle.dbtools.rt.config.setup.SetProperty execute INFO: Modified: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/defaults.xml, setting: db.serviceNameSuffix =
Then I have added my single pluggable database with:
It has created below configuration file:
I define the routing based on the request path prefix with (nothing original as the url will have the pluggable database name):
[oracle@server1 ords]$ java -jar ords.war map-url --type base-path /pdb1 pdb1 Apr 12, 2018 3:22:25 PM INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,,]
Публикация метрик на стороне приложений и сервисов
На серверах приложений установлен Linux или Solaris node exporter, который собирает метрики ОС (cpu, memory, disk usage & utilization), а также MIB Exporter, собирающий и публикующий параметры работы сервисов и модулей биллинговой системы.
Рассылка уведомлений
Помимо отправки уведомлений, реализованной в PL/SQL пакете, имеется возможность настроить Prometheus Alertmanager, который может отправлять алерты Slack, Teams, Telegram, Teams через специальные адаптеры, либо настроить Karma dashboard для удобного отображения алертов из Prometheus Alertmanager.
Пример сформированного алерта в Karma dashboard:
Рис.19 Пример алерта в Karma Alerts dashboard
На данный момент alertmanager планируется использовать как дополнительную опцию, только в случае явной потребности заказчика в уведомлениях по каналам, отличным от email.
Anonymous Block
The following example uses an anonymous block to call the DBMS_STATS package to gather statistics for the schema.
The following example uses EXEC (or EXECUTE ) to call the DBMS_STATS package to gather statistics for the schema.
Create a Test Database User
We need a new database user for our testing.
REST-Enabled SQL Service
From official documentation:
I have activated REST-Enabled SQL Service with:
[oracle@server1 ords]$ java -jar ords.war set-property restEnabledSql.active true Apr 12, 2018 12:41:30 PM oracle.dbtools.rt.config.setup.SetProperty execute INFO: Modified: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/defaults.xml, setting: restEnabledSql.active = true
The documentation provide below command with curl:
With Insomnia it gives:
ords07 ords08 ords09
On the right part of the above screen shots we can see the correct result: 107 rows.
Views
We can see the definition in the database using the USER_ORDS_% views.
Procedures Using the PL/SQL Web Toolkit
The following procedure includes a call to the HTP package.
If we execute the procedure it results in an error.
Run a Script
Create a file called "/tmp/tables.sql" with the following contents.
We can now make a call passing the file contents as the data using the "-d @" or "--data @" option. The output is minified, which makes it quite difficult to read, so I've formatted it for the sake of this article. You can see each statement is run separately, with its output presented in the "response" element.
JSON Documents
Rather than a plain SQL statement or script, a JSON document can be sent as a payload to REST Enabled SQL. Create a procedure with some parameters.
Create a file called "/tmp/test1.json" with the following contents.
We can now post this JSON document to the service using the "Content-Type: application/json" header.
For queries the offset and limit elements can used to page through data. Create a file called "/tmp/test2.json" with the following contents.
Make the call using the new JSON document.
Enable ORDS
We are going to assume ORDS is installed and is available from the following base URL.
We have to enable Oracle REST data services for the test schema. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name. In this example we used "hr".
Web services from the schema can now be referenced using the following base URL.
We are now ready to start.
Procedures Using DBMS_OUTPUT
The following example runs an anonymous block which makes a call to the DBMS_OUTPUT package. Provided the script enables server output, the response will contain the output.
Create PUT Web Services (Amend/UPDATE)
The PUT method is typically used to amend data and expects the parameter values to be passed in the payload. The PUT operation is often expected to perform a create if the data doesn't already exist, but I personally don't like this approach.
The source of the PUT handler can be as simple as a regular PL/SQL block containing an update statement, but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to update rows.
The following code creates a web service with a PUT handler that calls the stored procedure, passing the parameters from the payload.
The web service can be called using the following URL, method, header and payload.
Procedures Using Implicit Statement Results
The following procedure uses the implicit statement results functionality to return a result set.
When the procedure is executed the result set is displayed in the response.
ORDS setup
When trying with SYS account I have gotten a strange error:
SQL> exec ords.enable_schema(p_schema => 'hr', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr'); BEGIN ords.enable_schema(p_schema => 'hr', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr'); END; * ERROR at line 1: ORA-06598: insufficient INHERIT PRIVILEGES privilege ORA-06512: at "ORDS_METADATA.ORDS", line 1 ORA-06512: at line 1
So finally executed it with my nominative DBA account:
SQL> exec ords.enable_schema(p_schema => 'hr', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr'); PL/SQL procedure successfully completed.
Instead of using the example of the official documentation:
exec ords.define_service(p_module_name => 'examples.routes', p_base_path => '/examples/routes/', p_pattern => 'greeting/:name', - p_source => 'select ''Hello '' || :name || '' from '' || nvl(:whom,sys_context(''USERENV'',''CURRENT_USER'')) "greeting" from dual');
I have decided to try something much simpler for my first test but it failed for a strange error:
SQL> show user USER is "HR" SQL> exec ords.define_service(p_module_name => 'examples', p_base_path => 'examples/', p_method => 'GET', p_pattern => 'greeting/', - p_source => 'select sysdate from dual'); BEGIN ords.define_service(p_module_name => 'examples', p_base_path => 'examples/', p_method => 'GET', p_pattern => 'greeting/', p_source => 'select sysdate from dual'); END; * ERROR at line 1: ORA-01403: no data found ORA-06512: at "ORDS_METADATA.ORDS_INTERNAL", line 617 ORA-06512: at "ORDS_METADATA.ORDS_SECURITY", line 85 ORA-06512: at "ORDS_METADATA.ORDS_SERVICES", line 117 ORA-06512: at "ORDS_METADATA.ORDS_SERVICES", line 52 ORA-06512: at "ORDS_METADATA.ORDS", line 694 ORA-06512: at line 1
Then the magic idea came to my mind and while I was grumbling about the fact that Oracle could have used a UPPER command for account name I remembered that since 11g account are now case sensitive, and this by default:
SQL> show parameter SEC_CASE_SENSITIVE_LOGON NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_case_sensitive_logon boolean TRUE SQL> select parsing_schema, status, auto_rest_auth from ords_metadata.ords_schemas; PARSING_SCHEMA STATUS AUTO_REST_AUTH ------------------------------ ------------------------------ ------------------------------ ORDS_METADATA DISABLED ENABLED hr ENABLED ENABLED
So did a bit of cleaning with (no DISABLE_SCHEMA or using false with ENABLE_SCHEMA is not deleting the line in ORDS_METADATA.ORDS_SCHEMAS:
SQL> EXECUTE ORDS.DROP_REST_FOR_SCHEMA('hr'); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> exec ords.enable_schema(p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr'); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> select parsing_schema, status, auto_rest_auth from user_ords_schemas; PARSING_SCHEMA STATUS AUTO_REST_AUTH ------------------------------ ------------------------------ ------------------------------ HR ENABLED ENABLED
And finally the service definition went well (even if it looks stupid the COMMIT is strongly suggested), I have also decided to format a bit the date display:
SQL> show user USER is "HR" SQL> exec ords.define_service(p_module_name => 'examples', p_base_path => '/examples/', p_method => 'GET', p_pattern => '/greeting/', - p_source => 'select to_char(sysdate,''dd-mon-yyyy hh24:mi:ss'') as current_date from dual'); PL/SQL procedure successfully completed. SQL> commit; Commit complete.
Then a simple GET request on the url should provide current date. To be honest I have not expected to spend so much time on this !! Initial request with Curl failed::
So decided to activate debugging mode with ($ORACLE_HOME/ords/config/ords/defaults.xml file):
Either you edit the file or use:
[oracle@server1 ords]$ java -jar ords.war set-property debug.debugger true Apr 13, 2018 11:50:21 AM oracle.dbtools.rt.config.setup.SetProperty execute INFO: Modified: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/defaults.xml, setting: debug.debugger = true [oracle@server1 ords]$ java -jar ords.war set-property debug.printDebugToScreen true Apr 13, 2018 11:50:36 AM oracle.dbtools.rt.config.setup.SetProperty execute INFO: Modified: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/defaults.xml, setting: debug.printDebugToScreen = true
I have tried to download a binary on my Windows desktop and it has worked but I have remembered a Web training video where the presenter has introduced Postman. But I have not been able to make it working… So finally downloaded Insomnia and yeepee got the expected result:
If I try with a parameter in the url to get information for only one employee:
SQL> show user USER is "HR" SQL> exec ords.define_service(p_module_name => 'employees', p_base_path => '/employees/', p_method => 'GET', p_pattern => '/:id', - p_source => 'select * from employees where employee_id=:id'); PL/SQL procedure successfully completed. SQL> commit; Commit complete.
I can now specify in the url the employee id I would like to display:
With dictionary views you can double check what has been done (no ALL_xx or DBA_xx views, account owner is ORDS_METADATA):
SQL> set lines 200 SQL> col pattern for a15 SQL> col name for a15 SQL> col uri_prefix for a15 SQL> col uri_template for a15 SQL> col source_type for a20 SQL> select id, parsing_schema, type, pattern, status, auto_rest_auth from user_ords_schemas; ID PARSING_SCHEMA TYPE PATTERN STATUS AUTO_REST_AUTH ---------- ------------------------------ ---------- --------------- ------------------------------ ------------------------------ 10062 HR BASE_PATH hr ENABLED ENABLED SQL> select id, name, uri_prefix, items_per_page, status from user_ords_modules; ID NAME URI_PREFIX ITEMS_PER_PAGE STATUS ---------- --------------- --------------- -------------- ------------------------------ 10120 employees /employees/ 25 PUBLISHED 10067 examples /examples/ 25 PUBLISHED SQL> select id, module_id, uri_template from user_ords_templates; ID MODULE_ID URI_TEMPLATE ---------- ---------- --------------- 10133 10120 /:id 10101 10067 /greeting/ SQL> select id, template_id, source_type, method, source from user_ords_handlers; ID TEMPLATE_ID SOURCE_TYPE METHOD SOURCE ---------- ----------- -------------------- ---------- -------------------------------------------------------------------------------- 10134 10133 json/collection GET select * from employees where employee_id=:id 10102 10101 json/collection GET select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') as current_date from dual
Multiple Templates
The following code creates a web service with two templates, one of which uses a parameter to return a single record. Notice the parameter is used in the associated query to limit the results returned.
The web service is available using the following URLs.
Create DELETE Web Services (Remove/DELETE)
Like the POST and PUT methods, the source of the DELETE handler can be a as simple as a PL/SQL block containing a delete statement, but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to delete rows.
The following code creates a web service with a DELETE handler that calls the stored procedure, passing the parameter from the payload.
The web service can be called using the following URL, method, header and payload.
Run DDL
We can string together one or more DDL statements, each ending with a ";". In the script example we did this using a file, but we can include the commands inline, provided we escape any necessary characters.
Run DML
We can string together one or more DML statements, each ending with a ";". In the script example we did this using a file, but we can include the commands inline, provided we escape any necessary characters.
Manual Build
Rather than using the DEFINE_SERVICE procedure, we can build the same web service manually using the DEFINE_MODULE , DEFINE_TEMPLATE and DEFINE_HANDLER procedures. The following code creates a similar web service to that defined previously, but this time defining all the pieces manually.
The web service is available using the following URL.
First Party Authentication (Basic Authentication)
All the examples so far have used schema authentication, but we can use basic authentication to access the services provided the user has the SQL Developer role. Create these with care because these users can access all schemas that are REST enabled.
Create a user with the SQL Developer role.
We can now make a call using basic authentication rather than the schema credentials. Remember we specify the schema alias in the URL, which maps to the schema, so the REST Enabled SQL functionality knows which schema to run the query under.
This article gives an overview of using SQL Developer to interact with Oracle REST Data Services (ORDS).
Enable ORDS
Enable REST web services for the test schema itself. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name. In this example we've used a schema alias of "hr".
Web services from the schema can now be referenced using the following base URLs.
We are now ready to start.
Quick Build
The DEFINE_SERVICE procedure allows you to create a new module, template and handler in a single step. If the module already exists, it's replaced by the new definition.
The basic makeup of an ORDS RESTful web service is as follows.
We can see this pattern in the URL to access this web service. Remember, the default method for a browser URL is GET, so the final URL listed will work in a browser.
Stored Procedure (JSON)
The previous examples use handlers associated with queries, but they can be associated with stored procedures. I would suggest this is the best approach to REST enabling the database, as you can hide relational complexity behind a PL/SQL API. To show this, create the following procedure, which uses the APEX_JSON package to generate the JSON results.
The following code creates a web service which calls this procedure. Notice the source type of SOURCE_TYPE_PLSQL and a PL/SQL block as the source.
The web service is available using the following URLs.
Assumptions and Comments
This article assumes the following.
- You already have a functioning installation of ORDS.
- The paths for the ORDS configuration match those from the ORDS installation article listed above.
- You have an Oracle database available. In this article I will be using a 12c database, but it works just the same with an 11g database.
- You have a way to call the web services. Some GET web services can be called from a browser, but the other methods require some coding, or a REST client. I used "curl" and the "Advanced REST client" extension for Chrome.
- You already have an understanding of the various pieces of ORDS functionality. This article will not teach you about this, but the linked articles will.
Third Party OAuth 2.0-Based Authentication
SQL> exec oauth.grant_client_role(p_client_name => 'My Employees Application', p_role_name => 'employees_role'); PL/SQL procedure successfully completed. SQL> commit; Commit complete.
With my Insomnia tool the access is then a bit different as I am not required to get the access token first. Insomnia is able to do it in one call. I have chosen OAuth 2 in authentication method and I specify token url, client id and client secret the url I aim to fetch is still the same (the access token has been auto-filled by Insomnia):
In Timeline tab of response we can see that Insomnia as fetch the access token (Authorization: Bearer line) and used it to fetch my final url to get employee id 100 information:
With ORDS dictionary views it gives:
SQL> col name for a30 SQL> col client_name for a25 SQL> col response_type for a15 SQL> select name, description, response_type, client_id, client_secret from user_ords_clients; NAME DESCRIPTION RESPONSE_TYPE CLIENT_ID CLIENT_SECRET ------------------------------ ---------------------------------------- --------------- -------------------------------- -------------------------------- My Employees Application A Vue.JS client to access Employees data TOKEN 6YoYpRwsaeH19coruhZAsw.. yM49CRbG8WkvAb5AUx07lA.. SQL> select * from user_ords_client_roles; CLIENT_ID CLIENT_NAME ROLE_ID ROLE_NAME ---------- ------------------------- ---------- --------------- 10163 My Employees Application 10135 employees_role SQL> select name, label, description, client_name FROM user_ords_client_privileges; NAME LABEL DESCRIPTION CLIENT_NAME ------------------------------ -------------------- ---------------------------------------- ------------------------- employees_priv Employees data Securing access to employees data My Employees Application
Привет! Я работаю в компании Bercut, которая входит в группу компаний Ростелеком и более 20 лет занимается разработкой и поддержкой ПО для операторов сотовой и фиксированной связи. Прошел путь от инженера в отделе сопровождения до менеджера продукта. В последние годы работаю ведущим специалистом в отделе администрирования (Senior DBA) и знаю все про работу высоконагруженных биллинговых базах данных, обслуживающих от сотен тысяч до десятков миллионов абонентов. Сегодня я хочу рассказать про решение по комплексному мониторингу биллинговой системы у оператора связи на основе Grafana, Prometheus и Oracle ORDS.
Почему мы выбрали Grafana, Prometheus и Oracle ORDS
Глядя на то, как Grafana отлично справляется с визуализацией метрик с оборудования, инфраструктуры и серверов приложений, я решил, что было бы неплохо сделать дашборд и для мониторинга компекса на уровне БД. И, по моим ощущениям и отзывам коллег, получилось неплохо. Grafana ― это opensource решение, позволяющее легко создать дашборд с отображением метрики из разных источников. В Grafana можно объединить информацию по активности БД, нагрузке на сервере БД с ключевыми показателями работы биллинговой системы на одном экране, при этом интервал окна или временной период меняется всего несколькими кликами. Но не буду забегать вперед, начнем по порядку.
Grafana не умеет напрямую использовать БД Oracle как источник метрик, поэтому требовалась дополнительная прослойка. С учетом уже имеющегося у брата опыта, для сбора биллинговых метрик и хранилища истории был выбран Prometheus. В качестве экспортера из БД Oracle я исследовал возможности OracleDB Exporter. Oracledb_exporter представляет собой небольшой сервис, написанный на Ruby и имеющий конфигурационный файл на простейшем TOML языке. В конфигурационном файле содержатся SQL запросы, которые сервис будет запускать при поступлении запроса со стороны Prometheus. Однако я в нем быстро разочаровался по нескольким причинам:
Необходимость настройки отдельного экземпляра сервиса oracledb_exporter для каждой БД, если их несколько.
Необходимость настройки отдельного экземпляра сервиса oracledb_exporter для сбора метрик с разным интервалом опроса. Некоторые метрики нужно собирать очень часто, и они являются легкими с точки зрения выполнения запроса в БД; другие отрабатывают довольно долго, но нет необходимости в их частом запуске ― порой достаточно и раза в сутки.
Появление новой технологии (TOML) и точки настройки мониторинга для наших заказчиков и инженеров Bercut. Для кастомизации и добавления новых метрик коллегам придется изучать TOML.
И самое важное ― сложность в поддержке и обновлении метрик. C развитием продукта или по результатам каких-то инцидентов мы добавляем новые метрики; заказчики часто добавляют свои. Возникает проблема с применением изменений к конфигурационному TOML-файлу.
В то же время полностью от oracledb_exporter я не отказываюсь и продолжаю его использовать для получения некоторых метрик с экземпляра БД и Oracle ASM ― иметь статистику о топе ожиданий БД и % свободного пространства в ASM дисковых группах на дашборде биллинговой системы довольно важно.
Рис.1 Архитектура решения для мониторинга биллинговой системы
Prometheus, согласно настройкам, каждую минуту опрашивает ORDS сервис, который на каждый запрос запускает в БД PL/SQL процедуру, а она в свою очередь в HTML формате отдает текущие значения заранее рассчитанных метрик. Учитывая то, что часть метрик по большим или высоконагруженным таблицам могут рассчитываться минуты, а некоторые метрики не требует частого обновления, вариант заранее собрать и подготовить к публикации метрики оказался лучшим решением. Также это избавляет от необходимости иметь конфигурацию с несколькими экспортерами с различными интервалами опроса и таймаутами, что упрощает общую конфигурацию.
Полученные значения метрик Prometheus сохраняет в свою БД и хранит данные 2 недели. Также значения метрик сохраняются в БД Victoria Metrics на значительно более длительный срок.
Пользователь, открывая дашборд в Grafana, определяет период и интервал отображения информации. Данные выбираются из указанного хранилища метрик, и рисуются красивые и наглядные графики
Далее я более детально опишу все элементы схемы: сверху вниз, слева направо.
Create POST Web Services (Create/INSERT)
The POST method is typically used for a create operation, like inserting some data. Remember, from a database perspective, a create operation, like creating an employee, may involve multiple operations, not just inserts. The POST method expects the parameter values to be passed as a payload. I used the "Advanced REST client" extension for Chrome to send the requests here.
The source of the POST handler can be a regular PL/SQL block containing an insert statement, but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to insert rows.
The following code creates a web service with a POST handler that calls the stored procedure, passing the parameters from the payload.
The web service can be called using the following URL, method, header and payload.
You can see a more complex example here.
Enable REST Enabled SQL (ORDS Version 22.1 Onward)
So far we have enabled the conventional functionality available in previous versions. To enable the REST Enabled SQL functionality we must set the restEnabledSql.active attribute. This is the on/off switch for this functionality.
By default the functionality is only available over HTTPS, which is very sensible since the payload contains credentials, but we can allow HTTP access for demos by using the following setting. Without this we will see an error message saying, "This resource must only be accessed over HTTPS. It was incorrectly accessed using HTTP".
The following option can be used to stop us from killing the system by pulling out too many rows.
We will need to restart ORDS for this to take effect. The method will vary depending on if you are running ORDS under Tomcat, WebLogic or in standalone mode.
A direct call to this URL from a browser will result in a "405 Method Not Allowed" message.
Enable REST Enabled SQL (ORDS Versions 17.4 to 21.4)
So far we have enabled the conventional functionality available in previous versions. To enable the REST Enabled SQL functionality we must amend the "defaults.xml" file, which in this case is located in the "/u01/ords/conf/ords" directory. This entry is the on/off switch for this functionality.
This can be done manually, or with the following command.
By default the functionality is only available over HTTPS, which is very sensible since the payload contains credentials, but we can allow HTTP access for demos by using the following setting. Without this you will see an error message saying, "This resource must only be accessed over HTTPS. It was incorrectly accessed using HTTP".
The following option can be used to stop us from killing the system by pulling out too many rows.
You will need to restart ORDS for this to take effect. The method will vary depending on if you are running ORDS under Tomcat, WebLogic or in standalone mode.
A direct call to this URL from a browser will result in a "405 Method Not Allowed" message.
Basic Call Explained
In its simplest form a call to the REST Enabled SQL requires the following information.
In the early releases, when using schema authentication the username for the credential had to be in uppercase or the authentication will fail. This is no longer the case in later releases. The password is case sensitive.
So a basic call using curl would look like either of these, depending on which forms of the flags you prefer to use.
I will add the "-s" option in the call to curl and append " | python -mjson.tool" to pretty-print the JSON. This isn't necessary for the call to work.
If you are using HTTPS with a self-signed certificate, remember to use the "-k" option. To show the header information, use the "-i" option.
Почему нам понадобилось новое решение
Для настройки, управления и мониторинга всех продуктов в Bercut используется модуль Bercut ATOMS, с агентами на хостах и графической консолью MIB Explorer для администраторов ПО Bercut. В консоли специалисты операторов и Bercut выполняют все действия по настройке компонентов систем, трассировку, мониторинг производительности и т.д. Консоль имеет и встроенные средства для отображения графиков. С появлением Prometheus и Grafana, ставшими де-факто стандартами в ИТ, и возможности объединения мониторинга всех систем, системного и прикладного ПО, серверов, СХД, SAN, сетевой инфраструктуры в одном приложении мы также стали активно использовать Grafana. Ребята сделали MIB Exporter для экспорта метрик модулей систем в Prometheus. Коллега разработал Solaris Exporter для мониторинга серверов Oracle SPARC. Сейчас Prometheus и Grafana установлены у большинства наших заказчиков, и все перечисленное доступно через несколько десятков дашбордов.
Мониторинг метрик бизнес-логики биллинговой системы в БД Oracle, который я когда-то написал на PL/SQL, исторически был реализован обособленно. Он обладал необходимой функциональностью для формирования алертов и анализа инцидентов, но графического дашборда не имел. Конечно, мы делали попытки использовать Oracle Enterprise Manager, Zabbix и др., но это были скорее кастомные решения. Настройка отдельной метрики через User defined metrics/Metric extensions в OEM довольно трудоемка и посильна больше DBA, нежели администраторам комплекса.
При этом вопрос функционирования и нагрузки на БД Oracle периодически возникал при проблемах и отбоях: "Сережа, что с базой?" Инженеры в большинстве случаев не имеют доступа в Oracle Enterprise Manager и самостоятельно оценить нагрузку, факт конкуренции или блокировок в БД не могут. Т.е. требовался еще и простой "монитор", посмотрев на который коллеги могли бы самостоятельно проверить, не отличается ли текущая активность БД и нагрузка от той, что была час, день или неделю назад.
В общем, требовалось объединить моторинг работы БД и мониторинг бизнес-процессов внутри в БД в единое решение.
PL/SQL
Since this functionality uses the same SQL engine as SQL Developer and SQLcl, it can perform a subset of the commands available from them, provided it doesn't make reference to the host.
First Party Cookie-Based Authentication
Start by creating a role:
SQL> show user USER is "HR" SQL> exec ords.create_role(p_role_name => 'employees_role'); PL/SQL procedure successfully completed. SQL> commit; Commit complete.
Create a privilege associated with the role:
SQL> DECLARE l_arr OWA.vc_arr; BEGIN l_arr(1) := 'employees_role'; ords.define_privilege(p_privilege_name => 'employees_priv', p_roles => l_arr, p_label => 'Employees data', - p_description => 'Securing access to employees data'); commit; END; / PL/SQL procedure successfully completed.
Protect the RESTful API with he newly created privilege with (procedure not documented at the time of writing this post in ORDS 18.1):
SQL> exec ords.create_privilege_mapping(p_privilege_name => 'employees_priv', p_pattern => '/employees/*'); PL/SQL procedure successfully completed. SQL> commit; Commit complete.
With ORDS dictionary views it gives:
SQL> set lines 200 SQL> col name for a15 SQL> col label for a20 SQL> col description for a40 SQL> col pattern for a15 SQL> col privilege_name for a15 SQL> col role_name for a15 SQL> select id, name, schema_id from user_ords_roles where name='employees_role'; ID NAME SCHEMA_ID ---------- --------------- ---------- 10135 employees_role 10062 SQL> select id, label, name, description from user_ords_privileges where name='employees_priv'; ID LABEL NAME DESCRIPTION ---------- -------------------- --------------- ---------------------------------------- 10136 Employees data employees_priv Securing access to employees data SQL> select privilege_id, privilege_name, role_id, role_name from user_ords_privilege_roles where privilege_name='employees_priv'; PRIVILEGE_ID PRIVILEGE_NAME ROLE_ID ROLE_NAME ------------ --------------- ---------- --------------- 10136 employees_priv 10135 employees_role SQL> select privilege_id, name, pattern from user_ords_privilege_mappings where name='employees_priv'; PRIVILEGE_ID NAME PATTERN ------------ --------------- --------------- 10136 employees_priv /employees/*
Create a user to access again the RESTful API with:
[oracle@server1 ords]$ java -jar ords.war user hr_user employees_role Enter a password for user hr_user: Confirm password for user hr_user: Apr 17, 2018 4:40:27 PM oracle.dbtools.standalone.ModifyUser execute INFO: Created user: hr_user in file: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/credentials
And then either you click on the link if you use a browser or with Insommia you can fill in the authentication tab as follow and you can again access the RESTfull API in a secure manner:
Assumptions
This article assumes the following.
- You already have a functioning installation of ORDS 17.4 or higher, using an application server or standalone mode.
- The paths for the ORDS configuration match those from the ORDS installation article listed above.
- You have an Oracle database available. In this article I will be using a 12c database, but it works just the same with an 11g database unless otherwise stated.
- You have a way to call the web services. I used "curl" and the "Advanced REST client" extension for Chrome.
Create GET Web Services (Read/SELECT)
This section describes the creation of REST web services using the GET method, which are typically used to read data. To stay true to REST, you should not perform any changes to data with a GET operation. Think of this as query-only. It breaks down the process to give you some indication of the pieces that make up an ORDS RESTful web service. The sections for the other methods, will not be broken down in the same way, but the information in this section is applicable to them also.
Multiple Parameters
There are a number of ways to pass multiple parameters to a get services. The following code creates a web service to return a result set. Notice the parameters are not specified in the template, but are specified in the handler.
The web service is available using the following URL.
The following code recreates the previous web service to return a result set, but the parameters are specified in the template, so they are mandatory and the position is fixed.
The web service is available using the following URL.
Automatic Enabling of Schema Objects for REST Access (AutoREST)
So far we have seen on how to fetch data from tables but how we modify them ? When looking in official documentation on how to update, delete and insert rows in objects I end up in AutoREST chapter. This is in fact linked to two procedures of ORDS package that remain unused: ENABLE_SCHEMA and ENABLE_OBJECT.
The shortest and self explaining definition of AutoREST is:
AutoREST is a quick and easy way to expose database tables as REST resources.
By default AutoREST is enabled but when using ENABLE_SCHEMA you might want to deactivate AutoREST authentication for easier testing (needless to say you must not do this in production):
SQL> exec ords.enable_schema(p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> select parsing_schema, type, pattern, status, auto_rest_auth from user_ords_schemas; PARSING_SCHEMA TYPE PATTERN STATUS AUTO_REST_AUTH ------------------------------ ---------- --------------- ------------------------------ ------------------------------ HR BASE_PATH hr ENABLED DISABLED
Means we can display meta-data using special url:
Objects on the contrary are not enables by default:
SQL> set pages 1000 SQL> col parsing_object for a20 SQL> col object_alias for a20 SQL> select parsing_object, object_alias, type, status, auto_rest_auth from user_ords_objects; PARSING_OBJECT OBJECT_ALIAS TYPE STATUS AUTO_REST_AUTH -------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ADD_JOB_HISTORY add_job_history PROCEDURE DISABLED ENABLED COUNTRIES countries TABLE DISABLED ENABLED DEPARTMENTS departments TABLE DISABLED ENABLED EMPLOYEES employees TABLE DISABLED ENABLED EMP_DETAILS_VIEW emp_details_view VIEW DISABLED ENABLED JOBS jobs TABLE DISABLED ENABLED JOB_HISTORY job_history TABLE DISABLED ENABLED LOCATIONS locations TABLE DISABLED ENABLED REGIONS regions TABLE DISABLED ENABLED SECURE_DML secure_dml PROCEDURE DISABLED ENABLED 10 rows selected.
Let enable EMPLOYEES table with (I have chosen another alias not to mess up with already existing one we have created above). I also deactivate authentication (needless to say you must not do this in production):
SQL> exec ords.enable_object(p_enabled => TRUE, p_schema => 'HR', p_object => 'EMPLOYEES', - p_object_type => 'TABLE', p_object_alias => 'emp', p_auto_rest_auth => FALSE); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> select parsing_object, object_alias, type, status, auto_rest_auth from user_ords_enabled_objects; PARSING_OBJECT OBJECT_ALIAS TYPE STATUS AUTO_REST_AUTH -------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ EMPLOYEES emp TABLE ENABLED DISABLED
You can get meta-data with:
And here a list of multiple possible queries (not displaying a picture each time):
To insert a row, I get REST current date format with (ISO 8601):
To delete the just inserted row:
PUT would be use to perform an upsert (insert or update). Have a look to ORDS official documentation it contains plenty of different cases…
Configuring Secure Access to RESTful Services
- First Party Cookie-Based Authentication
- Third Party OAuth 2.0-Based Authentication
Multi-Value Parameters
In some situations need the web service to deal with multiple values of the same parameter. One way you can do this is by passing a comma-separated list of values as a single parameter value. The web service will have to be coded to deal with this. The following example creates a service that handles a list of employee numbers.
There are several ways to deal with dynamic IN-lists, described here, but we will use the following pipelined table function to make the list appear as rows.
We incorporate this pipelined table function into a query to handle multiple employee number values.
The web service is available using the following URL.
Визуализация метрик в Grafana
Для качественноого выполнения задачи нужны реальные данные. С одним из наших заказчиков была согласована разработка пилотной версии мониторинга, и уже на реальных данных с небольшого, но боевого комплекса я создал дашборд в Grafana. Источником выступают данные Prometheus.
В верхней левой части скриншота присутствует информация о текущей и исторической утилизации CPU, количество выполняющихся сессий в БД и график событий ожидания активных сессий БД. Зеленый означает выполнение на CPU, синий ― ожидание чтения с диска. В итоге ― полет нормальный. Правее ― таблица текущих открытых алертов. Метрик гораздо больше, чем графиков, и не всегда по каждой метрике требуется график. Метрика попадает на экран, если ее текущее значение превысило установленный порог. Рядом правее ― два счетчика, количество метрик с ошибками расчета и время отставания репликации данных на резервную БД, с которой снимается большинство метрик. Ниже ― метрики самой биллинговой системы.
Рис.7 Дашборд, часть 1 Рис. 8 Дашборд, часть 2
Для удобства фильтрации по выбранной БД и хосту я создал переменные hostname и db_name:
Рис. 9 Переменные для фильтров
Для большинства метрик используется просто публикация метрики, с фильтром по имени хоста:
Рис. 10 График активности сессий в БД
На указанном выше графике отображается статистика ASH по активным сессиям в Oracle с группировкой по классам ожидания ― требуется лицензия Oracle Diagnostic and Tuning Pack.
В ходе разработки дашборда с метриками типа Counter я стокнулся с проблемой. Т.к. метрики собирались асинхронно с опросом источника со стороны Prometheus, т.е. метрика вычислялась джобом и сохранялась в таблицу, а Prometheus с равным интервалом считывал значение метрик через сервис ORDS, то периодически возникала ситуация, когда Prometheus считывал то же значение, что было в предыдущий раз. В какой-то момент дельта изменений счетчика была равна 0, а при следующем опросе получался двойной прирост счетчика. В итоге на графике была гребенка, не соответствующая действительности:
Рис.11 Некорректный график по метрике "Скорость тарификации"
Я сделал доработку и добавил признак realtime для метрик. Метрики с установленным флагом realtime теперь вычисляются не джобом, а в режиме реального времени, в момент запроса со стороны Prometheus. Все стало правдиво и красиво:
Рис.12 Правильный график по метрике "Скорость тарификации"
Еще одна отличная функция, которую позволяет реализовать Prometheus, ― выявление аномалий. На основе статьи и ее перевода я написал формулы и сделал график, которые позволяют отслеживать факт нахождения текущих показателей метрики в границах, полученных на основе наблюдений за предыдущие 3 недели, с учетом отбрасывания нетипичных (праздничных) дней. И хотя визуальное отслеживание графика в масштабе нескольких часов или даже дней и так позволяет понять, находится ли показатель в норме, функция будет очень полезна именно для формирования алертов в автоматическом режиме. Конкретно эта метрика, по числу тарифицируемых звонков, показала себя не очень хорошо. Случаются "ложные" срабатывания в сторону увеличения числа тарифицируемых событий в момент массовых рассылок. Но такова действительность: запуск рассылки от МЧС или маркетологов трудно предсказать и учесть. В перспективе планирую допилить формулу так, чтобы она не реагировала на резкое увеличение значения метрики.
Рис.13 Выявление аномалий в скорости тарификации
Рис.14 Формула текущего значения скорости тарификации Рис.15 Формула верхней границы типичной скорости тарификации Рис.16 Формула нижней границы типичной скорости тарификации
Дополнительно привожу настройки графика Series overrides, т.к. из оригинальной статьи не совсем очевидно следовало, как отображать именно границу допустимых значений. Я взял коэффициент 1,5:
Рис. 17 Настройки графика
Скриншот сформированного алерта:
Рис.18 Пример алерта
Чтобы визуализация отслеживания аномалий заработала, пришлось подождать несколько недель, пока накопилась история наблюдений и формулы стали выдавать результат.
Enable ORDS for a Schema
Right-click on the connection and select the "REST Services > Enable REST Services. " menu option.
Check the "Enable schema" checkbox and alter the schema alias if you don't want to expose the schema name in the URL. In this case we will ignore authorization. Click the "Next >" button.
If you want to see the code that will run to enable ORDS for the schema, click the "SQL" tab.
Click the "Finish" button.
Click the "OK" button.
Сбор и накопление метрик в Prometheus
В конфигурационный файл Prometheus добавлен следующий блок:
Параметр i_debug я добавил для разделения списка всех метрик на 4 группы. Бывает, при наличии синтаксической ошибки, Prometheus не может распарсить вывод сервиса ORDS, и определить конкретную строку с ошибкой трудно. Мне не удалось найти средство, которое позволяет распарсить вывод и указать, на какой конкретно строке Prometheus спотыкается. Если вам известно решение, напишите в комментариях.
Проверяем статус джоба в прометее:
Рис.5 Статус джоба сбора метрик с биллинга в Prometheus
И доступность метрик:
Рис. 6 Просмотр метрики в Prometheus
Вычисление метрик на стороне БД
Вычисление и хранение метрик реализовано в виде набора таблиц и пакета на PL/SQL.
Рис. 2 Схема данных
Основной таблицей схемы является METRIC_DICT, в которой хранятся список метрик, параметры расписания запуска и SQL запрос для сбора метрики. Ряд метрик могут иметь несколько значений, например, метрика для контроля свободного места в табличном пространстве, ГБ" будет иметь несколько значений ― название табличного пространства в качестве метки и размер в качестве значения. Подобная реализация позволит в дальнейшем отображать все значения в Grafana на одном графике как одну метрику.
Справочник метрик выглядит так:
Рис.3 Справочник метрик
Установка Oracle Rest Data Services
Для установки и настройки ORDS нужен JDK. Если его нет, его нужно установить.
Установка выполняется в интерактивном режиме, либо в silent, с использованием parameter file /opt/ords-20.4.3/params/ords_params.properties
Я планирую сделать Ansible playbook для автоматического развертывания, поэтому использовал второй вариант. Файл параметров для установки в минимальном варианте:
В конфигурационном файле указаны имя пользователя и пароль, с которым сервис будет подключаться к БД. Рекомендуется создать выделенного пользователя, с минимальным набором привилегий:
Установка в silent режиме:
Сервис стартует в режиме standalone, для его остановки и дальнейшей настройки используется Ctrl-C.
Настройка соединения с БД. Используется файл параметров /opt/oracle/ords/params/db_params.properties
Создание подключения к БД:
Создание маппинга URL к созданной конфигурации БД:
Для удобства в ОС создан сервис, который будет автоматом подниматься при старте сервера. Создан конфигурационный файл:
Созданному пользователю выдаются права на вызов процедуры и создается синоним
. Описание схемы данных и публикация кода пакета для расчета метрик не предусмотрено в данной статье, т.к. этот код является интелектуальной собственностью компании. Вам потребуется реализовать расчет метрик самостоятельно. .
В результате сервис работает, процедура создана. Открываем в браузере URL в формате:
Рис.4 Пример работы ORDS сервиса
Enable AutoREST for an Object
Right-click on the object and select the "Enable REST Service. " menu option.
Check the "Enable object" checkbox and specify an object alias if you don't want to expose the object name in the URL. In this case we will ignore authorization. Click the "Next >" button.
If you want to see the code that will run to enable AutoREST for the object, click the "SQL" tab.
Click the "Finish" button.
Click the "OK" button.
Выводы
Решение с использованием Oracle REST Data Services, Prometheus, Grafana и с небольшой разработкой на PL/SQL позволило мне быстро и без закупки дополнительных лицензий реализовать мониторинг комплекса биллинговой системы. В едином графическом интерфейсе Grafana я объединил метрики приложения из БД, некоторые статистики работы самой БД Oracle и статистики сервера БД. Теперь в Grafana инженерам Bercut и заказчика доступен мониторинг всего работающего оборудования и ПО с использованием прочих экспортеров для OS Solaris, СХД, SAN и пр. С помощью единой платформы мониторинга время на выявление и локализацию проблемы, по моей оценке, сокращается на 15-60 минут. Используемое ПО позволяет легко добавить в комплекс мониторинга допы в виде отправки уведомлений по различным каналам; быстро адаптировать визуальное отображение данных и комбинировать разные источники метрик в одном дашборде. Grafana + Prometheus + ORDS ― это круто и современно!
This article gives an overview of using Oracle REST Data Services to developing RESTful web services using the PL/SQL API provided by the ORDS package.
Complete Example
Each of the examples above is defined as a separate module, most of which have a single template with a single handler. This is not a true reflection of how many web services will be presented. The example below combines the templates and handlers above into a single web service, allowing records to be inserted, updated, deleted and queried. It uses some of the stored procedures defined in the previous sections.
The web service supports the following URLs, methods, headers and payloads.
The REST Enabled SQL functionality introduced in Oracle REST Data Services (ORDS) 17.4 allows REST calls to send DML, DDL and scripts to any REST enabled schema by exposing the same SQL engine used in SQL Developer and SQLcl.
Run Query
We have already seen this in the basic call section, but here is an example of a single query. Notice we have had to escape any single quotes.
It should be pretty obvious from the script example, you can process multiple statements in a single call, with each statement being displayed as a separate element.
Notice we also have access to objects that are not owned by, but accessible by the user.
Читайте также: