New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ActiveRecord::StatementInvalid: OCIError: ORA-12801: error signaled in parallel query server P000 #1130

Closed
yahonda opened this Issue Jan 4, 2017 · 3 comments

Comments

Projects
None yet
2 participants
@yahonda
Copy link
Collaborator

yahonda commented Jan 4, 2017

While preparing Oracle enhanced adapter development environment using docker. It always failed with ORA-12801: error signaled in parallel query server P000 ORA-01006: bind variable does not exist: SELECT view_name FROM all_views where owner = SYS_CONTEXT('userenv', 'session_user') error.

$ bundle exec rake spec
==> Loading config from ENV or use default
==> Running specs with MRI version 2.4.0
==> Effective ActiveRecord version 5.1.0.alpha
rake aborted!
ActiveRecord::StatementInvalid: OCIError: ORA-12801: error signaled in parallel query server P000
ORA-01006: bind variable does not exist: SELECT view_name FROM all_views where owner = SYS_CONTEXT('userenv', 'session_user')
stmt.c:243:in oci8lib_240.so
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:162:in `exec'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:39:in `block in exec_query'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1044:in `log'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:22:in `exec_query'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:82:in `select_rows'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/structure_dump.rb:320:in `drop_sql_for_feature'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/structure_dump.rb:257:in `full_drop'
/home/yahonda/git/oracle-enhanced/Rakefile:22:in `block in <top (required)>'
/home/yahonda/.rbenv/versions/2.4.0/bin/bundle:22:in `load'
/home/yahonda/.rbenv/versions/2.4.0/bin/bundle:22:in `<main>'
OCIError: ORA-12801: error signaled in parallel query server P000
ORA-01006: bind variable does not exist
stmt.c:243:in oci8lib_240.so
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:162:in `exec'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:39:in `block in exec_query'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1044:in `log'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:22:in `exec_query'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:82:in `select_rows'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/structure_dump.rb:320:in `drop_sql_for_feature'
/home/yahonda/git/oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/structure_dump.rb:257:in `full_drop'
/home/yahonda/git/oracle-enhanced/Rakefile:22:in `block in <top (required)>'
/home/yahonda/.rbenv/versions/2.4.0/bin/bundle:22:in `load'
/home/yahonda/.rbenv/versions/2.4.0/bin/bundle:22:in `<main>'
Tasks: TOP => spec => clear
(See full trace by running task with --trace)
Coverage report generated for RSpec to /home/yahonda/git/oracle-enhanced/coverage. 643 / 1847 LOC (34.81%) covered.
$

Steps to reproduce:

  • Install ruby 2.4.0

  • Install and start up docker

$ docker -v
Docker version 1.12.5, build 079fbe3/1.12.5
  • Clone "Oracle Database on Docker"
$ git clone https://github.com/oracle/docker-images.git
  • Download "Oracle Database 12c Release 1 (12.1.0.2.0) for Linux x86-64"
linuxamd64_12102_database_1of2.zip
linuxamd64_12102_database_2of2.zip
  • Build a docker image
$ cd docker-images/OracleDatabase/dockerfiles/
$ cp /path/to/linuxamd64_12102_database_*.zip 12.1.0.2/.
$ ./buildDockerImage.sh -v 12.1.0.2 -e
  • Startup docker image
$ sudo docker run -p 1521:1521 --name yahonda oracle/database:12.1.0.2-ee

You will see this kind of output here. the last one is password
which may vary at each environment.

ORACLE AUTO GENERATED PASSWORD FOR SYS, SYSTEM AND PDBAMIN: CyorXJBgdMA=1

Please wait for this message and keep this terminal window open, if you close it
Oracle instance and docker process will be down.

#########################
DATABASE IS READY TO USE!
#########################
  • Download "Instant Client Downloads for Linux x86-64" and install them
oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
  • Install Oracle instant client
`$ sudo rpm -Uvh oracle-instantclient12.1*.rpm
  • Update .bash_profile and reload it.

Use your own DATABASE_SYS_PASSWORD value shown at "ORACLE AUTO GENERATED PASSWORD FOR SYS, SYSTEM AND PDBAMIN:"

export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=${HOME}/network/admin
export TWO_TASK=ORCLPDB1
export DATABASE_NAME=$TWO_TASK
export NLS_LANG=American_America.AL32UTF8
export DATABASE_SYS_PASSWORD=CyorXJBgdMA=1
  • Connect to ORCLPDB1 database using sqlplus

$ sqlplus sys/CyorXJBgdMA=1@//localhost:1521/ORCLPDB1 as sysdba

  • Create tnsnames.ora file and add tns alias called ORCLPDB1

$ mkdir -p $TNS_ADMIN
$ cd $TNS_ADMIN
$ vi tnsnames.ora

ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME=ORCLPDB1)
    )
  )
  • Create database users for Oracle enhanced adapter unit tests

$ sqlplus sys/CyorXJBgdMA=1@//localhost:1521/ORCLPDB1 as sysdba
SQL>

drop USER hr cascade;
drop USER oracle_enhanced cascade;
drop USER oracle_enhanced_schema cascade;
drop USER arunit cascade;
drop USER arunit2 cascade;
drop USER ruby cascade;
CREATE USER oracle_enhanced IDENTIFIED BY oracle_enhanced;

GRANT unlimited tablespace, create session, create table, create sequence,
create procedure, create trigger, create view, create materialized view,
create database link, create synonym, create type, ctxapp TO oracle_enhanced;

CREATE USER oracle_enhanced_schema IDENTIFIED BY oracle_enhanced_schema;

GRANT unlimited tablespace, create session, create table, create sequence,
create procedure, create trigger, create view, create materialized view,
create database link, create synonym, create type, ctxapp TO oracle_enhanced_schema;

CREATE USER arunit IDENTIFIED BY arunit;

GRANT unlimited tablespace, create session, create table, create sequence,
create procedure, create trigger, create view, create materialized view,
create database link, create synonym, create type, ctxapp TO arunit;

CREATE USER arunit2 IDENTIFIED BY arunit2;

GRANT unlimited tablespace, create session, create table, create sequence,
create procedure, create trigger, create view, create materialized view,
create database link, create synonym, create type, ctxapp TO arunit2;

CREATE USER ruby IDENTIFIED BY oci8;
GRANT connect, resource, create view,create synonym TO ruby;
GRANT EXECUTE ON dbms_lock TO ruby;
GRANT CREATE VIEW TO ruby;
GRANT unlimited tablespace to ruby;

create user hr identified by hr;
grant dba to hr;
  • Clone Oracle enhanced adapter

$ git clone https://github.com/rsim/oracle-enhanced.git

$ cd oracle-enhanced/

$ bundle

$ bundle exec rake spec

Workaround

Replace "all_" views with "user_" ones and remove owner = SYS_CONTEXT('userenv', 'session_user') addresses this error. yahonda@0b410bc

The reason why we need to use "all_" views are to support switching schema option introduced in #742

@yahonda

This comment has been minimized.

Copy link
Collaborator

yahonda commented Jan 4, 2017

These unit tests are running fine with Oracle database installed on local linux and without pluggable database feature. I am not sure which Oracle on Docker and/or using Pluggable database causes this error. To isolate them I (may) be going to install Oracle 12c using pluggable database locally, without Docker.

@yahonda

This comment has been minimized.

Copy link
Collaborator

yahonda commented Jan 5, 2017

It reproduces with Oracle Database 12.1.0.2 pluggable database on Oracle Linux 7, not using docker. Then applied "DB PSU 12.1.0.2.161018 (Oct2016)" , it does not reproduce anymore.

I think this issue is not related with Docker. Something wrong with container database 12.1.0.2
and fixed between 12.1.0.2 and 12.1.0.2.161018.

Since my original plan was migrating Oracle enhanced adapter test environment to docker ones, which is not feasible as of right now. Because PSU cannot be applied. There is a request via oracle/docker-images#199 I'm monitoring its status.

@awaltman

This comment has been minimized.

Copy link

awaltman commented Nov 4, 2017

Thank you for logging this issue. I ran into exactly the same problem when using oracle-enhanced with an Oracle 12.1.0.2 database running in a docker container and it was very helpful to see that it was a known issue.

After rebuilding the Oracle database image, applying the latest PSU using the applypatch scripts from OracleDatabase/samples/applypatch, creating a new database instance container from the image and then running $ORACLE_HOME/OPatch/datapatch against it, the issue was resolved.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment