The different Oracle Database utilities and how to use them with Fujitsu BS2000/OSD are discussed in the following topics:
See Also:
Oracle Database UtilitiesThe BS2000-specific information about Oracle Database utilities that you must use with Oracle Database 12c Release 1 (12.1) for Fujitsu BS2000/OSD are discussed in the following sections:
Every Oracle Database utility and product under BS2000 uses an Oracle Database environment definition file, named ORAENV.
The ORAENV file is divided into the following parts:
An executable part for BS2000 commands
A static part for the definition of the environment variables
When processing the environment-definition file, a file link with the name ORAENV is created to the file itself. Oracle programs use the link name ORAENV to open this file. When reading this file, all lines with BS2000 commands (‘/’ in column one) and comments (‘*’ in column one) will be ignored. Only the variable settings starting in column one will be accepted.
You must generate this file before you use the Oracle Database programs as it contains several Oracle Database environment variables. These Oracle Database environment variables describe the operating environment for the Oracle Database and utilities.
If you do not generate the ORAENV file, then the default values are used for all environment variables. In some cases, there are no default values for environment variables, such as for ORASID. If you start an Oracle Database program or utility without generating the ORAENV file first, then you cannot connect to the Oracle Database.
This section includes the following topics:
To call the ORAENV file (sid.P.ORAENV) for the database DEMO, enter the following CALL-PROCEDURE command on the ORAENV file:
/CALL-PROCEDURE DEMO.P.ORAENV
To specify the environment variables, call the ORAENV file containing the environment variables for the database you want to use.
If required, you can change Oracle Database 12c Release 1 (12.1) working environment by editing the user variables in the ORAENV file.
See Also:
“Oracle Environment Variables” for a list of the variables that you can specify in theORAENV fileNote:
The values that you assign to user variables are specific to your task and the database with which you work. The database administrator can also set other variables that may affect the whole database instance. If you try to set values for the DBA-specific variables in the ORAENV file, then they are ignored.
The ORALOAD library, $ORACINST.ORALOAD.LIB is required for executing Oracle Database 12c Release 1 (12.1) programs. The ORALOAD library must be identified by the link name ORALOAD before calling Oracle Database programs. If the link name is missing, then a BLS (BS2000 loader) error message is displayed. The ORALOAD link name is set when the ORAENV procedure is called.
The ORAMESG library, $ORACINST.ORAMESG.LIB is required for Oracle messages. This library is assigned the link name ORAMESG in the ORAENV procedure.
Before you start Oracle Database programs, you must call the environment definition file.
See Also:
"Calling the Environment Definition File" for more informationUse the SDF command START-EXECUTABLE-PROGRAM or the shorter form START-EXECUTABLE to start a program or a utility. Specify the options and operands as the first data input line when the data prompt (*) is displayed, as shown in the following example:
/START-EXECUTABLE (*LINK(ORALOAD),program_name) CCM0001 enter options: * [option_switch] [arguments]
where:
program_name is the name of the program or the utility that you want to start
option_switch is one or more of the program-dependent optional switches. If this is used, then the switch is preceded by a dash (-).
arguments are one or more operands of the program (or utility), or the user name and password combination, or both.
As soon as the program is loaded, the CCM0001 prompt is displayed and enables you to enter the command line options. As shown in the preceding examples, you can enter the option_switch or arguments for the program. The prompt of the program is displayed. If the program is SQL*Plus, then the prompt is SQL>. You can now enter one of the commands of the program. See the generic documentation for the product for a description of the valid commands.
For example, to start SQL*Plus, enter the following command:
/START-EXECUTABLE (*LINK(ORALOAD),SQLPLUS)
* userid/password
To start a utility in UNIX-Style, the Oracle syntax file $ORACINST.SYSSDF.ORACLE.USER must be activated. This is done by the MOD-SDF (MODIFY-SDF-OPTIONS) command in the ORAENV file. Remove the comment marker '&*' and call the sid.P.ORAENV file again. The following commands to start an Oracle utility are available:
/START-ORACLE-ADRCI or /ADRCI /START-ORACLE-CM-CONTROL or /CMCTL /START-ORACLE-CMMIGR or /CMMIGR /START-ORACLE-EXPORT or /OEXP /START-ORACLE-EXPDP or /EXPDP /START-ORACLE-IMPORT or /OIMP /START-ORACLE-IMPDP or /IMPDP /START-ORACLE-LISTENER-CONTROL or /LSNRCTL /START-ORACLE-ORAPWD or /ORAPWD /START-ORACLE-SQLLOADER or /SQLLDR /START-ORACLE-SQLPLUS or /SQLPLUS /START-ORACLE-TNSPING or /TNSPING /START-ORACLE-RECOVERY-MANAGER or /RMAN
Specify the parameters after the start command. Enclose the parameters within single quotation marks, if they contain a white space or an equal sign (=) . The following examples show how to start an utility in UNIX Style in the BS2000 program environment:
/lsnrctl
/sqlplus /nolog
/oimp 'system/manager file=iea buffer=210000 ignore=y grants=y rows=y full=y commit=y'
Starting from Oracle Database 12c Release 1 (12.1), you can run utilities like SQL*Plus both in the normal BS2000 environment and the POSIX environment.
During Oracle Database installation, the utilities are installed within the POSIX file system in the oracle_home_path/bin directory.
To start Oracle utilities in the POSIX shell, you must set the ORACLE_HOME environment variable and extend the PATH environment variable by the path name of the Oracle directory, oracle_home_path/bin as follows:
$ ORACLE_HOME=/u01/app/oracbase/product/12.1.0/dbhome_1 $ export ORACLE_HOME $ PATH=$ORACLE_HOME/bin:$PATH $ export PATH
Alternatively, you can execute the oracle_home_path/.profile.oracle profile, which is created during Oracle Database installation under POSIX. This profile sets and expands the most important variables like ORACLE_HOME and PATH.
To execute the profile, enter the following command:
$ . /u01/app/oracbase/product/12.1.0/dbhome_1/.profile.oracle
Set the variable ORACLE_SID to start an Oracle utility for a specific Oracle Database instance. Check the related ORAENV file in the BS2000 file system for instance specific environment variables. Set these variables in the POSIX shell before you start the utility.
Utilities running in the POSIX shell provide the opportunity to read instance-specific variables from the ORAENV file in the BS2000 file system. To provide access to the BS2000 ORAENV file, you must create a file by name, oraenvsid in the oracle_home_path/dbs directory. This file contains the qualified BS2000 file name of the BS2000 ORAENV file. It acts as a link to the ORAENV file in the BS2000 file system.
For example, to access the ORAENV file, $ORADATA.ORCL.P.ORAENV, you must create an oraenvORCL file in the oracle_home_path/dbs directory, as follows:
$ ORACLE_HOME=/u01/app/oracbase/product/12.1.0/dbhome_1 $ export ORACLE_HOME $ echo '$ORADATA.ORCL.P.ORAENV' > $ORACLE_HOME/dbs/oraenvORCL $ chmod 664 $ORACLE_HOME/dbs/oraenvORCL
Note:
Utilities running in the POSIX shell handle the variables of the BS2000 ORAENV file as subordinate variables. Environment variables in the POSIX shell take precedence over settings in the BS2000 ORAENV file.
The SID in the file name oraenvsid is case sensitive and must match the SID specified in ORACLE_SID.
You must grant access to the user using the BS2000 ORAENV file, if the POSIX user that runs the Oracle utility in the POSIX shell is different from the BS2000 user ID where the ORAENV file is located.
If an Oracle utility uses the BEQ protocol to connect to a database, then Oracle Net Services gets the job parameters to start a dedicated server in the BS2000 environment from the BGJPAR variable. If you do not specify this variable, then Oracle Net Services uses default values.
Note:
The BGJPAR variable might not be set after the oracle_home_path/.profile.oracle profile is run.
While using the BEQ protocol, Oracle recommends that you define the particular BS2000 job parameters for BS2000 jobs that are started by Oracle Net Services. The BGJPAR variable provides the option to define these parameters. You can define this variable either in the related BS2000 ORAENV file or by explicitly setting it in the POSIX environment to the appropriate value.
For example, to assign a bequeathed server task to a special JOB-CLASS, set the BGJPAR variable in the POSIX environment as follows:
$ ORACLE_SID=orcl $ export ORACLE_SID $ BGJPAR='START=SOON,CPU-LIMIT=NO,JOB-CLASS=JCBORA,LOGGING=*NO' $ export BGJPAR
You can start the utilities in a similar way as on other UNIX systems. For example, to start SQL*Plus, use the following commands:
$ sqlplus /nolog $ SQL> connect / as sysdba
You can connect to an Oracle Database instance using one of the following methods:
Oracle Net Services with the Bequeath adapter.
Oracle Net Services over TCP/IP or IPC.
See Also:
"Oracle Net Services"Check with your database administrator if you can connect to the Oracle database using the listed methods, as the possibilities available are dependent on how the system has been configured. Usually, you specify the way you connect to an Oracle Database instance as part of the logon string appended to the userid/password, and separated from it by an at sign (@), as described in the following sections:
If you do not specify a connection string, then use the DEFAULT_CONNECTION or TWO_TASK environment variables to specify an Oracle Database Net Services connect descriptor.
See Also:
“Oracle Environment Variables” for more information about theORAENV file, and the DEFAULT_CONNECTION and TWO_TASK environment variables.Use Oracle Net Services to access a local or a remote database instance. Use the Oracle Net Services logon string to identify the following for accessing a local or a remote database:
Protocol to be used.
Database that you want to access.
Type of server (whether dedicated or shared) that you want to use.
The Oracle Net Services logon string has the following structure:
/START-EXECUTABLE (*LINK(ORALOAD),SQLPLUS) * userid/password@service_name
where:
service_name specifies a service name stored in the TNSNAMES.ORA file that identifies the TNS connect descriptor for the desired database. If you are not sure of what you should enter, then contact your database administrator.
The following example shows a logon string to connect to a database defined in the TNSNAMES.ORA file as SERVERX:
HR/HR@SERVERX
See Also:
“Oracle Net Services” for information about connecting to an Oracle Database using the Bequeath adapterIn most cases, Oracle Database for BS2000/OSD programs use the functions of the C-BS2000 run-time system to access their input and output files. Oracle Database programs can read and write SAM, ISAM, and PAM files.
This section includes the following topics:
SAM or ISAM files store textual data. Each record is considered as a single text line. For example, the SQL script files used by SQL*Plus and spool output files.
SQL*Loader input data is provided as SAM or ISAM files. These files may also contain non-printable data, such as packed decimal or binary integer values. For ISAM files, the key at the beginning of the record is generally ignored.
Under BS2000/OSD, the Oracle Database utilities add default extensions to file names only when the last component of the specified file name is longer than three characters, or when only one component is specified, as shown in the following table:
| Original File Name | Extended File Name |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
This is similar to the file naming conventions used with Oracle Database on a UNIX system.
In special cases, instead of specifying a file name, you can also specify the link name of a previously issued BS2000 /SET-FILE-LINK command. Use the syntax link=linkname in places where a file name is requested. In this way, you can override default file attributes, preallocate file space, and so on. There are a few exceptions where you cannot use the link=linkname notation.
When using the link=linkname notation, the default file name extensions do not work. As a result, file name defaults derived from such notation are not valid, and you must provide explicit names in such cases.
For example, when working with SQL*Loader, if you specify link=linkname for the SQL*Loader control file, then you must provide explicit names for the BAD, LOG, and DISCARD file names.
Some programs may report a syntax error when the link=linkname notation is used on the command (options) line. In such cases, omit the parameter on the command line and specify it instead, when you are prompted for the missing parameter.
Oracle Database 12c Release 1 (12.1) for BS2000/OSD uses fixed link names for specific files.
The most important link names are as follows:
| Type | Meaning/Usage |
|---|---|
|
ORAENV |
Oracle Database environment definition file. |
|
ORALOAD |
The link name is mandatory and specifies the load library from which the Oracle Database modules are loaded during processing. |
|
ORAMESG |
The link name is mandatory and specifies the message library from which Oracle message modules are loaded during execution. |
Typically, you can set these link names by running the ORAENV procedure.
SQL*Plus is an interactive and a batch query tool that is installed with every Oracle Database installation. It has a command-line user interface. SQL*Plus has its own commands and environment, and provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus, and operating system commands to perform the following:
Format, perform calculations on, store, and print from query results
Examine table and object definitions
Develop and run batch scripts
Perform database administration
This section describes how to use SQL*Plus in the BS2000 environment and in the POSIX environment. It supplements the SQL*Plus User's Guide and Reference. It contains the following topics:
This section describes how to use SQL*Plus in the BS2000 environment. It contains the following topics:
Use the INTERRUPT key [K2] to interrupt the execution of a SQL*Plus statement in SQL*Plus. For example, you can interrupt SQL*Plus if you receive a long report that you do not want to be completely displayed on the screen. When you press the INTERRUPT key [K2], the display of the report is stopped and the SQL*Plus command prompt is displayed.
Note:
If you issue an INTERRUPT key when an input is requested, then you must answer this request before interrupting the process. However, this answer will be ignored.
The SQL*Plus HOST command and the $ command enables you to run a BS2000 command without exiting from SQL*Plus.
Some examples of how you can use the HOST command:
If you enter the HOST command without any BS2000 command, then it takes you to the BS2000 command level:
SQL> HOST /SHOW-USER-STATUS
To return to SQL*Plus, use the BS2000 command RESUME .
If you enter the HOST command with a BS2000 command, then the command executes and the control returns to the SQL*Plus command level:
SQL> HOST SHOW-USER-STATUS SQL>
The following BS2000 commands, if used with the HOST or $ command, do not return to SQL*Plus when they have finished running:
START-EXECUTABLE-PROGRAM
LOAD-EXECUTABLE-PROGRAM
START-PROGRAM
LOAD-PROGRAM
CALL-PROCEDURE
HELP-SDF
LOGOFF
You can use the SQL*Plus EDIT command to start the BS2000 editor:
SQL> EDIT
This command:
Writes the SQL buffer, which contains the current SQL statement, to a file called SQLEDT.BUF in the current BS2000 user ID.
Starts the editor EDT, which reads the SQLEDT.BUF file into the work area.
You can then edit and write to the file using the @write command. Use the @halt command to exit the editor and return to SQL*Plus. SQL*Plus then reads the current contents of SQLEDT.BUF back into its command buffer, from which you can execute the SQL statement by entering a forward slash (/) at the SQL*Plus command prompt.
Note:
If you use the SQL*Plus DEFINE _EDITOR command to define a name for the editor, then SQL*Plus ignores it when running in the BS2000 environment. It always starts the EDT editor.
You can also use the EDIT command to edit a SQL file by specifying the SQL file. For example, if you enter the following command, then the editor EDT is called to edit the LOGIN.SQL file:
SQL> EDIT LOGIN[.SQL]
Note that you can omit the default file name extension .SQL.
When you use the SQL*Plus SPOOL command, SQL*Plus uses the default output-file suffix, .LST.
Note:
The output generated by BS2000/OSD operating system commands are not spooled.
When you issue a SPOOL OUT command, SQL*Plus executes the BS2000 /PRINT command:
/PRINT tempfile,ERASE
where tempfile is a temporary copy of the spool file. This routes the file to the central printer. To specify any /PRINT command options, such as character sets, or routing to a remote printer, add the following line to the ORAENV file:
PRINTPAR=options
where options is any sequence of /PRINT command options. Refer to the BS2000 manual . SQL*Plus then executes a /PRINT command, which includes these options.
See Also:
BS2000 manual User Commands (ISP Format) for more information about these optionsYou can run SQL script with SQL*Plus by using the START command or the @ (at symbol).
If SQL*Plus is executed in the BS2000 environment, it searches the SQL script in the current BS2000 user ID. If the script is not found, then SQL*Plus searches the paths specified by the SQLPATH environment variable. This variable is used to specify one or more file name prefixes separated by a semicolon (;), which should be applied when searching for the SQL script file.
For example, if SQLPATH is set as follows:
SQLPATH=PRIVATE;$GLOBAL;/guest/scripts/;
then, when you enter the following command:
SQL> @filename
SQL*Plus searches for the SQL script file in the following sequence, until a matching file name is found:
filename.SQL in the current BS2000 user ID in the BS2000 DMS.PRIVATE.filename.SQL with the prefix PRIVATE in the current user ID in the BS2000 DMS.$GLOBAL.filename.SQL in the BS2000 user ID $GLOBAL in the BS2000 DMS./guest/scripts/filename.sql in the directory /guest/scripts/ in the POSIX file system.See Also:
"Basics of Oracle Database Utilities" for more information about default file name extensionsIf you execute SQL*Plus within a BS2000 SDF command procedure, add the following command to the procedure before the /START-EXECUTE command for SQL*Plus:
/ASSIGN-SYSDTA *SYSCMD
This forces SQL*Plus to read data from the procedure, instead of prompting at the terminal.
You can run SQL*Plus not only in the normal BS2000 environment, but also in the POSIX environment.
This section describes the following:
Set the required environment variables and run SQL*Plus as described in "Starting Oracle Utilities in the POSIX Program Environment." To connect to a database immediately, specify the user name and password as an argument as follows:
$ sqlplus username/password
If you do not want to connect to a database, then specify /nolog as an argument:
$ sqlplus /nolog
If you start SQL*Plus without any arguments, then you will be prompted for the user name and password.
Interrupting a SQL statement when you run SQL*Plus in the POSIX environment depends on the terminal connected with your POSIX session.
To interrupt a SQL statement in the POSIX environment:
Enter @@c and then press the Enter key if the POSIX shell is started on a blockmode terminal.
Press the Ctrl + C key combination if the POSIX shell is started by a remote X-client through rlogin or ssh using an xterm terminal.
The SQL*Plus HOST command and the $ command enables you to enter a POSIX shell command, without exiting SQL*Plus.
When using the HOST command, remember the following points:
If you enter the HOST command without any shell command, then a POSIX subshell is started and the POSIX command prompt is displayed. To return to SQL*Plus, you must use the exit command or the return command in the POSIX subshell.
If you enter the HOST command with a POSIX shell command, then the command is executed in a subshell and then control returns to SQL*Plus.
Use the bs2cmd POSIX shell command to execute BS2000 commands.
Start a text editor in SQL*Plus with the EDIT command, to edit an SQL statement.
The default editor depends on the terminal connected with your POSIX session:
If the POSIX shell is started on a blockmode terminal, then edtu is set as the default editor in SQL*Plus.
If the POSIX shell is started by a remote X-client through rlogin or ssh using an xterm terminal, then vi is set as the default editor in SQL*Plus.
SQL*Plus provides the opportunity to define a preferred text editor with the DEFINE _EDITOR command. In the POSIX environment you can define a preferred editor. For example, to define the editor used by the EDIT command to be the POSIX editor edtu, enter the following command in SQL*Plus:
DEFINE _EDITOR = edtu
Note:
The editor vi does not work on blockmode terminals.
The editor edtu does not work on xterm terminals.
The command EDIT:
Writes the SQL buffer, which contains the current SQL statement to a file called SQLEDT.BUF in the current working directory in the POSIX file system.
Starts the editor, which reads the file SQLEDT.BUF into the work area.
You can then edit and write this file using @write with edtu editor and using :w with vi editor.
To exit the editor and return to SQL*Plus, use the @halt command with edtu and :q with vi. SQL*Plus then reads the current contents of SQLEDT.BUF back into its command buffer, from which the SQL statement can be run by entering slash “/” at the SQL*Plus command prompt.
You can also use the EDIT command to edit a SQL file by specifying the name of the SQL file. For example, if you enter the following command, then the editor is called to edit the login.sql file:
SQL> EDIT login[.sql]
Note that you can omit the default file name extension, .sql.
When using the SQL*Plus SPOOL command, SQL*Plus uses the default output-file suffix, .lst .
The command SPOOL OUT is not supported, when you use SQL*Plus in the POSIX environment.
You can run a SQL script with SQL*Plus by using the START command or the @ (at symbol).
If SQL*Plus is executed in the POSIX environment, it searches the SQL script in the current working directory. If the script is not found, then SQL*Plus searches the paths specified by the SQLPATH environment variable. This variable specifies one or more file name prefixes separated by a semicolon (;), which should be applied when searching for the SQL script file.
For example, if SQLPATH is set as follows:
SQLPATH=’private;/BS2/$GLOBAL;/guest/scripts/;’
export SQLPATH
then, when you enter the following command:
SQL> @filename
SQL*Plus searches for the SQL script file in the following sequence, until a matching file name is found:
filename.SQL in the current working directory in the POSIX file system.private/filename.SQL in the subdirectoryprivate of the current working directory in the POSIX file system./BS2/$GLOBAL.filename.SQL in the BS2000 user ID $GLOBAL in the BS2000 DMS./guest/scripts/filename.sql in the directory /guest/scripts/ in the POSIX file system.See Also:
"Basics of Oracle Database Utilities" for more information about default file name extensionsYou can set up your SQL*Plus environment to use the same settings with each session. There are two operating system files to do this:
The Site Profile file, glogin.sql for site wide settings.
The User Profile file, login.sql for user specific settings.
When a user starts SQL*Plus, the glogin.sql file is executed first followed by the user's login.sql file. These profiles are discussed in detail in the following topics:
The Site Profile file, glogin.sql is executed when you start SQL*Plus. This file contains SQL statements and SQL*Plus commands that must be run at the beginning of a SQL*Plus session. The glogin.sql file is located in the POSIX file system, $ORACLE_HOME/sqlplus/admin. The database administrator may customize the glogin.sql file if required.
Every time you start SQL*Plus, the user profile script login.sql is executed after the glogin.sql script. Similar to glogin.sql, this file also contains SQL statements and SQL*Plus commands that a user wants to run at the beginning of every SQL*Plus session.
When you start SQL*Plus in the BS2000 environment, SQL*Plus first searches for login.sql in the current BS2000 user ID. If the login.sql file is not found, then SQL*Plus searches along the path that is specified by the SQLPATH environment variable. If there are more than one login.sql file, then SQL*Plus executes the first login.sql file that is found. For a customized SQL*Plus environment, each BS2000 user ID can have its own login.sql file.
When you start SQL*Plus in the POSIX environment, SQL*Plus first searches for login.sql in the current working directory in the POSIX file system. If the login.sql file is not found, then SQL*Plus searches along the path that is specified by the SQLPATH environment variable . If there are more than one login.sql file, then SQL*Plus executes the first login.sql file that is found. For a customized SQL*Plus environment, each POSIX user can have its own login.sql file.
See Also:
"Oracle Environment Variables" for a description of the SQLPATH environment variable
SQL*Plus User's Guide and Reference for more information about login.sql
The following is a sample startup file:
set echo off set feedback 4 set pause on set pause PLEASE ACKNOWLEDGE TO CONTINUE set echo on
The SQL*Plus symbol used for concatenation is the vertical bar, "|" (X'4F'). For users with German keyboards and using a 7–bit terminal character set, any key that transmits a X'4F' (for example, "ö"), can be used.
The sample schemas provide a common platform for examples.
See Also:
SQL*Plus User's Guide and Reference for more information about the sample schemas and SQL*Plus
"Creating a Database" for information about how to install the sample schemas
The limits of several SQL*Plus elements are specified in SQL*Plus User's Guide and Reference. The following table defines BS2000 specific limits:
| Item | Limit |
|---|---|
|
File name length |
54 in the BS2000 DMS (including CATID and BS2000 user ID) 512 in the POSIX system |
|
LINESIZE |
32767 |
|
MAXDATA |
32767 |
|
Maximum number of nested command files |
12 |
SQL*Loader is a tool used for moving data from an external file (or files) into the tables of an Oracle database. SQL*Loader can load data in several formats and can even load several tables simultaneously. You can also use it to load only records that match a particular data value.
This section includes the following topics:
See Also:
Oracle Database Utilities for a detailed description of SQL*Loader and its demonstration filesThe Export utility is used to write data from an Oracle database into the BS2000 system files. Use this utility with the Import utility to back up your data and to move data between Oracle databases.
This section includes the following topics:
You can start the Export utility exp either in the BS2000 environment or the POSIX environment.
See Also:
Export dump files, which are created in the BS2000 DMS by EXP are usually of the SAM file structure. You can override default output file specifications by running a SET-FILE-LINK command such as:
/SET-FILE-LINK LINK-NAME=explink,FILE-NAME=expfile,ACCESS-METHOD=*SAM,RECORD-FORMAT=*FIXED,RECORD-SIZE=2048,BUFFER-LENGTH=*STD(1)
Then, call the EXP utility by specifying the following in response to the output file name prompt:
LINK=explink
On a nonkey public volume set, you may need to adjust the BLKSIZE and RECSIZE values for efficient disk-space usage (note that RECSIZE must be 16 bytes less than the BLKSIZE on nonkey disks). Specify the RECSIZE value to match the export record size.
For example:
/SET-FILE-LINK LINK-NAME=explink,FILE-NAME=expfile,ACCESS-METHOD=*SAM,RECORD-FORMAT=*FIXED,RECORD-SIZE=2032,BUFFER-LENGTH=2048
Note:
Do not use variable record size with SAM files.
When using a block size (PAM) or record size (SAM) other than 2048, you must also specify a corresponding RECORDLENGTH parameter to EXP on the options line.
When exporting large volumes of data, the default disk-space allocation for the output file is inappropriate, and the program spends a significant amount of time allocating secondary extents of disk space. If the maximum number of extents exceeds the number that the catalog entry can hold, then an output-file error occurs.
You must always preallocate the EXP output file with the BS2000 /FILE command, before starting the Export utility. When allocating the file, you must use a realistic estimate for both the primary and secondary space allocations.
For example:
/CREATE-FILE LARGE.EXPORT.DMP,SPACE=(30000,30000) /ADD-FILE-LINK LINK-NAME=EXPOUT,FILE-NAME=LARGE.EXPORT.DMP /START-EXECUTABLE (*LINK(ORALOAD),EXP) * system/manager ... Export file: EXPDAT.DMP >link=expout ...
You can export to foreign systems using the following methods:
To export directly to tape:
Create a catalog entry for a file using the CREATE-FILE command.
Create a link using the ADD-FILE-LINK command. For example:
/CREATE-FILE tapefile,SUPPORT=*TAPE(VOLUME=vsn,DEVICE-TYPE=device) /ADD-FILE-LINK LINK-NAME=tapelink,FILE-NAME=tapefile,ACCESS-METHOD=*SAM,RECORD-FORMAT=*FIXED,RECORD-SIZE=2048,BUFFER-LENGTH=*STD(1)
Set the environment variable EXP_CLIB_FILE_IO to FALSE.
EXP by specifying the following value in response to the output file name prompt:
LINK=tapelink
The export utility writes the output as SAM files, which simplifies export to an Oracle Database on foreign systems.
The Import utility is used to write data from the files created by the Export utility to an Oracle database.
This section includes the following topics:
See Also:
“Known Problems, Restrictions and Workarounds” in Oracle Database Release Notes for Fujitsu BS2000/OSD for restrictions when using the Import utilityThis section gives you few guidelines on importing data from non-BS2000 systems. It includes the following topics:
If the import file on the BS2000/OSD operating system has a block size (BLKSIZE) not equal to 2 KB, then you must specify the block size during import with the Import parameter RECORDLENGTH.
The Import utility can read directly from tape, provided the file can be processed as a SAM file, which is usually the case even for EXP files created on foreign systems (for example, as a sequence of fixed 2 KB blocks).
To read a foreign export file directly:
Create a catalog entry for a file using the IMPORT-FILE command.
Create a link using the ADD-FILE-LINK command. For example:
/IMPORT-FILE SUPPORT=*TAPE(VOLUME=vsn,DEVICE-TYPE=device,FILE-NAME=tapefile) /ADD-FILE-LINK LINK-NAME=tapelink,FILE-NAME=tapefile
Set the environment variable IMP_CLIB_FILE_IO to FALSE.
IMP by specifying the following value in response to the input file name prompt:
LINK=tapelink
If you use FTP, then ensure that you specify binary mode. This is to avoid automatic ASCII-EBCDIC conversion. The received file is stored as a PAM file by the BS2000 FTP utility and can immediately be used as an input file to IMP.
You must use FTP on BS2000 when you want to use an export file from BS2000 as an import file on an ASCII platform. To avoid new line (NL) insertion at block boundaries, ensure that you provide the binary and the ftyp binary parameters.
The Data Pump Export and Import are functionally similar to Export and Import discussed in the preceding sections. However, the I/O processing for dump files is done in the Oracle database server rather than in the client utility session.
This utility is used to write data from an Oracle database into the BS2000 system files. Use this utility with the Data Pump Import utility to back up your data and to move data between Oracle databases.
You can start the Data Pump Export utility, expdp either in the BS2000 environment or the POSIX environment.
See Also:
The Data Pump Export dump files are always created in the BS2000 DMS as PAM files with BLKSIZE=(STD,2).
To use an export file from BS2000 as an import file on an ASCII platform, use FTP as the transfer utility on BS2000 side and indicate the binary parameter .
Note:
If you start EXPDP in UNIX-style and use interactive-command mode [K2] key, then the parameters must be specified when you are prompted for them, and not on the command line.
Data Pump Export to tape is not supported.
The Data Pump Import utility is used to write data from the files created by the Data Pump Export utility to an Oracle database.
You can start the Data Pump Import utility impdp either in the BS2000 environment or the POSIX environment.
See Also:
The following example shows how to use the command-line mode of impdp in the BS2000 environment:
START-EXECUTABLE (*LINK(ORALOAD),IMPDP) * username/password [options]
If you use an export file from an ASCII platform as an import file on BS2000, then use FTP as the transfer utility on BS2000 side and indicate the binary parameter.
Before you get the file, issue the FTP command:
file dmp-file,fcbtype=pam,blksize=(std,2),blkctrl=no
Note:
If you start IMPDP in UNIX-style and use interactive-command mode [K2] key, then the parameters must be specified when you are prompted for them, and not on the command line.
Data Pump Import by tape is not supported.
On BS2000/OSD, Recovery Manager does not support tapes. Disks are the only backup media.
As a workaround, use the Recovery Manager output as a first level storage to be migrated by BS2000 subsystem HSMS (Hierarchical Storage Management System) to tapes. However, it is the administrator's responsibility to ensure the cooperation of the two systems.
The following is an example of a Recovery Manager command in the BS2000 environment:
/START-EXECUTABLE (*LINK(ORALOAD),RMAN) *target "dba1/dba1@i1" catalog "dba2/dba2@i2" cmdfile "b.dat" log "b.log"
To check the data structure integrity of offline databases, use the DBVERIFY command-line utility. You can start the DBVERIFY utility dbv, either in the BS2000 environment or the POSIX environment.
The following examples shows how to use dbv in the BS2000 environment:
/START-EXECUTABLE (*LINK(ORALOAD),DBV) file=orcl.dbs.database1.dbf blocksize=4096 feedback=100
The following example shows how to use dbv in the POSIX environment:
$ dbv file=orcl.dbs.database1.dbf blocksize=4096 feedback=100
See Also:
"Starting Oracle Utilities in the BS2000 Program Environment"
"Starting Oracle Utilities in the POSIX Program Environment"
Oracle Database Utilities for more information about the DBVERIFY program
The Workload Replay Client (WRC) is a multithreaded program where each thread submits a workload from a captured session.
An executable program, wrc is located in the $ORACLE_HOME/bin directory. The specific characteristics of the Workload Replay Client (WRC) on BS2000 is discussed in the following topics:
Related Topics
Before you start the Workload Replay Client (WRC), you must copy the WRC.OPTFILE from the installation user ID to the client user ID. The WRC.OPTFILE must reside in the BS2000 DMS. Use the following command:
/COPY-FILE $ORACINST.WRC.OPTFILE,WRC.OPTFILE,SAME
See Also:
Oracle Database Testing Guide to run the Workload Replay ClientYou must not modify the parameters of the WRC.OPTFILE except when there are problems with the WRC application. The following table shows the parameters defined in the WRC.OPTFILE. Modify the parameter values carefully:
| Parameter | Value | Description |
|---|---|---|
|
|
|
Application Load Library |
|
|
Default: |
Defines the thread scheduling policy: Standard, First In First Out, or Round Robin. |
|
|
Integer 1…2147483647 Default: 100 |
Number of retries to get a lock. |
|
|
Integer 0…59 Default: 1 |
Minimum number of resource tasks. |
|
|
Integer 0…59 Default: 1 |
Maximum number of resource tasks. |
|
|
Integer 1…59 Default: 1 |
Minimum number of thread tasks. |
|
|
Integer 1…59 Default: 1 |
Maximum number of thread tasks. |
|
|
Integer 1…2147483647 Default: 262144 |
Number of bytes reserved for the main stack. |
|
|
Integer 1…2147483647 Default: 262144 |
Default size of a stack for a thread. |
|
|
Integer 1…2147483647 Default: 131072 |
Number of memory pages reserved for the application memory pool. |
To get information about the running WRC, call the procedure ITH-SHOW from the SYSPRC library of the PTHREADS installation:
/CALL-PROCEDURE $TSOS.SYSPRC.PTHREADS.014(ITH-SHOW)
This utility produces an output similar to the following:
/CALL-PROCEDURE $TSOS.SYSPRC.PTHREADS.014(ITH-SHOW)
% BLS0523 ELEMENT 'ITHSHOW', VERSION 'V01.4A10', TYPE 'L' FROM LIBRARY
':BUG2:$
TSOS.SYSLNK.PTHREADS.014' IN PROCESS
% BLS0524 LLM 'ITHSHOW', VERSION 'V01.4A10' OF '2013-11-30 03:17:44' LOADED
% BLS0551 COPYRIGHT (C) 2013 Fujitsu Technology Solutions. ALL RIGHTS RESERVED
STARTED AT 2016-04-20-145816 BY POSIX (running)
LLM = WRC (prelinked)
MAIN = IC@#MAIN
APPLL = :POR5:$ORA12102.ORALOAD.LIB
RUNTL = :BUG2:$TSOS.SYSLNK.PTHREADS.014
PTHvers = 01.4A21 2016-02-10 18:55:31
FDs = 5 (3 ORIG FDs, 2 RESO FDs)
Threads = 5 (1 user threads, 4 system threads)
TYPE TSN PID JOB-TYPE PRI CPU-USED CPU-MAX ACCOUNT#
ORIG 1D4U 2902 (X'0B56') 3 DIALOG *0 240 3.0365 32767 FSC
waiting for new requests
RESO 1D48 2917 (X'0B65') 3 DIALOG *0 240 0.0450 32767 FSC
executing request
THRE 1D49 2918 (X'0B66') 3 DIALOG *0 240 0.2478 32767 FSC
Here you find the TSNs of the tasks involved in the WRC application. You can connect to the WRC application when you choose the TSN of the ORIG task as the input for the parameter TSN of the ITH-START procedure in the following format:
/CALL-PROCEDURE $TSOS.SYSPRC.PTHREADS.014(ITH-START),(TSN=1D4U)
When you see the double slash prompt you can type SHOW-PTHREADS-STATUS to show the status of the running WRC application or CANCEL-THREADED-PROGRAM to cancel the application. If the ORIG task is already terminated, then you can terminate all other tasks of the WRC application. In the BS2000 environment, use the following system command:
/CANCEL_JOB JOB-IDENTIFICATION=tsn
In the POSIX environment use the shell command kill to abort pending processes. You must use the signal SIGABRT (6) or SIGTERM (15) to notify the target process:
$ kill -6 pid
This utility imports and exports text data. .
You can start the Oracle Text Loader utility ctxldr either in the BS2000 environment or the POSIX environment.
See Also:
Oracle Text for installing Oracle Text
"Starting Oracle Utilities in the BS2000 Program Environment"
"Starting Oracle Utilities in the POSIX Program Environment"
The following examples show how to use ctxldr in the BS2000 environment:
/START-EXECUTABLE (*LINK(ORALOAD),CTXLDR) *-USER username/password [options]
The following example shows how to use ctxldr in the POSIX environment:
$ ctxldr -USER username/password [options]
Related Topics