-
Notifications
You must be signed in to change notification settings - Fork 19
OCP ‐ The scp for a database
The ocp utility is modelled over the scp utility and it can be used to copy a file between your system running ocp and a directory in a database. As simple example, ocp -l admin/{password}@//host/service myfile.txt DIRECTORY: will copy the local file called myfile.txt to the named directory in the database, and similarly ocp -l admin/{password}@//host/service DIRECTORY:thatfile.zip thisfile.zip will copy the file called thatfile.zip from the named database directory to your local system with the name thisfile.zip. The database can be an Autonomous Database or an on premise database.
In addition to copying to/from your local system from where you execute the ocp command, you can copy files between an Autonomous Database and a cloud bucket.
Just one typical use case for ocp is to download an awr dump file generated by awrdump.
In order to copy a file in either direction between your local file system and a database directory, a helper package must be installed in the database. It is recommended to perform the installation as a DBA as that will make the package available to all users. If you do not have DBA access, it can alternatively be installed as a non-DBA in which case only that user has access to it.
To prepare the database by installing the rwl_ocp package, call ocp with the -l option to connect to the database and the --create-ocp option to install the package. A sample call and output is:
ocp -l admin/{password}@sample_tp --create-ocp
RWP*OCP Release 3.2.1.0 Production on Thu, 24 Apr 2025 09:05:32 UTC
Connected default database to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Package rwl_ocp created
Public synonym rwl_ocp created and granted
This needs to be done only once per database.
To list the database directories that are available to some database user (not necessarily a DBA), call ocp with the --directories option. A sample call and output is:
ocp -l pdbadmin/{password}@//server/host --directories
RWP*OCP Release 3.2.1.0 Production on Thu, 24 Apr 2025 09:13:41 UTC
Connected default database to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
directory path
------------------------------ ----
RWLTEMP /tmp
TMP /tmp
ORACLE_BASE /scratch/bengsig/app
ORACLE_HOME /scratch/bengsig/oh2116h
DATA_PUMP_DIR /scratch/bengsig/app/admin/r2116/dpdump/27FD17E4953A
To copy a local file to the database, provide the source as a file name (possibly with a directory part) and the destination in the format DIRECTORY:file, where the part after the : can be omitted. If omitted the file will be named after the source file. Note that ocp will not overwrite an existing destination file unless the --overwrite option is given. A sample call and output is:
ocp -l pdbadmin/{password}@//server/host /tmp/file.txt TMP:
RWP*OCP Release 3.2.1.0 Production on Thu, 24 Apr 2025 09:23:39 UTC
Connected default database to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Copying '/tmp/file.txt' to 'TMP:file.txt'
The command to copy from the database to the local file system is like the previous one, except it is the source file that has the DIRECTORY:file format. The destination can be a directory, a file possibly including directory parts or it can be empty in which case the current directory is used. As in the opposite direction, the --overwrite option is necessary to overwrite an already existing destination file. A sample call and output is:
ocp -l pdbadmin/{password}@//server/host --overwrite TMP:file.txt /tmp
RWP*OCP Release 3.2.1.0 Production on Thu, 24 Apr 2025 09:30:21 UTC
Connected default database to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Copying 'TMP:file.txt' to '/tmp/file.txt'
If your database is an Autonomous Database, you can list the contents of a database directory using the --list-directory option:
ocp -l admin/{password}@sample_tp --list-directory=DATA_PUMP_DIR
RWP*OCP Release 3.2.0.17 Development on Thu, 24 Apr 2025 09:48:57 UTC
Connected default database to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Contents of database directory 'DATA_PUMP_DIR'
bytes last modified file name
36679680 2022.11.22 14:31:42 22_97090_97097.dmp
147050 2022.11.22 14:31:47 22_97090_97097.log
139440128 2025.01.22 14:46:39 bkedump.dmp
147035 2025.01.22 14:46:43 bkedump.log
Note that at present, non Autonomous Databases do not have a way to list directory contents. If you can log in to the database server, you can get a listing there.
Databases in Oracle Cloud Infrastructure, in particular Autonomous Databases have a predefined set of directories and can access cloud storage in so called buckets. The ocp command can be used to copy files in either direction between such a database directory and a cloud bucket. In order to do so, there are a few necessary preparation steps that must be done once for each tenancy and cloud user:
In order to copy an a file from a database directory to a cloud bucket, you need to have an Auth Token. You may already have an one, which you can use to create the necessary cloud credential.
If you do not have an Auth Token, you can create one from your cloud console: Go to your own user under Identity->Users and select the option to create an Auth Token. Note that you can only have very few Auth Tokens and that the token identifier (a string of some 20 random characters) will only be displayed once, so you need to save it safely.
You subsequently need to add your Auth Token as a credential in the ADMIN account of your autonomous database, which is done using dbms_cloud.create_credential. A typical call to do this would be to execute something like the following using sqlplus:
begin
dbms_cloud.create_credential
( 'credname'
, username=>'first.last@example.com'
, password=>'abc.123-defgji>XYZ('
);
end;
where the password is the contents of the Auth Token and the username is your email address. If you have access to multiple autonomous databases in the same tenancy, you need to add the Auth Token by calling dbms_cloud.create_credentail once for each database. The same auth token should be added to every Autonomous Database where you will be using ocp to access a cloud bucket. Note that the credential name given as the first argument to dbms_cloud.create_credential is given to the ocp command using the --credential option and that doing so implies ocp copies between the database and the cloud bucket rather than between database and the local file system.
From your OCI console, go to the Object Storage and create a bucket. This step needs to be repeated for each relevant region, but the same bucket can very well be used for multiple databases in the same region.
A bucket is identified by two values: The credential name and a so called location URI. You can provide the full location URI directly, but in most cases ocp can generate it from three values: A region, a namespace and a bucket. These values must be given to ocp in addition to the -l option to log on to the database. A sample call (listing the bucket contents) is:
ocp -l admin/{password}@sample_tp --credential=credname --list-bucket \
--region=us-phoenix-1 --namespace=mytenancy --bucket=mybuck
RWP*OCP Release 3.2.0.17 Development on Thu, 24 Apr 2025 13:12:04 UTC
Connected default database to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Contents of cloud bucket at 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/mytenancy/b/mybuck/o/'
bytes last modified file name
252809216 2022.11.22 16:44:59 sample01.dmp
3806724096 2022.11.24 11:30:30 sample-02.dmp
88100 2025.03.30 20:01:19 xyz.txt
In stead of the last three arguments, you could have given the full location URI as this call shows:
ocp -l admin/{password}@sample_tp --credential=credname --list-bucket\
--location-uri=https://objectstorage.us-phoenix-1.oraclecloud.com/n/mytenancy/b/mybuck/o/
If you provide exactly two arguments to ocp in addition to the arguments shown above, these two arguments are respectively the source and the destination file. One of these must be in the format DIRECTORY:filename and the other simply a file name. If the destination already exists you need to give the --overwrite option to do so. A sample copy from the database to the cloud bucket is:
ocp -l admin/{password}@sample_tp --credential=credname --list-bucket \
--region=us-phoenix-1 --namespace=mytenancy --bucket=mybuck \
DATA_PUMP_DIR:22_97090_97097.dmp mydump-90-97.dmp
RWP*OCP Release 3.2.0.17 Development on Thu, 24 Apr 2025 13:24:59 UTC
Connected default database to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Copying DATA_PUMP_DIR:22_97090_97097.dmp to https://objectstorage.us-phoenix-1.oraclecloud.com/n/mytenancy/b/mybuck/o/mydump-90-97.dmp
and a sample copy in the opposite direction is:
ocp -l admin/{password}@sample_tp --credential=credname --list-bucket \
--region=us-phoenix-1 --namespace=mytenancy --bucket=mybuck \
mydump-90-97.dmp DATA_PUMP_DIR:
RWP*OCP Release 3.2.0.17 Development on Thu, 24 Apr 2025 13:24:59 UTC
Connected default database to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Copying https://objectstorage.us-phoenix-1.oraclecloud.com/n/mytenancy/b/mybuck/o/mydump-90-97.dmp to DATA_PUMP_DIR:mydump-90-97.dmp