SCA is a command line tool. All functions can be realized through single executable program.
General options may be used in all three types of tasks.
Before using the analysis program, you are advised to read help information carefully. (Command:
Introduction: SCA is the program used to do SQL life-cycle inspection and simulation from Oracle to MogDB. There are 3 types of workers: 1. [I] Compatible inspection about objects and SQLs 2. [S] SQL performance simulator 3. [C] Oracle data collector Options: --[ Overall ]-- -h, --help : Show current help message -v, --version : Show current server version -d, --data : Unzipped data directory for analyzer, or dest location for collection -t, --target-db : Target database for all type of running -T, --type : Running Type: I = Inspection, and automatic building target database, this is default : S = Simulation, like Oracle SPA, use existing target database : IO = Inspection Only, use existing target database : IS = Inspection & Simulation, use existing target database : C = Run as agent, for data collection -w, --workers : Parallel workers in each tasks, default: 10 -x, --debug : Enable debug mode -H, --host : Server host address or name, default: 220.127.116.11 -P, --port : Database server port, default: 55432 -F, --force : Force mode, drop old objects before create it : In repository initialization, means drop old repository if exists -l, --logfile : Write output to both logfile (without progress-bar) and screen (with progress-bar) -L, --log-only : Write output to only logfile (without progress-bar) -r, --report : Final report file location, default in data directory with name 'report' -R, --rewrite : PLSQL rewrite scripts location, default in data directory with name 'rewrite' --[ Repository ]-- -i, --init : Initial repository, server connection information needed -U, --suser : [Init] Super user used to login database server, default: mogdb -E, --spassword : [Init] Password for super user, default: mogdb -n, --database : Repository database, default: sca_db -u, --user : Repository user, default: sca_repo -e, --password : Repository password, default: SCA@password --[ Collection ]-- -C, --catagory : Collection catagory: A = all, M = object metadata, Q = sql data, default is A -q, --sql-days : How many days for session sql data, default: 7 -Q, --sql-interval : SQL collection interval in seconds, default: 600 -U, --suser : Oracle user used for data colletion, default: sys -E, --spassword : Password for Oracle login user, default: oracle -s, --schema-include : Users/Schemas included in data collection, default: '' -S, --schema-exclude : Users/Schemas excluded in data collection : Default: SYS,SYSTEM,OUTLN,QS_ES,QS_WS,QS_OS,QS_CBADM,QS_CB,SH,HR,QS,QS_CS,ORACLE_OCM,TSMSYS,DIP,PM,OE,DBSNMP,APPQOSSYS,WMSYS,EXFSYS,CTXSYS,ANONYMOUS,XDB,XS$NULL,ORDDATA,SI_INFORMTN_SCHEMA,ORDPLUGINS,ORDSYS,MDSYS,OLAPSYS,MDDATA,SPATIAL_WFS_ADMIN_USR,SPATIAL_CSW_ADMIN_USR,SYSMAN,MGMT_VIEW,APEX_030200,APEX_040200,FLOWS_FILES,APEX_PUBLIC_USER,OWBSYS,OWBSYS_AUDIT,SCOTT,UNKNOWN,PUBLIC,ODM,ODM_MTR,WKSYS,WKPROXY,DMSYS,IX,AQ$_ORDERS_QUEUETABLE_V -m, --enable-monitor : Starting monitor process in backgroud, Valid values: 1/on/true/t = ENABLE, 0/off/false/f = DISABLE, default: on Usage: 1. Init repository and analyze data (used for first running) ./sca_linux_x86_64 -i -H <host> -P <port> -U <super-user> -E <super-password> -d <unzipped data directory> 2. Just analyze data ./sca_linux_x86_64 -d <unzipped data directory> 3. Just Init repository (just need ran once for a repository database) ./sca_linux_x86_64 -i -H <host> -P <port> -U <super-user> -E <super-password> 4. Just do SQL performance simulator (repository database is ready) ./sca_linux_x86_64 -T S -d <unzipped data directory> 5. Do Data collection as agent for test (repository database is not needed) # Notice: "-q 0.001 -Q 60" means gather Session SQL only once # "-m off" means do not monitor system status (CPU Idle and Disk Free) ./sca_linux_x86_64 -T C -s SCOTT -t '<target-db>' -H <host> -P <port> -U <oracle-user> -E <oracle-password> -q 0.001 -Q 60 -m off 6. Do Data collection as agent for regular use (repository database is not needed) ./sca_linux_x86_64 -T C -s SCOTT -t '<target-db>' -H <host> -P <port> -U <oracle-user> -E <oracle-password>
View the current version of SCA:
hongyedba@localhost ~ % ./sca_linux_x86_64 -v Server version: 4.0.0
Except data collection (-TC) tasks, tasks of other types need to have -d specified.
Data will be read from the data directory and inserted into database tables, and finally the generated report result will be written into the data directory by default.
Specifies the name of a target database. Generally, when migrating data structures and data using MTK or other data migration tools, you need to create a target database in MogDB.
- Compatibility assessment can be implemented only when the target database has object structures.
- SQL simulation can be implemented only when the target database has object structures and real and consistent full production data. Otherwise, performance simulation result makes no sense.
Specifies the task type.
Currently, SCA supports the following types of tasks:
I [Inspection]: automatically creates the target database structure and executes SQL compatibility assessment.
IO [Inspection Only]: executes only compatibility assessment and needs to work with
-t(specifying a target database).
S [Simulation]: executes the performance simulation task (similar to Oracle SPA) and needs to work with
-t(specifying a target database).
IS [Inspection & Simulation]: executes both compatibility assessment and performance simulation tasks and needs to work with
-t(specifying a target database).
C [Collection]: executes the data collection task on the client. It is used for collecting required object information and session SQL execution performance data from Oracle.
Specifies the degree of parallelism for running tasks. Appropriate degree of parallelism can improve the running speed of each task. The default degree of parallelism is 10.
Applicable to: file data loaded to a data repository, SQL compatibility assessment, SQL complexity assessment, SQL performance simulation, and other operations
- In SQL simulation tasks, a greater degree of parallelism may lead to degradation of the execution efficiency of single SQL. Therefore, an appropriate degree of parallelism needs to be chosen according to the loading of the actual production environment and is generally set to the average number of active sessions of a production database.
Enabling the debug mode does not affect normal analysis logic but will output a large number of logs. The debug mode is usually used for assisting the analysis program in running exceptions itself.
In the collection mode, -H specifies the IP address of the Oracle database server.
In other modes, -H specifies the IP address of the MogDB database server.
In the collection mode, -P specifies the Oracle database port.
In other modes, -P specifies the MogDB database port.
Specifies whether to enable the forcible mode. This mode takes effect in the data repository initialization scenarios.
During the initialization of a data repository, if the forcible mode is enabled, the data repository will be deleted first and then re-created.
Specifies the log file of a program. The program will output data to both the terminal command line and log file.
Specifies the log file of a program. The program will output data only to the log file.
Specifies the report directory.
The report here includes the compatibility assessment and SQL simulation reports in the HTML format, which can be viewed offline.
Specifies the storage procedure script directory after PL/SQL rewrite.
Executes repository initialization. Repository initialization is run only when the first compatibility assessment or SQL simulation is run.
Data collection tasks do not require repositories.
In the collection mode, -U specifies the user name of the Oracle database administrator.
Specifies the super user name during repository initialization. During the follow-up task analysis, -U is required for specifying the user for connecting to MogDB.
In the collection mode, -E specifies the login password of the Oracle database administrator.
Specifies the password of the super user during repository initialization. During the follow-up task analysis, -E is required for specifying the password for connecting to MogDB.
Specifies the name of a repository during repository initialization. If -n is not specified,
sca_db will be used as the name of the repository.
Specifies the user name of a repository during repository initialization. If -u is not specified,
sca_repo will be used as the user name of the repository.
Specifies the password of a repository user during repository initialization. If -e is not specified,
SCA@password will be used as the password of the repository user.
During task analysis, -e is used for specifying the login password of the user specified by -u.
The collection categories include:
A: collects all data, including basic structure information, object information, session SQL information, and SQL performance data of a database.
M: collects basic structure information and object information of a database.
Q: collects session SQL information and SQL performance data.
Specifies the total number of days for SQL-related data collection. Data miss may occur in collecting the executed SQL data from session cache GV$SQLAREA. You can prolong the number of collection days to reduce the data collection miss possibility.
By default, SQL data of a week will be collected.
Specifies the SQL-related data collection interval. By default, the SQL data is collected every 10 minutes.
The SQL data collected every time will be compared with the collected data to filter out the repeated data. This can prevent the data file from being too large.
Specifies the schema whitelist for data collection. Only schema-related data listed in the whitelist is collected.
Specifies the schema blacklist for data collection. Schema-related data listed in the blacklist is not collected.
By default, Oracle system users are listed in the schema blacklist.
Specifies whether to enable the resource monitoring process in the background. This process is enabled by default.
The resource monitoring process queries the CPU usage of the current server every 3s and the remaining space of the file system where the data directory is located.
When the CPU usage is greater than 90% or the remaining space of the file system is lower than 100 MB, the monitoring sub-process will stop collecting data from the primary process so that the server will not become faulty due to resource problems.