Hi, I need to run sql scripts ( create an instance , create spfile from pfile, create a pluggable database etc)
Sid creation got successful - this is run as a direct batch script execution
execute 'create_instance_sid' do
command "C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\oradim.exe -NEW -SID orclcdb -STARTMODE MANUAL -PFILE \'C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\database\\initorclcdb.ora\'"
only_if {File.exist?("#{node['oracle']['oracle_home']}")}
end
creation of spfile is not successful, no error is logged and seems it is not executed in the back end and the sqlplus is not invoked.
execute 'create_spfile' do
environment ({"ORACLE_SID" => "orclcdb"})
command "echo CREATE SPFILE='C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\database\\spfileorclcdb.ora' FROM PFILE='C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\database\\initorclcdb.ora'; | C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\sqlplus -s sys/MyPassword_12@orclcdb as sysdba"
not_if {File.exists?('C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\database\\spfileorclcdb.ora')}
timeout 1800
end
this script is giving a script error. Can some one please give a suggestion on how to run sql scripts in chef recipes. There is not good examples on the syntax in anywhere
batch 'Create_SPFile_Batch' do
code <<-EOH
sqlplus sys/MyPassword_12<<-SQLEOH
CREATE SPFILE="C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\database\\spfileorclcdb.ora" FROM PFILE="C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\database\\initorclcdb.ora";
exit;
SQLEOH
EOH
cwd "C:\\app\\Administrator\\product\\12.2.0\\dbhome_1\\bin\\"
end
Running sql scripts directly from a chef recipe should work fine, using the execute resource, assuming you are careful about whitespace and quoting and the normal types of execute cli concerns. You will definitely want a guard on any of those resources for idempotency. Personally I tend to avoid using batch, execute or script type resources wherever I can.
If I needed to do this I would avoid executing the scripts directly and would instead make my chef recipe or resources a wrapper around the behavior from a tool like sqitch: https://github.com/sqitchers/sqitch
Could you please give a working example ? My code given above doesnt work for some reason and I cant figure out why. I have not kept any unwanted spaces or anything.
Also thanks for the details about guard conditions. It was the next thing I was getting stuck at when running the queries idempotent.
The solution I found for this issue was to create a windows batch file ( .bat ) which calls sql scripts in it and I called that .bat file inside my chef recipe.
execute 'run_sql_scripts' do
command "path_to_bat_file/mybatfile.bat"
not_if {some guard condition here}
timeout node['module']['script']['timeout']
end
Within the batch file I call the scripts are below.