Catcon.pl使用

在Oracle 12c中，Oracle 建议使用catcon.pl 脚本来执行SQL 脚本和SQL 语句

Catcon.pl脚本是一个perl 脚本，需要在操作系统中执行。

脚本在$ORACLE_HOME/rdbms/admin目录下
In a CDB, the catcon.pl script is the best way to run SQL scripts and SQL statements.

An Oracle Database installation includes several SQL scripts.

These scripts perform operations such as creating data dictionary views and installing options.

The catcon.pl script can run scripts in the root and in specified PDBs in the correct order, and it generates log files that you can view to confirm that the SQL script or SQL statement did not generate unexpected errors. It also starts multiple processes and assigns new scripts to them as they finish running scripts previously assigned to them.

Note:

1. Unless you exclude the CDB seed when you run catcon.pl, the SQL script or SQL statement is run on the CDB seed.
2. You can use the catcon.pl script to run scripts on both CDBs and non-CDBs.

   

1. Syntax and Parameters for catcon.pl

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl  

[--usr username[/password]] [--int_usr username[/password]] [--script_dir directory]

[--log_dir directory] [{--incl_con|--excl_con} container] [--echo] [--spool]

[--error_logging { ON | errorlogging-table-other-than-SPERRORLOG } ] [--app_con application_root]

[--no_set_errlog_ident] [--diag] [-ignore_unavailable_pdbs] [--verbose] [--force_pdb_mode pdb_mode]

[--recover] --log_file_base log_file_name_base

-- { SQL_script [arguments] | --x'SQL_statement' }

--usr Short name: -u

Specifies the username and password to connect to the root and the specified PDBs.

Specify a common user with the required privileges to run the SQL script or the SQL statement.

The default is "/ AS SYSDBA". If no password is supplied, then catcon.pl prompts for a password.

--int_usr Short name: -U

Specifies the username and password to connect to the root and the specified PDBs.

Specify a common user with the required privileges to perform internal tasks, such as running queries on the CDB's metadata.

The default is "/ AS SYSDBA". If no password is supplied, then catcon.pl prompts for a password.

--script_dir Short name: -d

Directory that contains the SQL script. The default is the current directory.

--log_dir Short name: -l

Directory into which catcon.pl writes log files. The default is the current directory.

{--incl_con|--excl_con} Short names: {-c|-C}

The containers in which the SQL script is run or is not run.

The --incl_con parameter lists the containers in which the SQL script is run.

The --excl_con parameter lists the containers in which the SQL script is not run.

Specify containers in a space-delimited list of PDB names enclosed in single quotation marks.

The --incl_con and --excl_con parameters are mutually exclusive.

When this parameter is used, the --app_con parameter cannot be used.

--echo Short name: -e

Sets echo ON while running the script. The default is echo OFF.

--spool  Short name: -s

Spools the output of every script into a file with the following name:log-file-name-base_script-name-without-extension_[container-name-if-any].default-extension

--error_logging Short name: -E

When set to ON, the default error logging table is used. ON is the default setting. When set to ON, errors are written to the table SPERRORLOG in the current schema in each container in which the SQL script runs. If this table does not exist in a container, then it is created automatically.

When a table other than SPERRORLOG is specified, errors are written to the specified table. The table must exist in each container in which the SQL script runs, and the current user must have the necessary privileges to perform DML operations on the table in each of these containers.

See SQL*Plus User's Guide and Reference for more information about the error logging table.

--app_con Short name: -F

Specify an application root. The scripts are run in the application root and in the application PDBs that are plugged into the application root.

When this parameter is used, the --incl_con and --excl_con parameters cannot be used.

--no_set_errlog_ident Short name: -I

Do not issue a SET ERRORLOGGING identifier. This option is intended for cases in which the SET ERRORLOGGING identifier is already set and should not be overwritten.

--diag Short name: -g

Turns on the generation of debugging information.

--ignore_unavailable_pdbs Short name: -f

Ignore PDBs that are closed or, if the --incl_con or --excl_con option is used, do not exist and process only open PDBs that were specified explicitly or implicitly.

When this option is not specified and some specified PDBs do not exist or are not open, an error is returned and none of the containers are processed.

--force_pdb_mode

The required open mode for all PDBs against which the scripts are run. Specify one of the following values:

UNCHANGED

READ WRITE

READ ONLY

UPGRADE

DOWNGRADE

When a value other than UNCHANGED is specified, all of the PDBs against which the script is run are changed to the specified open mode. If a PDB is open in a different mode, then the PDB is closed and re-opened in the specified mode. After all of the scripts are run, each PDB is restored to its original open mode.

When UNCHANGED, the default, is specified, the open mode of the PDBs is not changed.

--recover Short name: -R

Causes catcon.pl to attempt to recover if a SQL*Plus process that it spawned ends unexpectedly. When this parameter is not specified, catcon.pl does not attempt to recover the process and closes.

--log_file_base Short name: -b

(Required) The base name for log file names.

1. Running the catcon.pl Script

1. Run the catcon.pl script and specify one or more SQL scripts or SQL statements:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl parameters SQL_script

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl parameters -- --xSQL_statement

Examples That Run the catcon.pl Script

This example runs the catblock.sql script in all of the containers of a CDB.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl --usr SYS

--script_dir $ORACLE_HOME/rdbms/admin --log_file_base catblock_output catblock.sql

This example runs the catblock.sql script in the hrpdb and salespdb PDBs in a CDB.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl --usr SYS --int_usr SYS

--script_dir $ORACLE_HOME/rdbms/admin --log_dir '/disk1/script_output' --incl_con 'HRPDB SALESPDB'  --log_file_base catblock_output catblock.sql

The --incl_con parameter specifies that the SQL script is run in the hrpdb and salespdb PDBs. The script is not run in any other containers in the CDB.

This example runs the catblock.sql script in all of the containers in a CDB except for the hrpdb and salespdb PDBs.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl --usr SYS

--script_dir $ORACLE_HOME/rdbms/admin --log_dir '/disk1/script_output' --excl_con 'HRPDB SALESPDB'  --log_file_base catblock_output catblock.sql

The --excl_con parameter specifies that the SQL script is run in all of the containers in the CDB except for the hrpdb and salespdb PDBs.

This example runs the custom_script.sql script in all of the containers of a CDB.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl --usr SYS

--script_dir /u01/scripts --log_file_base 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.

This example runs a SQL statement in all of the containers of a CDB.

这里应该是使用--p指定值应答--P提示

Command line parameters to SQL scripts can be introduced using --p. Interactive (or secret) parameters to SQL scripts can be introduced using --P.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl --usr SYS --echo

--log_file_base select_output -- --x"SELECT * FROM DUAL"

The --echo parameter shows output for the SQL statement.

The --log_file_base parameter specifies that the base name for log file names is select_output.

The SQL statement SELECT * FROM DUAL is inside quotation marks and is preceded by --x. Because --x is preceded by a parameter (--log_file_base), it must be preceded by --.

Ensure that --xSQL_statement is preceded by -- if it follows any single-letter parameter. If --xSQL_statement is preceded by a script name or another --xSQL_statement, then do not precede it with --. Also, note that the SQL statement must be inside single quotation marks.