1 说明
在Oracle 12c中,Oracle 建议使用catcon.pl 脚本来执行SQL 脚本和SQL 语句,其可以在root 和指定的PDB中执行。 Catcon.pl脚本是一个perl 脚本,需要在操作系统中执行。
在执行脚本时可以指定-c 参数指定特定的container,如果没有指定,默认对所有container有效。 脚本在$ORACLE_HOME/rdbms/admin目录下。http://www.cndba.cn/cndba/dave/article/225
[oracle@cndba admin]$ pwd /u01/app/oracle/product/12.1.0/db_1/rdbms/admin[oracle@cndba admin]$ ll catcon.pl -rw-r--r--. 1 oracle oinstall 13220 Jun 14 2014 catcon.pl[oracle@cndba admin]$[oracle@cndba admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl Usage: catcon [-u username[/password]] [-U username[/password]] [-d directory] [-l directory] [{-c|-C} container] [-p degree-of-parallelism] [-z EZConnect strings] [-e] [-s] [-E { ON | errorlogging-table-other-than-SPERRORLOG } ] [-I] [-g] [-f] [-r] -b log-file-name-base -- { sqlplus-script [arguments] | --x<SQL-statement> } ... Optional: -u username (optional /password; otherwise prompts for password) used to connect to the database to run user-supplied scripts or SQL statements defaults to "/ as sysdba" -U username (optional /password; otherwise prompts for password) used to connect to the database to perform internal tasks defaults to "/ as sysdba" -d directory containing the file to be run -l directory to use for spool log files -c container(s) in which to run sqlplus scripts, i.e. skip all Containers not named here; for example, -c 'PDB1 PDB2', -C container(s) in which NOT to run sqlplus scripts, i.e. skip all Containers named here; for example, -C 'CDB$ROOT PDB3' NOTE: -c and -C are mutually exclusive -p expected number of concurrent invocations of this script on a given host NOTE: this parameter rarely needs to be specified -z blank-separated EZConnect strings corresponding to RAC instances which can be used to run scripts -e sets echo on while running sqlplus scripts -s output of running every script will be spooled into a file whose name will be <log-file-name-base>_<script_name_without_extension>_[<container_name_if_any>].<default_extension> -E sets errorlogging on; if ON is specified, default error logging table will be used, otherwise, specified error logging table (which must have been created in every Container) will be used -I do not issue set Errorlogging Identifier (ostensibly because the caller already did it and does not want us to override it) -g turns on production of debugging info while running this script -f instructs catcon to ignore PDBs which are closed or, if -c or -C was used, do not exist and process existing PDBs which were specified (explicitly or implicitly) and are open NOTE: if this flag is not specified and some specified PDBs do not exist or are not open, an error will be returned and none of the Containers will be processed. -r causes scripts to be run in all PDBs and then in the Root (reverse of the default order); required for running catdwgrd.sql in a CDB Mandatory: -b base name (e.g. catcon_test) for log and spool file names sqlplus-script - sqlplus script to run OR SQL-statement - a statement to execute NOTES: - if --x<SQL-statement> is the first non-option string, it needs to be preceeded with -- to avoid confusing module parsing options into assuming that '-' is an option which that module is not expecting and about which it will complain - command line parameters to SQL scripts can be introduced using --p interactive (or secret) parameters to SQL scripts can be introduced using --P For example, perl catcon.pl ... x.sql '--pJohn' '--PEnter Password for John:' ...
2 使用示例
2.1 在CDB中所有的Containers中执行catblock.sql脚本
[oracle@cndba admin]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -b catblock_output catblock.sql catcon: ALL catcon-related output will be written to catblock_output_catcon_24939.lst catcon: See catblock_output*.log files for output generated by scripts catcon: See catblock_output_*.lst files for spool files, if any Enter Password: catcon.pl: completed successfully[oracle@cndba admin]$
相关参数说明:
-u:指定每个container中执行命令的用户。
-d:指定执行脚本的目录。
-b:指定输出log文件的名称。
我们这里没有指定-c,所以会在所有的container中执行,如果有PDB 没有open,那么就会报错。
2.2 在指定的PDB中执行catblock.sql脚本
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 IM READ WRITE NO SQL>[oracle@cndba ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -U SYS -d $ORACLE_HOME/rdbms/admin -l '/tmp' -c 'IM' -b catblock_output catblock.sql catcon: ALL catcon-related output will be written to /tmp/catblock_output_catcon_25194.lst catcon: See /tmp/catblock_output*.log files for output generated by scripts catcon: See /tmp/catblock_output_*.lst files for spool files, if any Enter Password: Enter Password: catcon.pl: completed successfully[oracle@cndba ~]$
除了上节讲的参数之外,这里还多了3个参数:
-U: 指定执行内部任务的用户。
-l:指定输出日志文件的目录。
-c:指定在哪些PDB上执行脚本。
2.3 在CDB中除了指定的PDB之外的container上执行catblock.sql脚本http://www.cndba.cn/cndba/dave/article/225
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 IM READ WRITE NO[oracle@cndba ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/tmp' -C 'IM' -b catblock_output catblock.sql catcon: ALL catcon-related output will be written to /tmp/catblock_output_catcon_25305.lst catcon: See /tmp/catblock_output*.log files for output generated by scripts catcon: See /tmp/catblock_output_*.lst files for spool files, if any Enter Password: catcon.pl: completed successfully[oracle@cndba ~]$
这里只有一个新参数:
-C:表示除了这个PDB,在其他的PDB上执行。
2.4 执行带有命令行参数的SQL 脚本
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d /u01/scripts -b custom_script_output custom_script.sql '--phr' '--PEnter password for user hr:'
参数说明如下:
The --p parameter specifies hr for a command line parameter
The --P parameter specifies an interactive parameter that prompts for the password of user hr.
2.5 在CDB的所有Container中执行SQL 语句
[oracle@cndba ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -e -b select_output -- --x'SELECT * FROM DUAL' catcon: ALL catcon-related output will be written to select_output_catcon_25562.lst catcon: See select_output*.log files for output generated by scripts catcon: See select_output_*.lst files for spool files, if any Enter Password: catcon.pl: completed successfully
--查看生成的log:
[oracle@cndba ~]$ ls 20831110 custom_script_output2.log Desktop Public awrrpt_1_93_94_2.html custom_script_output3.log Documents select_output0.log awrrpt_1_93_94.html custom_script_output_catcon_25434.lst Downloads select_output1.log awrrpt_1_95_96.html custom_script_output_catcon_25482.lst Music select_output2.log awrrpt_1_96_97.html custom_script_output_catcon_25507.done p20831110_121020_Linux-x86-64_db.zip select_output3.log cndba.dmp custom_script_output_catcon_25508.done p6880880_121010_Linux-x86-64.zip select_output_catcon_25562.lst custom_script_output0.log custom_script_output_catcon_25509.done PatchSearch.xml Templates custom_script_output1.log custom_script_output_catcon_25510.done Pictures Videos[oracle@cndba ~]$ cat select_output0.log SQL*Plus: Release 12.1.0.2.0 Production on Sat Nov 7 06:00:36 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. SQL> Connected. SQL> 2 Session altered. SQL> 2 Session altered. SQL> SQL> 2 Session altered. SQL> 2 Session altered. SQL> SQL> SQL> 2 Session altered. SQL> NOW_CONNECTED_TO -------------------------------------------------------------------------------- ==== Current Container = CDB$ROOT Id = 1 ==== SQL> NOW_CONNECTED_TO -------------------------------------------------------------------------------- ==== Current Container = CDB$ROOT Id = 1 ==== SQL> 2 Session altered. SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 CATCONSECTION -------------------------- ==== CATCON EXEC ROOT ==== SQL> BEGIN_RUNNING -------------------------------------------------------------------------------- ==== SELECT * FROM DUAL Container:CDB$ROOT Id:1 15-11-07 06:00:37 Proc:0 ==== SQL> BEGIN_RUNNING -------------------------------------------------------------------------------- ==== SELECT * FROM DUAL Container:CDB$ROOT Id:1 15-11-07 06:00:37 Proc:0 ==== SQL> 2 Session altered. SQL> 2 Session altered. SQL> 2 D - X SQL> END_RUNNING -------------------------------------------------------------------------------- ==== SELECT * FROM DUAL Container:CDB$ROOT Id:1 15-11-07 06:00:37 Proc:0 ==== SQL> END_RUNNING -------------------------------------------------------------------------------- ==== SELECT * FROM DUAL Container:CDB$ROOT Id:1 15-11-07 06:00:37 Proc:0 ==== SQL> SQL> SQL> 2 Session altered. SQL> SQL> SQL> 2 Session altered. SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 Session altered. SQL> NOW_CONNECTED_TO -------------------------------------------------------------------------------- ==== Current Container = PDB$SEED Id = 2 ==== SQL> NOW_CONNECTED_TO -------------------------------------------------------------------------------- ==== Current Container = PDB$SEED Id = 2 ==== SQL> SQL> 2 CATCONSECTION ----------------------------------- ==== CATCON EXEC IN CONTAINERS ==== SQL> BEGIN_RUNNING -------------------------------------------------------------------------------- ==== SELECT * FROM DUAL Container:PDB$SEED Id:2 15-11-07 06:00:38 Proc:0 ==== SQL> BEGIN_RUNNING -------------------------------------------------------------------------------- ==== SELECT * FROM DUAL Container:PDB$SEED Id:2 15-11-07 06:00:38 Proc:0 ==== SQL> 2 Session altered. SQL> 2 Session altered. SQL> 2 D - X SQL> END_RUNNING -------------------------------------------------------------------------------- ==== SELECT * FROM DUAL Container:PDB$SEED Id:2 15-11-07 06:00:38 Proc:0 ==== SQL> END_RUNNING -------------------------------------------------------------------------------- ==== SELECT * FROM DUAL Container:PDB$SEED Id:2 15-11-07 06:00:38 Proc:0 ==== SQL> SQL> SQL> 2 Session altered. SQL> SQL> SQL> ========== PROCESS ENDED ========== SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options[oracle@cndba ~]$
http://www.cndba.cn/cndba/dave/article/225
http://www.cndba.cn/cndba/dave/article/225
版权声明:本文为博主原创文章,未经博主允许不得转载。