当所包含的SQL脚本执行失败时，使人偶构建失败 [英] Get puppet build to fail when the contained SQL script fails execution

查看：114
本文介绍了当所包含的SQL脚本执行失败时，使人偶构建失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我试图运行一个无所事事的版本，该版本将Oracle XE安装在Ubuntu Virtualbox VM中，然后运行SQL脚本来初始化Oracle模式.

无业游民的版本在这里: https://github.com/ajorpheus/vagrant -ubuntu-oracle-xe setup.sql 作为oracle模块

运行这个无所事事的版本时出现错误，只是被告知"oracle-script"位在没有更多详细信息的情况下是不成功的.为了弄清楚"oracle-script"位是否存在问题，我将其提取到此，并在此尝试了建议. /p>

  • RTFM之后...我看到Puppet的 exec 必须是幂等的.但是，问题仍然存在，如果表空间已经存在，为什么木偶构建不会失败?

    • 谢谢！

    更新

    按照下面的TheQ的建议添加日志输出后，我看到以下输出:

    debug: Executing 'sqlplus system/manager@xe < /tmp/setup.sql'
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns: SQL*Plus: Release 11.2.0.2.0 Production on Sat Apr 5 16:02:37 2014
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns: Copyright (c) 1982, 2011, Oracle.  All rights reserved.
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns: Connected to:
notice: /Stage[main]//Exec[oracle-script]/returns: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns: create tablespace some_INDEX_50M datafile '~\u01\some_INDEX_50M.dbf' size 50m
notice: /Stage[main]//Exec[oracle-script]/returns: *
notice: /Stage[main]//Exec[oracle-script]/returns: ERROR at line 1:
notice: /Stage[main]//Exec[oracle-script]/returns: ORA-01543: tablespace 'SOME_INDEX_50M' already exists
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns: Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
notice: /Stage[main]//Exec[oracle-script]/returns: executed successfully

    解决方案

    我相信p会根据被调用程序的返回代码来检测脚本是否成功.默认情况下，无论在会话期间运行了什么，sqlplus在关闭时都将返回0.

    [oracle@bbdb2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 17 08:47:08 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select asdjkhasd from sadbjaksd;
select asdjkhasd from sadbjaksd
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bbdb2 ~]$ echo $?
0

    如果希望sqlplus以错误状态退出，则可以使用everything命令，例如

    [oracle@bbdb2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 17 08:48:17 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> whenever sqlerror exit failure;
SQL> select bogus from nowhere;
select bogus from nowhere
                  *
ERROR at line 1:
ORA-00942: table or view does not exist


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bbdb2 ~]$ echo $?
1

    请注意在后一种情况下的其他返回码.这足以让puppet知道命令失败.

    I am attempting to run a vagrant build which installs Oracle XE in an Ubuntu Virtualbox VM and then runs an SQL script to initialize the Oracle Schema.

    The vagrant build is here : https://github.com/ajorpheus/vagrant-ubuntu-oracle-xe The setup.sql is run as a part of the oracle module's init.pp (look near the end of that file or search for 'oracle-script').

    I was getting an error while running this vagrant build and was just being told that the 'oracle-script' bit was not successful without any more details. To figure out whether it is a problem with the 'oracle-script' bit, I extracted it out into a test-sql.pp :

    file {
    '/tmp/setup.sql':
      ensure => file,
      source => '/tmp/setup.sql';
  }

  exec { 'oracle-script':
    path => ['/bin', '/u01/app/oracle/product/11.2.0/xe/bin'],
    command => 'sqlplus system/manager@xe < /tmp/setup.sql',
    require => [ File['/tmp/setup.sql']],
    timeout => '0',
  }

    Now I have another issue (which is what this question is about). The setup.sql creates a table space and on subsequent runs, the tablespace creation should fail and so should the puppet script.

    I am running the following command 

    puppet apply --verbose --debug test-sql.pp

    And the output always indicates a successful outcome:

    debug: Exec[oracle-script](provider=posix): Executing 'sqlplus system/manager@xe < /tmp/setup.sql'
debug: Executing 'sqlplus system/manager@xe < /tmp/setup.sql'
notice: /Stage[main]//Exec[oracle-script]/returns: executed successfully

    However, if I run the same SQL script manually:

    Questions

    1. Why doesn't the Puppet build fail even though the contained SQL script does? Should I be looking at the exit codes of SQL Plus?

    2. How can I get puppet to emit more verbose debug information? I found this, and have tried the suggestions there.

    3. After RTFM ... I see that Puppet's exec is required to be idempotent. However, the question remains, if the tablespace already exists, why doesn't the puppet build fail?

    Thanks!

    Update

    After adding logoutput, as per TheQ's suggestion below, I see the following output:

    debug: Executing 'sqlplus system/manager@xe < /tmp/setup.sql'
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns: SQL*Plus: Release 11.2.0.2.0 Production on Sat Apr 5 16:02:37 2014
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns: Copyright (c) 1982, 2011, Oracle.  All rights reserved.
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns: Connected to:
notice: /Stage[main]//Exec[oracle-script]/returns: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns: create tablespace some_INDEX_50M datafile '~\u01\some_INDEX_50M.dbf' size 50m
notice: /Stage[main]//Exec[oracle-script]/returns: *
notice: /Stage[main]//Exec[oracle-script]/returns: ERROR at line 1:
notice: /Stage[main]//Exec[oracle-script]/returns: ORA-01543: tablespace 'SOME_INDEX_50M' already exists
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns:
notice: /Stage[main]//Exec[oracle-script]/returns: Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
notice: /Stage[main]//Exec[oracle-script]/returns: executed successfully

    解决方案

    I believe puppet detects the success of the script based on the return code of the called program. By default, sqlplus returns 0 when you close it, regardless of what may have been ran during the session.

    [oracle@bbdb2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 17 08:47:08 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select asdjkhasd from sadbjaksd;
select asdjkhasd from sadbjaksd
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bbdb2 ~]$ echo $?
0

    If you want sqlplus to exit with an error status, you can use the whenever command, e.g.

    [oracle@bbdb2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 17 08:48:17 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> whenever sqlerror exit failure;
SQL> select bogus from nowhere;
select bogus from nowhere
                  *
ERROR at line 1:
ORA-00942: table or view does not exist


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bbdb2 ~]$ echo $?
1

    Notice the different return code in the latter case. This should be enough to let puppet know the command failed.

    这篇关于当所包含的SQL脚本执行失败时，使人偶构建失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

    查看全文
    相关文章
    数据库最新文章
    热门教程
    热门工具
    登录 关闭
    扫码关注1秒登录
    发送“验证码”获取 | 15天全站免登陆