Feed aggregator

AWR and ASH guide lines.

Tom Kyte - Mon, 2018-07-23 15:06
Dear Team, Please share some bokks details for AWR and ASH report analysis in depth. Thanks Pradeep
Categories: DBA Blogs

SQL Query optimization : is scalar subqueries killing the performance?

Tom Kyte - Mon, 2018-07-23 15:06
I need help on query optimization. The below-mentioned query is just a sample. Actual query has 50+ scalar subqueries. Base table table_xyz has over 5 million records. <code>SELECT id, seq, ( SELECT functio...
Categories: DBA Blogs

Random date between two date ranges

Tom Kyte - Mon, 2018-07-23 15:06
I need to pick one random date per month from July 2017 to June 2018. I have the below mentioned code which is only selecting them for entire 2017 Calendar year whereas I need it from July 2017 to June 2018. Any advise? with tst as ( Select lev...
Categories: DBA Blogs

table delete,truncate

Tom Kyte - Mon, 2018-07-23 15:06
Hi Team, In Our production environment When are checking the report is not working, after to analysis we found that there was master table has been truncated or deleted by somebody. Now we are trying to identify the sql_text against that table....
Categories: DBA Blogs

SOCAR Accelerates International Expansion with NetSuite

Oracle Press Releases - Mon, 2018-07-23 15:00
Press Release
SOCAR Accelerates International Expansion with NetSuite Car sharing startup scales its business with successful launch in Malaysia

KUALA LUMPUR—Jul 23, 2018

SOCAR, a South Korean car sharing startup, has implemented Oracle NetSuite to support its rapid growth and international expansion. With NetSuite, SOCAR has been able to successfully launch its service in Kuala Lumpur, Malaysia, and scale its business to support rapidly growing demand that has seen 72,000 new members added in just five months at the end of January this year. SOCAR has also seen over 24,000 usage hours recorded on its platform per month from Malaysia to date.

First launched in South Korea in 2011, SOCAR is an on-demand, keyless car sharing service that offers customers the convenience of a car without the commitment of ownership. With the SOCAR mobile app, customers can book a car, from anywhere and at any time, and then use it to meet their individual transport needs. After signing up more than 3.4 million members in South Korea and offering over 8,200 cars in 3,200 zones throughout the country, SOCAR decided to launch its service in Kuala Lumpur in January 2018. By partnering with the local government to strategically place cars at major public transport hubs, SOCAR has grown rapidly. To manage this growth and the complex requirements of its business model, SOCAR selected NetSuite.

“The car sharing market is rapidly growing and by reducing car ownership, it can help us live in cities with cleaner air, less traffic and more accessible parks, gyms and schools,” said Leon Foong, CEO, SOCAR. “Our business has grown rapidly in South Korea and we are seeing huge demand in Malaysia. We plan to have more than 1,000 cars available in Malaysia by the end of this year, which is a big increase from the 240 cars we started off with, and NetSuite is playing a huge role in supporting that growth.”

With NetSuite, SOCAR has been able to drive efficiencies, improve decision-making and successfully scale its business. By replacing a manual, Excel-driven process for reconciling and tracking financial transactions, NetSuite has enabled SOCAR to achieve a complete view into critical business information and efficiently manage its high volume of expenses, revenue and sales transaction data. As a result, the SOCAR accounting and finance teams now have accurate cash flow data and access to the general ledger at any time and from anywhere, which is crucial for any business to grow.

“The car sharing market will experience exponential growth in the future, with 35 million users expected to sign up for 25 million hours of driving time each month by 2021,” said Ronen Naishtein, general manager, Asia, HK and TW, Oracle NetSuite. “SOCAR is perfectly positioned to ride that growth and with NetSuite, the SOCAR team has a flexible and integrated approach to support its changing business requirements and expand into overseas markets quicker.”

Contact Info
Suzanne Myerson
Oracle NetSuite
+61 414 101 583
suzanne.myerson@oracle.com
Mizu Chitra
Text100 Singapore
+65 6603 9000
SGNetSuite@text100.com.sg
About SOCAR

SOCAR Malaysia is a car-sharing startup that aims to change the landscape of Malaysian transportation by bringing in a new era of multi-flex mobility. Prior to launching in Malaysia, SOCAR Korea has brought car-sharing to millions of South Koreans and grown its fleet to almost 10,000 cars.

SOCAR Malaysia is the company’s first overseas expansion and aims to give people the convenience of driving a car without the commitment of owning one. With its mobile application, SOCAR will make car-sharing convenient for everyone by allowing members to book and unlock cars on its platform with just a few taps. All cars on the SOCAR platform are keyless and prices are inclusive of petrol and insurance (comprehensive and personal accident coverage). SOCAR Malaysia is the fastest growing B2C car-sharing platform globally having grown its fleet from 0 to 450 cars in just 6 months. Over the next 6 months, the company aims to grow their fleet to over 1,000 cars and bring the convenience and joy of car-sharing to hundreds of thousands of members across Malaysia. Learn more at www.socar.my.

About Oracle NetSuite

For more than 20 years, Oracle NetSuite has helped organizations grow, scale and adapt to change. NetSuite provides a suite of cloud-based applications, which includes financials / Enterprise Resource Planning (ERP), HR, professional services automation and omnichannel commerce, used by more than 40,000 organizations and subsidiaries in 199 countries and territories. For more information, please visit http://www.netsuite.com.sg.

Follow NetSuite’s Cloud blog, Facebook page and @NetSuite Twitter handle for real-time updates.

About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Suzanne Myerson

  • +61 414 101 583

Mizu Chitra

  • +65 6603 9000

Syncing Active Directory users and groups to PostgreSQL

Yann Neuhaus - Mon, 2018-07-23 10:55

A lot of companies use Active Directory to manage their users and groups. What most of this companies also want to do is to manage their database users and groups in Active Directory. PostgreSQL comes with ldap/kerberos authentication by default but does not provide anything that helps with managing users and groups in an external directory. And even for the authentication the user already needs to be existent in PostgreSQL. One tool you might want to have a look at and that helps with this requirement is pg-ldap-sync.

As usual I am using CentOS 7 for the scope of this post. For getting pg-ldap-sync onto the system PostgreSQL needs to be installed as pg_config is expected to be there. Once you have that several packages need to be installed (the openldap-clients is not required but it is handy to have it just in case you want to test some ldapsearch commands against Active Directory):

[root@pgadsync ~]$ yum install -y ruby rubygem-rake rubygems ruby-devel openldap-clients git

pg-ldap-sync can either be installed directly with ruby commands or you can install it from Git:

[postgres@pgadsync ~]$ git clone https://github.com/larskanis/pg-ldap-sync.git
[postgres@pgadsync ~]$ cd pg-ldap-sync
[postgres@pgadsync pg-ldap-sync]$ gem install bundler
[postgres@pgadsync pg-ldap-sync]$ bundle install
[postgres@pgadsync pg-ldap-sync]$ bundle exec rake install
[postgres@pgadsync pg-ldap-sync]$ which pg_ldap_sync 
~/bin/pg_ldap_sync
[postgres@pgadsync pg-ldap-sync]$ cd ..
[postgres@pgadsync ~]$ bin/pg_ldap_sync --help
Usage: bin/pg_ldap_sync [options]
    -v, --[no-]verbose               Increase verbose level
    -c, --config FILE                Config file [/etc/pg_ldap_sync.yaml]
    -t, --[no-]test                  Don't do any change in the database

And then, of course, you need something in the Active Directory for synchronization. In my test Active Directory I create a new “Organizational Unit” called “PostgreSQL”:

Selection_001

Inside this “Organizational Unit” there is a user which is used for authenticating against Active Directory:
Selection_002

Then we have two other “Organizational Units”, one for the PostgreSQL DBAs and one for the groups we’d like to sync:
Selection_003

There are three people in the pgadmins unit:
Selection_004

There is one group in the groups unit:
Selection_005

… and the group has two members:
Selection_006

This is what we want to synchronize to PostgreSQL. The final requirement is that two roles need to be there is PostgreSQL (you’ll notice later why that is important):

postgres@pgbox:/home/postgres/ [PG10] psql -X postgres
psql (10.3)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# create role ldap_users;
CREATE ROLE
postgres=# create role ldap_groups;
CREATE ROLE
postgres=# \du
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 ldap_groups | Cannot login                                               | {}
 ldap_users  | Cannot login                                               | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# 

With pg-ldap-sync each instance you want to have synchronized needs a separate yaml file like this one:

# With this sample config the distinction between LDAP-synchronized
# groups/users from is done by the membership to ldap_user and
# ldap_group. These two roles has to be defined manally before
# pg_ldap_sync can run.

# Connection parameters to LDAP server
# see also: http://net-ldap.rubyforge.org/Net/LDAP.html#method-c-new
ldap_connection:
  host: 172.22.30.1
  port: 389
  auth:
    method: :simple
    username: CN=pgadsync,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
    password: xxxxx
#  encryption:
#    method: :simple_tls

# Search parameters for LDAP users which should be synchronized
ldap_users:
  base: OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
  # LDAP filter (according to RFC 2254)
  # defines to users in LDAP to be synchronized
#  filter: (&(objectClass=person)(objectClass=organizationalPerson)(givenName=*)(sn=*)(sAMAccountName=*))
  filter: (sAMAccountName=*)
  # this attribute is used as PG role name
  name_attribute: sAMAccountName
  # lowercase name for use as PG role name
  lowercase_name: true

# Search parameters for LDAP groups which should be synchronized
ldap_groups:
  base: OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
  filter: (cn=dbas)
  # this attribute is used as PG role name
  name_attribute: cn
  # lowercase name for use as PG role name
  lowercase_name: false
  # this attribute must reference to all member DN's of the given group
  member_attribute: member

# Connection parameters to PostgreSQL server
# see also: http://rubydoc.info/gems/pg/PG/Connection#initialize-instance_method
pg_connection:
  host: 192.168.22.99
  dbname: postgres
  user: postgres
  password: postgres

pg_users:
  # Filter for identifying LDAP generated users in the database.
  # It's the WHERE-condition to "SELECT rolname, oid FROM pg_roles"
  filter: oid IN (SELECT pam.member FROM pg_auth_members pam JOIN pg_roles pr ON pr.oid=pam.roleid WHERE pr.rolname='ldap_users')
  # Options for CREATE RULE statements
  create_options: LOGIN IN ROLE ldap_users

pg_groups:
  # Filter for identifying LDAP generated groups in the database.
  # It's the WHERE-condition to "SELECT rolname, oid FROM pg_roles"
  filter: oid IN (SELECT pam.member FROM pg_auth_members pam JOIN pg_roles pr ON pr.oid=pam.roleid WHERE pr.rolname='ldap_groups')
  # Options for CREATE RULE statements
  create_options: NOLOGIN IN ROLE ldap_groups
#grant_options:

When you have a look at the “pg_users” and “pg_groups” you will notice why the two PostgreSQL roles created above are required. They are used to distinguish the users and groups coming from the directory and those created locally.

Ready to sync:

[postgres@pgadsync ~]$ bin/pg_ldap_sync -c etc/pg_ldap_sync.yaml -vv 
I, [2018-07-23T14:23:46.350588 #29270]  INFO -- : found user-dn: CN=dba1,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:23:46.360073 #29270]  INFO -- : found user-dn: CN=dba2,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:23:46.363133 #29270]  INFO -- : found user-dn: CN=dba3,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:23:46.474105 #29270]  INFO -- : found group-dn: CN=dbas,OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:23:46.517468 #29270]  INFO -- : user stat: create: 3 drop: 0 keep: 0
I, [2018-07-23T14:23:46.517798 #29270]  INFO -- : group stat: create: 1 drop: 0 keep: 0
I, [2018-07-23T14:23:46.518047 #29270]  INFO -- : membership stat: grant: 2 revoke: 0 keep: 0
I, [2018-07-23T14:23:46.518201 #29270]  INFO -- : SQL: CREATE ROLE "dba1" LOGIN IN ROLE ldap_users
I, [2018-07-23T14:23:46.522229 #29270]  INFO -- : SQL: CREATE ROLE "dba2" LOGIN IN ROLE ldap_users
I, [2018-07-23T14:23:46.525156 #29270]  INFO -- : SQL: CREATE ROLE "dba3" LOGIN IN ROLE ldap_users
I, [2018-07-23T14:23:46.528058 #29270]  INFO -- : SQL: CREATE ROLE "dbas" NOLOGIN IN ROLE ldap_groups
I, [2018-07-23T14:23:46.531065 #29270]  INFO -- : SQL: GRANT "dbas" TO "dba3","dba1" 

… and that’s it. Users and groups are now available in PostgreSQL:

postgres=# \du
                                        List of roles
  Role name  |                         Attributes                         |     Member of     
-------------+------------------------------------------------------------+-------------------
 dba1        |                                                            | {ldap_users,dbas}
 dba2        |                                                            | {ldap_users}
 dba3        |                                                            | {ldap_users,dbas}
 dbas        | Cannot login                                               | {ldap_groups}
 ldap_groups | Cannot login                                               | {}
 ldap_users  | Cannot login                                               | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

When you add anther user to the directory:

Selection_007

… and run the sync again all remaining users will of course not be touched but the new one gets created (notice that I copied the dba4 in the directory, this is why the user is member of the dbas group):

[postgres@pgadsync ~]$ bin/pg_ldap_sync -c etc/pg_ldap_sync.yaml -vv 
I, [2018-07-23T14:27:26.314729 #29273]  INFO -- : found user-dn: CN=dba1,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:27:26.323719 #29273]  INFO -- : found user-dn: CN=dba2,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:27:26.326764 #29273]  INFO -- : found user-dn: CN=dba3,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:27:26.328800 #29273]  INFO -- : found user-dn: CN=dba4,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:27:26.394066 #29273]  INFO -- : found group-dn: CN=dbas,OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:27:26.434236 #29273]  INFO -- : found pg-user: "dba1"
I, [2018-07-23T14:27:26.434443 #29273]  INFO -- : found pg-user: "dba2"
I, [2018-07-23T14:27:26.434531 #29273]  INFO -- : found pg-user: "dba3"
I, [2018-07-23T14:27:26.439065 #29273]  INFO -- : found pg-group: "dbas" with members: ["dba3", "dba1"]
I, [2018-07-23T14:27:26.439357 #29273]  INFO -- : user stat: create: 1 drop: 0 keep: 3
I, [2018-07-23T14:27:26.439468 #29273]  INFO -- : group stat: create: 0 drop: 0 keep: 1
I, [2018-07-23T14:27:26.439656 #29273]  INFO -- : membership stat: grant: 1 revoke: 0 keep: 2
I, [2018-07-23T14:27:26.439759 #29273]  INFO -- : SQL: CREATE ROLE "dba4" LOGIN IN ROLE ldap_users
I, [2018-07-23T14:27:26.441692 #29273]  INFO -- : SQL: GRANT "dbas" TO "dba4" 

To more tips: When you want the complete ldap path for a user can do it like this:
Selection_008

It is advisable to test the filters you have in the yaml like:

[postgres@pgadsync ~]$ ldapsearch -x -h 172.22.30.1 -D "pgadsync@test.dbiservices.com" -W "(sAMAccountName=*)" -b "OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com"  | grep sAMAccountName
Enter LDAP Password: 
# filter: (sAMAccountName=*)
sAMAccountName: dba1
sAMAccountName: dba2
sAMAccountName: dba3
sAMAccountName: dba4

You might wonder how you can assign the permissions then. Just pre-create the role and give the permissions you want:

postgres=# drop role dbas;
DROP ROLE
postgres=# create role dbas in role ldap_groups;
CREATE ROLE
postgres=# grant CONNECT ON DATABASE postgres to dbas;
GRANT

The assignments to that group will come from the directory once you run the next synchronization.

Hope that helps …

 

Cet article Syncing Active Directory users and groups to PostgreSQL est apparu en premier sur Blog dbi services.

Oracle Analytics Cloud Workshop FAQ

Rittman Mead Consulting - Mon, 2018-07-23 07:59

A few weeks ago, I had the opportunity to present the Rittman Mead Oracle Analytics Cloud workshop in Oracle's head office in London. The aim of the workshop was to educate potential OAC customers and give them the tools and knowledge to decide whether or not OAC was the right solution for them. We had a great cross section of multiple industries (although telecoms were over represented!) and OBIEE familiarity. Together we came up with a series of questions that needed to be answered to help in the decision making process. In the coming workshops we will add more FAQ style posts to the blog to help flesh out the features of the product.

If you are interested in coming along to one of the workshops to get some hands on time with OAC, send an email to training@rittmanmead.com and we can give you the details.

Do Oracle provide a feature comparison list between OBIEE on premise and OAC?

Oracle do not provide a feature comparison between on-premise and OAC. However, Rittman Mead have done an initial comparison between OAC and traditional on-premise OBIEE 12c installations:

High Level
  • Enterprise Analytics is identical to 12c Analytics
  • Only two Actions available in OAC: Navigate to BI content, Navigate to Web page
  • BI Publisher is identical in 12c and OAC
  • Data Visualiser has additional features and a slightly different UI in OAC compared to 12c
BI Developer Client Tool for OAC
  • Looks exactly the same as the OBIEE client
  • Available only for Windows, straightforward installation
  • OAC IP address and BI Server port must be provided to create an ODBC data source
  • Allows to open and edit online the OAC model
  • Allows offline development. Snapshots interface used to upload it to OAC (it will completely replace existing model)
Data Modeler
  • Alternative tool to create and manage metadata models
  • Very easy to use, but limited compared to the BI Developer Client.
Catalog
  • It's possible to archive/unarchive catalog folders from on-premise to OAC.
BAR file
  • It's possible to create OAC bar files
  • It's possible to migrate OAC bar files to OBIEE 12c
Can you ever be charged by network usage, for example connection to an on premise data source using RDC?

Oracle will not charge you for network usage as things stand. Your charges come from the following:

  • Which version of OAC you have (Standard, Data Lake or Enterprise)
  • Whether you are using Pay-as-you-go or Monthly Commitments
  • The amount of disk space you have specified during provisioning
  • The combination of OCPU and RAM currently in use (size).
  • The up-time of your environment.

So for example an environment that has 1 OCPU with 7.5 GB RAM will cost less than an environment with 24 OCPUs with 180 GB RAM if they are up for the same amount of time, everything else being equal. This being said, there is an additional charge to the analytics license as a cloud database is required to configure and launch an analytics instance which should be taken into consideration when choosing Oracle Analytics Cloud.

Do you need to restart the OAC environment when you change the RAM and OCPU settings?

Configuring the number of OCPUs and associated RAM is done from the Analytics Service Console. This can be done during up time without a service restart, however the analytics service will be unavailable:

alt

PaaS Service Manager Command Line Interface (PSM Cli), which Francesco covered here, will allow this to be scripted and scheduled. An interesting use case for this would be to allow an increase in resources during month end processing where your concurrent users are at its highest, whilst in the quieter parts of the month you can scale back down.

This is done using the 'scale' command, this command takes a json file as a parameter which contains information about what the environment should look like. You will notice in the example below that the json file refers to an object called 'shape'; this is the combination of OCPU and RAM that you want the instance to scale to. Some examples of shapes are:

  • oc3 — 1 OCPU with 7.5 GB RAM
  • oc4 — 2 OCPUs with 15 GB RAM
  • oc5 — 4 OCPUs with 30 GB RAM
  • oc6 — 8 OCPUs with 60 GB RAM
  • oc7 — 16 OCPUs with 120 GB RAM
  • oc8 — 24 OCPUs with 180 GB RAM
  • oc9 — 32 OCPUs with 240 GB RAM

For example:

The following example scales the rittmanmead-analytics-prod service to the oc9 shape.

$ psm analytics scale -s rittmanmead-analytics-prod -c ~/oac-obiee/scale-to-monthend.json
where the JSON file contains the following:

{ "components" : { "BI" : "shape" : "oc9", "hosts":["rittmanmead-prod-1"] } } }

Oracle supply documentation for the commands required here: https://docs.oracle.com/en/cloud/paas/java-cloud/pscli/analytics-scale2.html .

How is high availability provisioned in Oracle Analytics Cloud?

Building a high available infrastructure in the cloud needs to take into consideration three main areas:

Server Failure: Oracle Analytics Cloud can be clustered, additional nodes (up to 10) can be added dynamically in the Cloud 'My Services' console should they need to be:

alt

It is also possible to provision a load balancer, as you can see from the screenshot below:

alt

Zone Failure: Sometimes it more than just a single server that causes the failure. Cloud architecture is built in server farms, which themselves can be network issues, power failures and weather anomalies. Oracle Analytics Cloud allows you to create an instance in a region, much like Amazons "availability zone". A sensible precaution would be to create a disaster recover environment in different region to your main prod environment, to help reduce costs this can be provisioned on the Pay-as-you-go license model and therefore only be chargeable when its being used.

Cloud Failure: Although rare, sometimes the cloud platform can fail. For example both your data centres that you have chosen to counter the previous point could be victim to a weather anomaly. Oracle Analytics Cloud allows you to take regular backups of your reports, dashboards and metadata which can be downloaded and stored off-cloud and re implemented in another 12c Environment.

In addition to these points, its advisable to automate and test everything. Oracle supply a very handy set of scripts and API called PaaS Service Manager Command Line Interface (PSM Cli) which can be used to achieve this. For example it can be used to automate backups, set up monitoring and alerting and finally and arguably most importantly it can be used to test your DR and HA infrastructure.

Can you push the user credentials down to the database?

At this point in time there is no way to configure database authentication providers in a similar way to Weblogic providors of the past. However, Oracle IDCS does have a REST API that could be used to simulate this functionality, documentation can be found here: https://docs.oracle.com/en/cloud/paas/identity-cloud/rest-api/OATOAuthClientWebApp.html

You can store user group memberships in a database and for your service’s authentication provider to access this information when authenticating a user's identity. You can use the script configure_bi_sql_group_provider to set up the provider and create the tables that you need (GROUPS and GROUPMEMBERS). After you run the script, you must populate the tables with your group and group member (user) information.

Group memberships that you derive from the SQL provider don't show up in the Users and Roles page in Oracle Analytics Cloud Console as you might expect but the member assignments work correctly.

alt

These tables are in the Oracle Database Cloud Service you configured for Oracle Analytics Cloud and in the schema created for your service. Unlike the on-premises equivalent functionality, you can’t change the location of these tables or the SQL that retrieves the results.
The script to achieve this is stored on the analytics server itself, and can be accessed using SSH (using the user 'opc') and the private keys that you created during the instance provisioning process. They are stored in: /bi/app/public/bin/configure_bi_sql_group_provider

Can you implement SSL certificates in Oracle Analytics Cloud?

The short answer is yes.

When Oracle Analytics Cloud instances are created, similarly to on-premise OBIEE instances, a a self-signed certificate is generated. The self-signed certificate is intended to be temporary and you must replace it with a new private key and a certificate signed by a certification authority. Doc ID 2334800.1 on support.oracle.com has the full details on how to implement this, but the high level steps (take from the document itself) are:

  • Associate a custom domain name against the public ip of your OAC instance
  • Get the custom SSL certificate from a Certificate Authority
  • Specify the DNS registered host name that you want to secure with SSL in servername.conf
  • Install Intermediate certificateRun the script to Register the new private key and server certificate
Can you implement Single Sign On (SSO) in Oracle Analytics Cloud?

Oracle Identity Cloud Service (IDCS) allows administrators to create security providors for OAC, much like the providors in on premise OBIEE weblogic providors. These can be created/edited to include single sign on URLs,Certificates etc, as shown in the screenshot below:

alt

Oracle support Doc ID 2399789.1 covers this in detail between Microsoft Azure AD and OAC, and is well worth the read.

Are RPD files (BAR files) backwards compatible?

This would depend what has changed between the releases. The different version numbers of OAC doesn't necessarily include changes to the OBIEE components themselves (e.g. it could just be an improvement to the 'My Services' UI). However, if there have been changes to the way the XML is formed in reports for example, these wont be compatible with different previous versions of the catalog. This all being said, the environments look like they can be upgraded at any time so you should be able to take a snapshot of your environment and upgrade it to match the newer version and then redeploy/refresh from your snapshot

How do you connect securely to AWS?

There doesn't seem to be any documentation on how exactly Visual Analyzer connects to Amazon Redshift using the 'Create Connection' wizard. However, there is an option to create an SSL ODBC connection to the Redshift database that can then be used to connect using the Visual Analyzer ODBC connection wizard:

alt

Can you still edit instanceconfig and nqsconfig files?

Yes you can, you need to use your ssh keys to sign into the box (using the user 'opc'). They are contained in the following locations:

/bi/domain/fmw/user_projects/domains/bi/config/fmwconfig/biconfig/OBIPS/instanceconfig.xml

/bi/domain/fmw/user_projects/domains/bi/config/fmwconfig/biconfig/OBIS/NQSConfig.INI

Its also worth mentioning that there is a guide here which explains where the responsibility lies should anything break during customisations of the platform.

Who is responsible for what regarding support?

Guide to Customer vs Oracle Management Responsibilities in Oracle Infrastructure and Platform Cloud Services (Doc ID 2309936.1)

Guide to Customer vs Oracle Management Responsibilities in Oracle Infrastructure and Platform Cloud Services

Categories: BI & Warehousing

Oracle Analytics Cloud Workshop FAQ

Rittman Mead Consulting - Mon, 2018-07-23 07:59

A few weeks ago, I had the opportunity to present the Rittman Mead Oracle Analytics Cloud workshop in Oracle's head office in London. The aim of the workshop was to educate potential OAC customers and give them the tools and knowledge to decide whether or not OAC was the right solution for them. We had a great cross section of multiple industries (although telecoms were over represented!) and OBIEE familiarity. Together we came up with a series of questions that needed to be answered to help in the decision making process. In the coming workshops we will add more FAQ style posts to the blog to help flesh out the features of the product.

If you are interested in coming along to one of the workshops to get some hands on time with OAC, send an email to training@rittmanmead.com and we can give you the details.

Do Oracle provide a feature comparison list between OBIEE on premise and OAC?

Oracle do not provide a feature comparison between on-premise and OAC. However, Rittman Mead have done an initial comparison between OAC and traditional on-premise OBIEE 12c installations:

High Level
  • Enterprise Analytics is identical to 12c Analytics
  • Only two Actions available in OAC: Navigate to BI content, Navigate to Web page
  • BI Publisher is identical in 12c and OAC
  • Data Visualiser has additional features and a slightly different UI in OAC compared to 12c
BI Developer Client Tool for OAC
  • Looks exactly the same as the OBIEE client
  • Available only for Windows, straightforward installation
  • OAC IP address and BI Server port must be provided to create an ODBC data source
  • Allows to open and edit online the OAC model
  • Allows offline development. Snapshots interface used to upload it to OAC (it will completely replace existing model)
Data Modeler
  • Alternative tool to create and manage metadata models
  • Very easy to use, but limited compared to the BI Developer Client.
Catalog
  • It's possible to archive/unarchive catalog folders from on-premise to OAC.
BAR file
  • It's possible to create OAC bar files
  • It's possible to migrate OAC bar files to OBIEE 12c
Can you ever be charged by network usage, for example connection to an on premise data source using RDC?

Oracle will not charge you for network usage as things stand. Your charges come from the following:

  • Which version of OAC you have (Standard, Data Lake or Enterprise)
  • Whether you are using Pay-as-you-go or Monthly Commitments
  • The amount of disk space you have specified during provisioning
  • The combination of OCPU and RAM currently in use (size).
  • The up-time of your environment.

So for example an environment that has 1 OCPU with 7.5 GB RAM will cost less than an environment with 24 OCPUs with 180 GB RAM if they are up for the same amount of time, everything else being equal. This being said, there is an additional charge to the analytics license as a cloud database is required to configure and launch an analytics instance which should be taken into consideration when choosing Oracle Analytics Cloud.

Do you need to restart the OAC environment when you change the RAM and OCPU settings?

Configuring the number of OCPUs and associated RAM is done from the Analytics Service Console. This can be done during up time without a service restart, however the analytics service will be unavailable:

alt

PaaS Service Manager Command Line Interface (PSM Cli), which Francesco covered here, will allow this to be scripted and scheduled. An interesting use case for this would be to allow an increase in resources during month end processing where your concurrent users are at its highest, whilst in the quieter parts of the month you can scale back down.

This is done using the 'scale' command, this command takes a json file as a parameter which contains information about what the environment should look like. You will notice in the example below that the json file refers to an object called 'shape'; this is the combination of OCPU and RAM that you want the instance to scale to. Some examples of shapes are:

  • oc3 — 1 OCPU with 7.5 GB RAM
  • oc4 — 2 OCPUs with 15 GB RAM
  • oc5 — 4 OCPUs with 30 GB RAM
  • oc6 — 8 OCPUs with 60 GB RAM
  • oc7 — 16 OCPUs with 120 GB RAM
  • oc8 — 24 OCPUs with 180 GB RAM
  • oc9 — 32 OCPUs with 240 GB RAM

For example:

The following example scales the rittmanmead-analytics-prod service to the oc9 shape.

$ psm analytics scale -s rittmanmead-analytics-prod -c ~/oac-obiee/scale-to-monthend.json where the JSON file contains the following:

{ "components" : { "BI" : "shape" : "oc9", "hosts":["rittmanmead-prod-1"] } } }

Oracle supply documentation for the commands required here: https://docs.oracle.com/en/cloud/paas/java-cloud/pscli/analytics-scale2.html .

How is high availability provisioned in Oracle Analytics Cloud?

Building a high available infrastructure in the cloud needs to take into consideration three main areas:

Server Failure: Oracle Analytics Cloud can be clustered, additional nodes (up to 10) can be added dynamically in the Cloud 'My Services' console should they need to be:

alt

It is also possible to provision a load balancer, as you can see from the screenshot below: 

alt

Zone Failure: Sometimes it more than just a single server that causes the failure. Cloud architecture is built in server farms, which themselves can be network issues, power failures and weather anomalies. Oracle Analytics Cloud allows you to create an instance in a region, much like Amazons "availability zone". A sensible precaution would be to create a disaster recover environment in different region to your main prod environment, to help reduce costs this can be provisioned on the Pay-as-you-go license model and therefore only be chargeable when its being used.

Cloud Failure: Although rare, sometimes the cloud platform can fail. For example both your data centres that you have chosen to counter the previous point could be victim to a weather anomaly. Oracle Analytics Cloud allows you to take regular backups of your reports, dashboards and metadata which can be downloaded and stored off-cloud and re implemented in another 12c Environment. 

In addition to these points, its advisable to automate and test everything. Oracle supply a very handy set of scripts and API called PaaS Service Manager Command Line Interface (PSM Cli) which can be used to achieve this. For example it can be used to automate backups, set up monitoring and alerting and finally and arguably most importantly it can be used to test your DR and HA infrastructure.

Can you push the user credentials down to the database?

At this point in time there is no way to configure database authentication providers in a similar way to Weblogic providors of the past. However, Oracle IDCS does have a REST API that could be used to simulate this functionality, documentation can be found here: https://docs.oracle.com/en/cloud/paas/identity-cloud/rest-api/OATOAuthClientWebApp.html

You can store user group memberships in a database and for your service’s authentication provider to access this information when authenticating a user's identity. You can use the script configure_bi_sql_group_provider to set up the provider and create the tables that you need (GROUPS and GROUPMEMBERS). After you run the script, you must populate the tables with your group and group member (user) information.

Group memberships that you derive from the SQL provider don't show up in the Users and Roles page in Oracle Analytics Cloud Console as you might expect but the member assignments work correctly.

alt

These tables are in the Oracle Database Cloud Service you configured for Oracle Analytics Cloud and in the schema created for your service. Unlike the on-premises equivalent functionality, you can’t change the location of these tables or the SQL that retrieves the results.
The script to achieve this is stored on the analytics server itself, and can be accessed using SSH (using the user 'opc') and the private keys that you created during the instance provisioning process. They are stored in: /bi/app/public/bin/configurebisqlgroupprovider

Can you implement SSL certificates in Oracle Analytics Cloud?

The short answer is yes.

When Oracle Analytics Cloud instances are created, similarly to on-premise OBIEE instances, a a self-signed certificate is generated. The self-signed certificate is intended to be temporary and you must replace it with a new private key and a certificate signed by a certification authority. Doc ID 2334800.1 on support.oracle.com has the full details on how to implement this, but the high level steps (take from the document itself) are:

  • Associate a custom domain name against the public ip of your OAC instance
  • Get the custom SSL certificate from a Certificate Authority
  • Specify the DNS registered host name that you want to secure with SSL in servername.conf
  • Install Intermediate certificateRun the script to Register the new private key and server certificate
Can you implement Single Sign On (SSO) in Oracle Analytics Cloud?

Oracle Identity Cloud Service (IDCS) allows administrators to create security providors for OAC, much like the providors in on premise OBIEE weblogic providors. These can be created/edited to include single sign on URLs,Certificates etc, as shown in the screenshot below:

alt

Oracle support Doc ID 2399789.1 covers this in detail between Microsoft Azure AD and OAC, and is well worth the read.

Are RPD files (BAR files) backwards compatible?

This would depend what has changed between the releases. The different version numbers of OAC doesn't necessarily include changes to the OBIEE components themselves (e.g. it could just be an improvement to the 'My Services' UI). However, if there have been changes to the way the XML is formed in reports for example, these wont be compatible with different previous versions of the catalog. This all being said, the environments look like they can be upgraded at any time so you should be able to take a snapshot of your environment and upgrade it to match the newer version and then redeploy/refresh from your snapshot

How do you connect securely to AWS?

There doesn't seem to be any documentation on how exactly Visual Analyzer connects to Amazon Redshift using the 'Create Connection' wizard. However, there is an option to create an SSL ODBC connection to the Redshift database that can then be used to connect using the Visual Analyzer ODBC connection wizard:

alt

Can you still edit instanceconfig and nqsconfig files?

Yes you can, you need to use your ssh keys to sign into the box (using the user 'opc'). They are contained in the following locations:

/bi/domain/fmw/user_projects/domains/bi/config/fmwconfig/biconfig/OBIPS/instanceconfig.xml

/bi/domain/fmw/user_projects/domains/bi/config/fmwconfig/biconfig/OBIS/NQSConfig.INI

Its also worth mentioning that there is a guide here which explains where the responsibility lies should anything break during customisations of the platform.

Who is responsible for what regarding support?

Guide to Customer vs Oracle Management Responsibilities in Oracle Infrastructure and Platform Cloud Services (Doc ID 2309936.1)

Guide to Customer vs Oracle Management Responsibilities in Oracle Infrastructure and Platform Cloud Services

Categories: BI & Warehousing

Announcement: Webinars for “Oracle Indexing Internals and Best Practices” Now Confirmed !!

Richard Foote - Mon, 2018-07-23 03:28
Exciting News !! I can now confirm the dates for my first webinars of my fully updated and highly acclaimed “Oracle Indexing Internals and Best Practice” seminar. For details of all the extensive content covered in the webinars, please visit my Indexing Seminar page. The webinars will run for 4 hours each day, spanning a full week period […]
Categories: DBA Blogs

Cluster Resource To Check When Patching RAC DBFS OGG

Michael Dinh - Sun, 2018-07-22 09:41

crsctl stat res|grep -i type|sort -u

TYPE=app.appvipx.type
TYPE=local_resource
TYPE=ora.asm.type
TYPE=ora.cluster_vip_net1.type
TYPE=ora.cvu.type
TYPE=ora.database.type
TYPE=ora.diskgroup.type
TYPE=ora.listener.type
TYPE=ora.mgmtdb.type
TYPE=ora.mgmtlsnr.type
TYPE=ora.network.type
TYPE=ora.oc4j.type
TYPE=ora.ons.type
TYPE=ora.scan_listener.type
TYPE=ora.scan_vip.type
TYPE=xag.goldengate.type


crsctl stat res -p -w 'TYPE = ora.database.type' | egrep '^NAME|AUTO_START'

crsctl stat res -t -w '((TARGET != ONLINE) or (STATE != ONLINE)'

crsctl stat res -t -w 'TYPE = xag.goldengate.type' -- OGG Resource
crsctl stat res -t -w 'TYPE = app.appvipx.type'    -- OGG VIP
crsctl stat res -t -w 'TYPE = local_resource'      -- DBFS Mount
crsctl stat res -t -w 'TYPE = ora.database.type'   -- DB resource (including DBFS)

You might ask, why not use crsctl stat res -t?

For this specific environment, there are 190 lines of output and needed to focus on what’s important.

List of Top 10 Software for Prototyping in 2018

Nilesh Jethwa - Sat, 2018-07-21 23:08

Top 10 Prototyping Software   When you are deciding on the best prototyping tool to use, you can consider the following factors:   Fidelity – Find out how powerful the tool is for visual and interaction design. Collaboration – Make … Continue reading ?

Via: MockupTiger Wireframes

Hide certain objects on an APEX page

Dimitri Gielis - Sat, 2018-07-21 17:20
A few days ago I got a question on how to hide the title row from the Interactive Report Pivot view.
So the person didn't want to show the red area:


The solution to this problem is to add the following CSS to your page:


table.a-IRR-table--pivot tr:nth-child(3) {
    display:none;
}

The result is this - the title is gone:


Doing this blog post is not about giving the solution to the above problem. I find it more important to show you the process to come to your answer. It comes down to find the right elements on the page which you can manipulate with CSS or JavaScript. To hide something, you can either use CSS with display: none or a JavaScript function (or JQuery hide()). The first thing you do is a search for the element. You want to use the Developer Tools of your browser for that. Most of the time you can right click on your page and do Inspect Element. The browser will show the HTML that is behind what you see on the page.


In the above screenshot, I see that row is a TR in a Table.
So the next step is to find a way to select that element. Typically you would use the id or class tag and look that up. The TR in our case doesn't have any of those, so I went up a line in the hierarchy until I find a good selector. The table has a class a-IRR-table--pivot which we can use.
Once we have the selector, we want to go to the real element, so we navigate back down. Now you need to know a bit of JavaScript or CSS or search on the internet how to do that. You can add elements after each other and it will drill down in the hierarchy again.
In our case, the TR is the third TR in the table, and there's a function to select that, which I used in CSS (nth-child).

If this is all new to you, learning about JavaScript and CSS selectors is a great start. For example, W3School is a nice site to get started learning more about HTML, CSS, JavaScript, and general web.

Categories: Development

Patching GoldenGate with DBFS

Michael Dinh - Fri, 2018-07-20 18:41

There seems to be no consistency as to what directories should be on DBFS for when GoldenGate is implemented with RAC.

Here I will share my thoughts based on issues encountered.

oracle@test1:/opt/oracle/12.2.0/ggs01$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (test1) 1> create subdirs

Creating subdirectories under current directory /oracle/12.2.0/ggs01

Parameter files                /oracle/12.2.0/ggs01/dirprm: created
Report files                   /oracle/12.2.0/ggs01/dirrpt: created
Checkpoint files               /oracle/12.2.0/ggs01/dirchk: created
Process status files           /oracle/12.2.0/ggs01/dirpcs: created
SQL script files               /oracle/12.2.0/ggs01/dirsql: created
Database definitions files     /oracle/12.2.0/ggs01/dirdef: created
Extract data files             /oracle/12.2.0/ggs01/dirdat: created
Temporary files                /oracle/12.2.0/ggs01/dirtmp: created
Credential store files         /oracle/12.2.0/ggs01/dircrd: created
Masterkey wallet files         /oracle/12.2.0/ggs01/dirwlt: created
Dump files                     /oracle/12.2.0/ggs01/dirdmp: created


GGSCI (test1) 2> 


$ ls -ld dir*
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirchk -> /dbfs_client/ggs01/dirchk
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dircrd -> /dbfs_client/ggs01/dircrd
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirdat -> /dbfs_client/ggs01/dirdat
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirdef -> /dbfs_client/ggs01/dirdef
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirdmp -> /dbfs_client/ggs01/dirdmp
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirout -> /dbfs_client/ggs01/dirout
drwxr-x--- 2 ggsuser oinstall 4096 Mar 20  2017 dirpcs
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirprm -> /dbfs_client/ggs01/dirprm
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirrpt -> /dbfs_client/ggs01/dirrpt
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirsql -> /dbfs_client/ggs01/dirsql

GoldenGate maintains data that it swaps to disk in dirtmp.
With all the issues for DBFS, might be better on local.
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirtmp -> /dbfs_client/ggs01/dirtmp

lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirwlt -> /dbfs_client/ggs01/dirwlt
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 dirwww -> /dbfs_client/ggs01/dirwww
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 20  2017 BR -> /dbfs_client/ggs01/BR

Here are errors when applying GoldenGate Patchset.

The errors were due to the stack being down from after running opatchauto apply -norestart which results in DBFS offline for the instance.

Errors can be avoided if directories are local as they should be.

The following actions have failed:
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirout
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style

Use Oracle RAC database as a baseline.
Are alert logs, trace files, etc… on shared volume if Oracle software is installed locally?

PL/SQL procedure and HTTPS request

Tom Kyte - Fri, 2018-07-20 14:06
Hi Tom, I'm trying to invoke a RESTful service (using HTTPS and JSON objects) from a procedure. The version of Oracle installed is: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2...
Categories: DBA Blogs

group records by interval of 3 seconds

Tom Kyte - Fri, 2018-07-20 14:06
Hi Tom, I am not able to find the exact answer to my question although I think I might need to use LAG and LEAD: I have records like this: Time Amount 11/22/2003 12:22:01 100 11/22/2003 12:22:03 200 11/22/2003 12:22:04 ...
Categories: DBA Blogs

enable trace for specific schema

Tom Kyte - Fri, 2018-07-20 14:06
Hi, We are having slowness issues so, I would like to activate the trace for 1 hour in my database for an specific schema, I have reviewed in several websites and I have these steps: 1- ALTER SESSION SET TRACEFILE_IDENTIFIER = "TEST_TRACE"; 2- A...
Categories: DBA Blogs

add subpartitions to partitioned table using alter table

Tom Kyte - Fri, 2018-07-20 14:06
Hi Tom, I have a list based partitioned table as shown below. create table t( col1 number, col2 date, data_id varchar2(10), archived char(1)) partition by list (data_id) (partition a_data_id values ('A'), partition b_data_id values ('B') )...
Categories: DBA Blogs

"Table access by index rowid" is slower than "table access by user rowid"

Tom Kyte - Fri, 2018-07-20 14:06
Hi Tom, Anonymous procedure having a type for table rowid on one column for a billion plus record table, i open a cursor selecting rowid of the table for rownum less than 200000, then i fetch a cursor bulk collecting from the table by limiting ever...
Categories: DBA Blogs

Select for update skip locked failing

Tom Kyte - Fri, 2018-07-20 14:06
Hi, I have a scenario where I have to get first 10 rows from filtered and ordered result set for update. As we are on multi user environment we are trying to use SELECT FOR UPDATE SKIP LOCKED. Let me put it in a simple example Scenario-1...
Categories: DBA Blogs

Expand your reach with Oracle Cloud Marketplace

Oracle Cloud Marketplace is an online store dedicated to marketing cloud business apps and professional services offered by Oracle Cloud partners. Oracle Cloud Marketplace was launched back in...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator