martinberger.com

OCI Compute Instances – Stop SSH Brute Force Attacks with fail2ban & UseDNS

Autor: Martin Berger 6. November 2018

Every day and night, the SSH login by key into my public accessible Oracle Cloud Infrastructure Linux Compute Instance was permitted for hours. And sometimes, when I had luck, it worked. For me it was not clear when it works and when not. But something has blocked me. The password authentification in the OCI Linux instance is basically disabled, the key is the only way to log in.

After some investigation on the OCI instance, I found a huge amount of login trials in the /var/log/secure file. These brute force attacks were locking me out!

Nov  4 03:57:24 instance-as-1 sshd[1975]: Received disconnect from 132.232.17.146 port 53924:11: Normal Shutdown, Thank you for playing [preauth]
Nov  4 03:57:24 instance-as-1 sshd[1975]: Disconnected from 132.232.17.146 port 53924 [preauth]
Nov  4 04:01:22 instance-as-1 sshd[2194]: Received disconnect from 132.232.17.146 port 59916:11: Normal Shutdown, Thank you for playing [preauth]
Nov  4 04:01:22 instance-as-1 sshd[2194]: Disconnected from 132.232.17.146 port 59916 [preauth]
Nov  4 04:05:28 instance-as-1 sshd[2448]: Received disconnect from 132.232.17.146 port 37640:11: Normal Shutdown, Thank you for playing [preauth]
Nov  4 04:05:28 instance-as-1 sshd[2448]: Disconnected from 132.232.17.146 port 37640 [preauth]
Nov  4 04:20:28 instance-as-1 sshd[3206]: Received disconnect from 125.65.42.187 port 48506:11:  [preauth]
Nov  4 04:20:28 instance-as-1 sshd[3206]: Disconnected from 125.65.42.187 port 48506 [preauth]
Nov  4 04:21:47 instance-as-1 sshd[3275]: Did not receive identification string from 46.101.174.170 port 46073
Nov  4 04:21:47 instance-as-1 sshd[3272]: Received disconnect from 118.123.15.142 port 38448:11:  [preauth]
Nov  4 04:21:47 instance-as-1 sshd[3272]: Disconnected from 118.123.15.142 port 38448 [preauth]
Nov  4 04:24:33 instance-as-1 sshd[3409]: Received disconnect from 118.123.15.210 port 41616:11:  [preauth]
Nov  4 04:24:33 instance-as-1 sshd[3409]: Disconnected from 118.123.15.210 port 41616 [preauth]
Nov  4 04:24:48 instance-as-1 sshd[3421]: Received disconnect from 115.238.245.2 port 50782:11:  [preauth]
Nov  4 04:24:48 instance-as-1 sshd[3421]: Disconnected from 115.238.245.2 port 50782 [preauth]
Nov  4 04:27:08 instance-as-1 sshd[3538]: Received disconnect from 61.184.247.2 port 59804:11:  [preauth]
Nov  4 04:27:08 instance-as-1 sshd[3538]: Disconnected from 61.184.247.2 port 59804 [preauth]
Nov  4 04:32:07 instance-as-1 sshd[3785]: Received disconnect from 125.65.42.192 port 41143:11:  [preauth]
Nov  4 04:32:07 instance-as-1 sshd[3785]: Disconnected from 125.65.42.192 port 41143 [preauth]
Nov  4 04:32:35 instance-as-1 sshd[3811]: Received disconnect from 61.184.247.11 port 57407:11:  [preauth]
Nov  4 04:32:35 instance-as-1 sshd[3811]: Disconnected from 61.184.247.11 port 57407 [preauth]
Nov  4 04:36:02 instance-as-1 sshd[3981]: Received disconnect from 200.46.254.107 port 57063:11: Normal Shutdown, Thank you for playing [preauth]
Nov  4 04:36:02 instance-as-1 sshd[3981]: Disconnected from 200.46.254.107 port 57063 [preauth]
Nov  4 04:36:08 instance-as-1 sshd[3965]: Connection closed by 125.65.42.178 port 60537 [preauth]
Nov  4 04:36:49 instance-as-1 sshd[4020]: Received disconnect from 115.238.245.8 port 51409:11:  [preauth]
Nov  4 04:36:49 instance-as-1 sshd[4020]: Disconnected from 115.238.245.8 port 51409 [preauth]
Nov  4 04:39:32 instance-as-1 sshd[4168]: Invalid user pos from 200.46.254.107 port 50308
Nov  4 04:39:32 instance-as-1 sshd[4168]: input_userauth_request: invalid user pos [preauth]
Nov  4 04:39:32 instance-as-1 sshd[4168]: Received disconnect from 200.46.254.107 port 50308:11: Normal Shutdown, Thank you for playing [preauth]
Nov  4 04:39:32 instance-as-1 sshd[4168]: Disconnected from 200.46.254.107 port 50308 [preauth]
Nov  4 04:39:46 instance-as-1 sshd[4179]: Received disconnect from 122.226.181.165 port 57930:11:  [preauth]
Nov  4 04:39:46 instance-as-1 sshd[4179]: Disconnected from 122.226.181.165 port 57930 [preauth]
Nov  4 04:41:00 instance-as-1 sshd[4250]: Received disconnect from 115.238.245.14 port 42326:11:  [preauth]
Nov  4 04:41:00 instance-as-1 sshd[4250]: Disconnected from 115.238.245.14 port 42326 [preauth]
Nov  4 04:41:17 instance-as-1 sshd[4265]: Received disconnect from 61.184.247.5 port 51434:11:  [preauth]
Nov  4 04:41:17 instance-as-1 sshd[4265]: Disconnected from 61.184.247.5 port 51434 [preauth]

Nov 4 03:57:24 instance-as-1 sshd[1975]: Received disconnect from 132.232.17.146 port 53924:11: Normal Shutdown, Thank you for playing [preauth]

Nov 4 03:57:24 instance-as-1 sshd[1975]: Disconnected from 132.232.17.146 port 53924 [preauth]

Nov 4 04:01:22 instance-as-1 sshd[2194]: Received disconnect from 132.232.17.146 port 59916:11: Normal Shutdown, Thank you for playing [preauth]

Nov 4 04:01:22 instance-as-1 sshd[2194]: Disconnected from 132.232.17.146 port 59916 [preauth]

Nov 4 04:05:28 instance-as-1 sshd[2448]: Received disconnect from 132.232.17.146 port 37640:11: Normal Shutdown, Thank you for playing [preauth]

Nov 4 04:05:28 instance-as-1 sshd[2448]: Disconnected from 132.232.17.146 port 37640 [preauth]

Nov 4 04:20:28 instance-as-1 sshd[3206]: Received disconnect from 125.65.42.187 port 48506:11: [preauth]

Nov 4 04:20:28 instance-as-1 sshd[3206]: Disconnected from 125.65.42.187 port 48506 [preauth]

Nov 4 04:21:47 instance-as-1 sshd[3275]: Did not receive identification string from 46.101.174.170 port 46073

Nov 4 04:21:47 instance-as-1 sshd[3272]: Received disconnect from 118.123.15.142 port 38448:11: [preauth]

Nov 4 04:21:47 instance-as-1 sshd[3272]: Disconnected from 118.123.15.142 port 38448 [preauth]

Nov 4 04:24:33 instance-as-1 sshd[3409]: Received disconnect from 118.123.15.210 port 41616:11: [preauth]

Nov 4 04:24:33 instance-as-1 sshd[3409]: Disconnected from 118.123.15.210 port 41616 [preauth]

Nov 4 04:24:48 instance-as-1 sshd[3421]: Received disconnect from 115.238.245.2 port 50782:11: [preauth]

Nov 4 04:24:48 instance-as-1 sshd[3421]: Disconnected from 115.238.245.2 port 50782 [preauth]

Nov 4 04:27:08 instance-as-1 sshd[3538]: Received disconnect from 61.184.247.2 port 59804:11: [preauth]

Nov 4 04:27:08 instance-as-1 sshd[3538]: Disconnected from 61.184.247.2 port 59804 [preauth]

Nov 4 04:32:07 instance-as-1 sshd[3785]: Received disconnect from 125.65.42.192 port 41143:11: [preauth]

Nov 4 04:32:07 instance-as-1 sshd[3785]: Disconnected from 125.65.42.192 port 41143 [preauth]

Nov 4 04:32:35 instance-as-1 sshd[3811]: Received disconnect from 61.184.247.11 port 57407:11: [preauth]

Nov 4 04:32:35 instance-as-1 sshd[3811]: Disconnected from 61.184.247.11 port 57407 [preauth]

Nov 4 04:36:02 instance-as-1 sshd[3981]: Received disconnect from 200.46.254.107 port 57063:11: Normal Shutdown, Thank you for playing [preauth]

Nov 4 04:36:02 instance-as-1 sshd[3981]: Disconnected from 200.46.254.107 port 57063 [preauth]

Nov 4 04:36:08 instance-as-1 sshd[3965]: Connection closed by 125.65.42.178 port 60537 [preauth]

Nov 4 04:36:49 instance-as-1 sshd[4020]: Received disconnect from 115.238.245.8 port 51409:11: [preauth]

Nov 4 04:36:49 instance-as-1 sshd[4020]: Disconnected from 115.238.245.8 port 51409 [preauth]

Nov 4 04:39:32 instance-as-1 sshd[4168]: Invalid user pos from 200.46.254.107 port 50308

Nov 4 04:39:32 instance-as-1 sshd[4168]: input_userauth_request: invalid user pos [preauth]

Nov 4 04:39:32 instance-as-1 sshd[4168]: Received disconnect from 200.46.254.107 port 50308:11: Normal Shutdown, Thank you for playing [preauth]

Nov 4 04:39:32 instance-as-1 sshd[4168]: Disconnected from 200.46.254.107 port 50308 [preauth]

Nov 4 04:39:46 instance-as-1 sshd[4179]: Received disconnect from 122.226.181.165 port 57930:11: [preauth]

Nov 4 04:39:46 instance-as-1 sshd[4179]: Disconnected from 122.226.181.165 port 57930 [preauth]

Nov 4 04:41:00 instance-as-1 sshd[4250]: Received disconnect from 115.238.245.14 port 42326:11: [preauth]

Nov 4 04:41:00 instance-as-1 sshd[4250]: Disconnected from 115.238.245.14 port 42326 [preauth]

Nov 4 04:41:17 instance-as-1 sshd[4265]: Received disconnect from 61.184.247.5 port 51434:11: [preauth]

Nov 4 04:41:17 instance-as-1 sshd[4265]: Disconnected from 61.184.247.5 port 51434 [preauth]

There is a interesting OCI documentation available called Securing Compute with steps how to secure OCI compute cloud instances – and one of this recommendation is: install fail2ban.

https://docs.cloud.oracle.com/iaas/Content/Security/Reference/compute_security.htm

fail2ban

fail2ban is an open source tool which reads several types of logfiles and creates based on rules new entries in the firewall table to block remote connections. I has default filters for ssh, apache postfix and many more. From Wikipedia:

Fail2Ban is an intrusion prevention software framework that protects computer servers from brute-force attacks.

Link: https://www.fail2ban.org/wiki/index.php/Main_Page

Installation

All steps have to be executed as user root. FYI: I wanted to be informed when a new IP was banned, therefore I have installed sendmail too.

# yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
# yum install fail2ban
# yum install sendmail
# systemctl enable fail2ban
# systemctl start sendmail
# systemctl enable sendmail

# yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

# yum install fail2ban

# yum install sendmail

# systemctl enable fail2ban

# systemctl start sendmail

# systemctl enable sendmail

Configuration

For my fail2ban configuration I have created a new file called jail.local and made my settings there.

# cp /etc/fail2ban/jail.conf /etc/fail2ban/jail.local

1	# cp /etc/fail2ban/jail.conf /etc/fail2ban/jail.local

jail.local

After 2 unsuccesful logins, the source IP will be banned for 86400 seconds. And if a new IP is added to the ban list, I get an email.

[DEFAULT]
sender = OCI
destemail = root
action = %(action_mwl)s
maxretry = 2
destemail = <my_email_address>
bantime = 86400

[sshd]
enabled = true

[DEFAULT]

sender = OCI

destemail = root

action = %(action_mwl)s

maxretry = 2

destemail = <my_email_address>

bantime = 86400

[sshd]

enabled = true

/etc/fail2ban/jail.d/00-firewalld.conf

For OL7 where firewalld is used, verify if the command firewallcmd-ipset is set in /etc/fail2ban/jail.d/00-firewalld.conf. If you use iptables, the command can be changed. Please read the documentation how to change the firewall.

[DEFAULT]
banaction = firewallcmd-ipset

1 2	[DEFAULT] banaction = firewallcmd-ipset

Start fail2ban

# systemctl start fail2ban

1	# systemctl start fail2ban

Verification

Status Check

# fail2ban-client status
Status
|- Number of jail: 1
`- Jail list: sshd

# fail2ban-client status

Status

|- Number of jail: 1

`- Jail list: sshd

Status Check with details, there is already one IP listed.

[root@instance-as-1 ]# fail2ban-client status sshd
Status for the jail: sshd
|- Filter
|  |- Currently failed: 0
|  |- Total failed:     7
|  `- Journal matches:  _SYSTEMD_UNIT=sshd.service + _COMM=sshd
`- Actions
   |- Currently banned: 1
   |- Total banned:     1
   `- Banned IP list:   46.101.204.153

[root@instance-as-1 ]# fail2ban-client status sshd

Status for the jail: sshd

|- Filter

| |- Currently failed: 0

| |- Total failed: 7

| `- Journal matches: _SYSTEMD_UNIT=sshd.service + _COMM=sshd

`- Actions

|- Currently banned: 1

|- Total banned: 1

`- Banned IP list: 46.101.204.153

After some minutes, more entries were recognized in the /var/log/secure log file and added.

# tail -f /var/log/fail2ban.log
2018-11-05 07:54:10,467 fail2ban.filter         [13219]: INFO    [sshd] Found 46.101.204.153
2018-11-05 07:54:10,472 fail2ban.filter         [13219]: INFO    [sshd] Found 46.101.204.153
2018-11-05 07:54:10,478 fail2ban.filter         [13219]: INFO    [sshd] Found 46.101.204.153
2018-11-05 07:54:10,483 fail2ban.filter         [13219]: INFO    [sshd] Found 46.101.204.153
2018-11-05 07:54:11,050 fail2ban.actions        [13219]: NOTICE  [sshd] Ban 46.101.204.153

# tail -f /var/log/fail2ban.log

2018-11-05 07:54:10,467 fail2ban.filter [13219]: INFO [sshd] Found 46.101.204.153

2018-11-05 07:54:10,472 fail2ban.filter [13219]: INFO [sshd] Found 46.101.204.153

2018-11-05 07:54:10,478 fail2ban.filter [13219]: INFO [sshd] Found 46.101.204.153

2018-11-05 07:54:10,483 fail2ban.filter [13219]: INFO [sshd] Found 46.101.204.153

2018-11-05 07:54:11,050 fail2ban.actions [13219]: NOTICE [sshd] Ban 46.101.204.153

firewall-cmd

A new rule is automatically added with the match set failban-sshd.

# firewall-cmd --direct --get-all-rule
ipv4 filter INPUT 0 -p tcp -m multiport --dports ssh -m set --match-set fail2ban-sshd src -j REJECT --reject-with icmp-port-unreachable

1 2	# firewall-cmd --direct --get-all-rule ipv4 filter INPUT 0 -p tcp -m multiport --dports ssh -m set --match-set fail2ban-sshd src -j REJECT --reject-with icmp-port-unreachable

sshd Configuration

After the fail2ban installation, there were other entries left in /var/log/secure.

Nov  6 04:50:25 instance-as-1 sshd[5885]: Received disconnect from 209.141.51.85 port 38056:11: Bye Bye [preauth]
Nov  6 04:50:25 instance-as-1 sshd[5885]: Disconnected from 209.141.51.85 port 38056 [preauth]
Nov  6 04:50:27 instance-as-1 sshd[5887]: reverse mapping checking getaddrinfo for offshore.onion [209.141.51.85] failed - POSSIBLE BREAK-IN ATTEMPT!
Nov  6 04:50:27 instance-as-1 sshd[5887]: Received disconnect from 209.141.51.85 port 39492:11: Bye Bye [preauth]
Nov  6 04:50:27 instance-as-1 sshd[5887]: Disconnected from 209.141.51.85 port 39492 [preauth]
Nov  6 04:50:28 instance-as-1 sshd[5889]: reverse mapping checking getaddrinfo for offshore.onion [209.141.51.85] failed - POSSIBLE BREAK-IN ATTEMPT!
Nov  6 04:50:28 instance-as-1 sshd[5889]: Received disconnect from 209.141.51.85 port 40808:11: Bye Bye [preauth]
Nov  6 04:50:28 instance-as-1 sshd[5889]: Disconnected from 209.141.51.85 port 40808 [preauth]
Nov  6 04:50:30 instance-as-1 sshd[5891]: reverse mapping checking getaddrinfo for offshore.onion [209.141.51.85] failed - POSSIBLE BREAK-IN ATTEMPT!
Nov  6 04:50:30 instance-as-1 sshd[5891]: Received disconnect from 209.141.51.85 port 42144:11: Bye Bye [preauth]
Nov  6 04:50:30 instance-as-1 sshd[5891]: Disconnected from 209.141.51.85 port 42144 [preauth]
Nov  6 04:50:31 instance-as-1 sshd[5893]: reverse mapping checking getaddrinfo for offshore.onion [209.141.51.85] failed - POSSIBLE BREAK-IN ATTEMPT!

Nov 6 04:50:25 instance-as-1 sshd[5885]: Received disconnect from 209.141.51.85 port 38056:11: Bye Bye [preauth]

Nov 6 04:50:25 instance-as-1 sshd[5885]: Disconnected from 209.141.51.85 port 38056 [preauth]

Nov 6 04:50:27 instance-as-1 sshd[5887]: reverse mapping checking getaddrinfo for offshore.onion [209.141.51.85] failed - POSSIBLE BREAK-IN ATTEMPT!

Nov 6 04:50:27 instance-as-1 sshd[5887]: Received disconnect from 209.141.51.85 port 39492:11: Bye Bye [preauth]

Nov 6 04:50:27 instance-as-1 sshd[5887]: Disconnected from 209.141.51.85 port 39492 [preauth]

Nov 6 04:50:28 instance-as-1 sshd[5889]: reverse mapping checking getaddrinfo for offshore.onion [209.141.51.85] failed - POSSIBLE BREAK-IN ATTEMPT!

Nov 6 04:50:28 instance-as-1 sshd[5889]: Received disconnect from 209.141.51.85 port 40808:11: Bye Bye [preauth]

Nov 6 04:50:28 instance-as-1 sshd[5889]: Disconnected from 209.141.51.85 port 40808 [preauth]

Nov 6 04:50:30 instance-as-1 sshd[5891]: reverse mapping checking getaddrinfo for offshore.onion [209.141.51.85] failed - POSSIBLE BREAK-IN ATTEMPT!

Nov 6 04:50:30 instance-as-1 sshd[5891]: Received disconnect from 209.141.51.85 port 42144:11: Bye Bye [preauth]

Nov 6 04:50:30 instance-as-1 sshd[5891]: Disconnected from 209.141.51.85 port 42144 [preauth]

Nov 6 04:50:31 instance-as-1 sshd[5893]: reverse mapping checking getaddrinfo for offshore.onion [209.141.51.85] failed - POSSIBLE BREAK-IN ATTEMPT!

After changing the parameter UseDNS to no in /etc/ssdh/sshd_config and a restart, these entries were history.

# vi /etc/ssh/sshd_config
Set UseDNS no

1 2	# vi /etc/ssh/sshd_config Set UseDNS no

# service sshd stop
# service sshd start

1 2	# service sshd stop # service sshd start

Summary

Never let a OCI compute cloud running with a public IP without to monitor login attemps! fail2ban is one step to get more security. It is easy to configure and it helps a lot. But you have to do the basic work like software updates, SSH key enabling etc. The Oracle documentation is a good base to start! My next step will be to install and configure WAZUH – I keep you up to date!

Links

https://www.oracle.com/technetwork/articles/servers-storage-admin/tips-harden-oracle-linux-1695888.html

https://fedoraproject.org/wiki/Fail2ban_with_FirewallD

Oracle Cloud Infrastructure, Oracle Linux

Easy Database Migration to Oracle Cloud Infrastructure OCI by Creating a Backup in the Cloud

Autor: Martin Berger 2. November 2018

Oracle has provided an updated OCI command line toolset with a new and easy method to migrate an on-premises database into the Oracle Cloud Infrastructure as DBaaS. According the document here, I have tried it out – and it works:

https://docs.cloud.oracle.com/iaas/Content/Database/Tasks/mig-onprembackup.htm

My Test Setup

Oracle 18c Enterprise Edition with SID=ORA18
Single Tenant Architecture
Oracle Linux 7.4
non TDE – Attention: non TDE on-premises data stays unencrypted in the cloud !!!

Database and Server Prerequisites

Archivelog Mode is enabled
Parameter db_create_file_dest is set
Server needs Internet Connection
Oracle OCI CLI installed and configured – https://docs.cloud.oracle.com/iaas/Content/API/SDKDocs/cliinstall.htm#bash
OPC Backup Module available – https://www.oracle.com/technetwork/database/availability/oracle-cloud-backup-2162729.html
Java installed – https://www.java.com/de/download/help/linux_x64rpm_install.xml

The OCI CLI Directory

OCI CLI and opc_install.jar plus the .pem file have to be in the same directory.

oracle@ol7ora18:~/migrate/ [ORA18] ll
total 1828
drwxr-xr-x 7 oracle oinstall 4096 Nov 2 12:45 .
drwx------. 23 oracle oinstall 4096 Nov 2 12:45 ..
drwxr-xr-x 2 oracle oinstall 4096 Nov 1 09:34 bin
drwxr-xr-x 2 oracle oinstall 43 Nov 1 09:34 cx_Oracle-doc
drwxr-xr-x 2 oracle oinstall 23 Nov 1 09:33 include
drwxr-xr-x 3 oracle oinstall 23 Nov 1 09:33 lib
lrwxrwxrwx 1 oracle oinstall 3 Nov 1 09:33 lib64 -> lib
drwxr-xr-x 2 oracle oinstall 39 Nov 1 09:34 oci-cli-scripts
-rw------- 1 oracle oinstall 1679 Nov 2 12:36 oci_api_key.pem
-rw-r--r-- 1 oracle oinstall 944514 Jun 12 09:37 opc_install.jar
-rw-r--r-- 1 oracle oinstall 891380 Nov 2 12:35 opc_installer.zip
-rw-r--r-- 1 oracle oinstall 11093 May 7 17:27 opc_readme.txt
-rw-r--r-- 1 oracle oinstall 59 Nov 1 09:33 pip-selfcheck.json

oracle@ol7ora18:~/migrate/ [ORA18] ll

total 1828

drwxr-xr-x 7 oracle oinstall 4096 Nov 2 12:45 .

drwx------. 23 oracle oinstall 4096 Nov 2 12:45 ..

drwxr-xr-x 2 oracle oinstall 4096 Nov 1 09:34 bin

drwxr-xr-x 2 oracle oinstall 43 Nov 1 09:34 cx_Oracle-doc

drwxr-xr-x 2 oracle oinstall 23 Nov 1 09:33 include

drwxr-xr-x 3 oracle oinstall 23 Nov 1 09:33 lib

lrwxrwxrwx 1 oracle oinstall 3 Nov 1 09:33 lib64 -> lib

drwxr-xr-x 2 oracle oinstall 39 Nov 1 09:34 oci-cli-scripts

-rw------- 1 oracle oinstall 1679 Nov 2 12:36 oci_api_key.pem

-rw-r--r-- 1 oracle oinstall 944514 Jun 12 09:37 opc_install.jar

-rw-r--r-- 1 oracle oinstall 891380 Nov 2 12:35 opc_installer.zip

-rw-r--r-- 1 oracle oinstall 11093 May 7 17:27 opc_readme.txt

-rw-r--r-- 1 oracle oinstall 59 Nov 1 09:33 pip-selfcheck.json

Set Environment Variables

oracle@ol7ora18:~/ [ORA18] export AD=EUZg:EU-FRANKFURT-1-AD-2 
oracle@ol7ora18:~/ [ORA18] export C=ocid1.compartment.oc1..aaaaaaaaburam3rql3blnmxo123456789123456789abcdef 
oracle@ol7ora18:~/ [ORA18] rm -rf /home/oracle/migrate/onprem_upload 
oracle@ol7ora18:~/ [ORA18] cd /home/oracle/migrate/oci-cli-scripts

oracle@ol7ora18:~/ [ORA18] export AD=EUZg:EU-FRANKFURT-1-AD-2

oracle@ol7ora18:~/ [ORA18] export C=ocid1.compartment.oc1..aaaaaaaaburam3rql3blnmxo123456789123456789abcdef

oracle@ol7ora18:~/ [ORA18] rm -rf /home/oracle/migrate/onprem_upload

oracle@ol7ora18:~/ [ORA18] cd /home/oracle/migrate/oci-cli-scripts

Execute the Database Migration Job

In the background:

The script installs and configures temporarily the OPC Backup Module
A RMAN backup job will be started with encrypted backups into the cloud on ObjectStorage
After the successful backup, the temporarily created files are removed

oracle@ol7ora18:~/migrate/bin/ [ORA18] ./create_backup_from_onprem --config-file /home/oracle/.oci/config --display-name testimport01 --availability-domain $AD --edition STANDARD_EDITION --opc-installer-dir /home/oracle/migrate --tmp-dir /home/oracle/migrate/onprem_upload --compartment-id $C --rman-password welcome1
Connecting to Oracle database
Oracle version is:18.3.0.0.0
Checking the archive log mode of the database
Checking if the database is open
Getting database name and database unique name
Database Id:1281053557 Name:ORA18 UniqueName:ORA18
Fetching character set
Character Set:AL32UTF8
Fetching national character set
National Character Set:AL16UTF16
Fetching rac mode
Rac mode:FALSE
Creating external backup job resource...
Created external backup job resource with id: ocid1.dbbackup.oc1.eu-frankfurt-1.abcdefghijklmnopqrst1234567890
Creating external backup job resource...
Creating external backup job resource...
Waiting for completion of external backup job...
Uploading parameter logs
Setting up opc installer
Executing command: java -jar /home/oracle/migrate/opc_install.jar -host https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/dbbackupfra -opcId 'iyA7xeIeR3abcdefghIJ' -opcPass <redacted_password> -walletDir /home/oracle/migrate/onprem_upload -libDir /home/oracle/migrate/onprem_upload -configFile /home/oracle/migrate/onprem_upload/opcORA18.ora -container iyA7xeIeRabcdefghIJKl
Executing RMAN. It will take a few minutes to complete..
Completing the external backup job..
Response:200
External Backup created.

oracle@ol7ora18:~/migrate/bin/ [ORA18] ./create_backup_from_onprem --config-file /home/oracle/.oci/config --display-name testimport01 --availability-domain $AD --edition STANDARD_EDITION --opc-installer-dir /home/oracle/migrate --tmp-dir /home/oracle/migrate/onprem_upload --compartment-id $C --rman-password welcome1

Connecting to Oracle database

Oracle version is:18.3.0.0.0

Checking the archive log mode of the database

Checking if the database is open

Getting database name and database unique name

Database Id:1281053557 Name:ORA18 UniqueName:ORA18

Fetching character set

Character Set:AL32UTF8

Fetching national character set

National Character Set:AL16UTF16

Fetching rac mode

Rac mode:FALSE

Creating external backup job resource...

Created external backup job resource with id: ocid1.dbbackup.oc1.eu-frankfurt-1.abcdefghijklmnopqrst1234567890

Creating external backup job resource...

Waiting for completion of external backup job...

Uploading parameter logs

Setting up opc installer

Executing command: java -jar /home/oracle/migrate/opc_install.jar -host https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/dbbackupfra -opcId 'iyA7xeIeR3abcdefghIJ' -opcPass <redacted_password> -walletDir /home/oracle/migrate/onprem_upload -libDir /home/oracle/migrate/onprem_upload -configFile /home/oracle/migrate/onprem_upload/opcORA18.ora -container iyA7xeIeRabcdefghIJKl

Executing RMAN. It will take a few minutes to complete..

Completing the external backup job..

Response:200

External Backup created.

Created Files for Backup and Transfer and the RMAN Logfile

oracle@ol7ora18:~/migrate/onprem_upload/ [rdbms18000] lr 
total 86268 
-rw-r--r-- 1 oracle oinstall 743 Nov 2 12:42 parameter.log 
-rw------- 1 oracle oinstall 0 Nov 2 12:43 cwallet.sso.lck 
-rw------- 1 oracle oinstall 1645 Nov 2 12:43 cwallet.sso 
-rw-r--r-- 1 oracle oinstall 206 Nov 2 12:43 opcORA18.ora 
-rw-r--r-- 1 oracle oinstall 16801 Nov 2 12:44 bulkimport.pl 
-rw-r--r-- 1 oracle oinstall 13730 Nov 2 12:44 odbsrmt.pl 
-rw-r--r-- 1 oracle oinstall 286 Nov 2 12:44 metadata.xml 
-rw-r--r-- 1 oracle oinstall 88215837 Nov 2 12:44 libopc.so 
-rw-r--r-- 1 oracle oinstall 40661 Nov 2 12:44 odbsrmt.pm 
-rw-r--r-- 1 oracle oinstall 11140 Nov 2 12:44 perl_readme.txt 
-rw-r--r-- 1 oracle oinstall 1118 Nov 2 12:44 rman.sql 
-rw-r--r-- 1 oracle oinstall 5205 Nov 2 12:44 rman.log

oracle@ol7ora18:~/migrate/onprem_upload/ [rdbms18000] lr

total 86268

-rw-r--r-- 1 oracle oinstall 743 Nov 2 12:42 parameter.log

-rw------- 1 oracle oinstall 0 Nov 2 12:43 cwallet.sso.lck

-rw------- 1 oracle oinstall 1645 Nov 2 12:43 cwallet.sso

-rw-r--r-- 1 oracle oinstall 206 Nov 2 12:43 opcORA18.ora

-rw-r--r-- 1 oracle oinstall 16801 Nov 2 12:44 bulkimport.pl

-rw-r--r-- 1 oracle oinstall 13730 Nov 2 12:44 odbsrmt.pl

-rw-r--r-- 1 oracle oinstall 286 Nov 2 12:44 metadata.xml

-rw-r--r-- 1 oracle oinstall 88215837 Nov 2 12:44 libopc.so

-rw-r--r-- 1 oracle oinstall 40661 Nov 2 12:44 odbsrmt.pm

-rw-r--r-- 1 oracle oinstall 11140 Nov 2 12:44 perl_readme.txt

-rw-r--r-- 1 oracle oinstall 1118 Nov 2 12:44 rman.sql

-rw-r--r-- 1 oracle oinstall 5205 Nov 2 12:44 rman.log

Excerpt from the rman.log

Well known from the Oracle Cloud Backup module .

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Nov 2 12:44:03 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA18 (DBID=1281053557)

RMAN> set echo on
2> set encryption on identified by * only;
3> run {
4> allocate channel odbms0 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';
5> allocate channel odbms1 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';
6> allocate channel odbms2 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';
7> allocate channel odbms3 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';
8> allocate channel odbms4 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';
9> backup as compressed backupset database tag 'DBTLongterm1541159054034Zmg' format 'DBTLongterm1541159054034Zmg__%d_%I_%U_%T_%t' keep until time 'sysdate+29000' restore point 'DBTLongterm1541159054034Zmg';
10> }
11>
echo set on

executing command: SET encryption
using target database control file instead of recovery catalog

allocated channel: odbms0
channel odbms0: SID=274 device type=SBT_TAPE
channel odbms0: Oracle Database Backup Service Library VER=12.2.0.2

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Nov 2 12:44:03 2018

Version 18.3.0.0.0

connected to target database: ORA18 (DBID=1281053557)

RMAN> set echo on

2> set encryption on identified by * only;

3> run {

4> allocate channel odbms0 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';

5> allocate channel odbms1 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';

6> allocate channel odbms2 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';

7> allocate channel odbms3 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';

8> allocate channel odbms4 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';

9> backup as compressed backupset database tag 'DBTLongterm1541159054034Zmg' format 'DBTLongterm1541159054034Zmg__%d_%I_%U_%T_%t' keep until time 'sysdate+29000' restore point 'DBTLongterm1541159054034Zmg';

10> }

11>

echo set on

executing command: SET encryption

using target database control file instead of recovery catalog

allocated channel: odbms0

channel odbms0: SID=274 device type=SBT_TAPE

channel odbms0: Oracle Database Backup Service Library VER=12.2.0.2

RMAN List Backup – Excerpt and Verification

The RMAN backup is encrypted by default.

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Nov 2 12:44:03 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA18 (DBID=1281053557)

RMAN> set echo on
2> set encryption on identified by * only;
3> run {
4> allocate channel odbms0 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';
5> allocate channel odbms1 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';
6> allocate channel odbms2 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';
7> allocate channel odbms3 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';
8> allocate channel odbms4 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';
9> backup as compressed backupset database tag 'DBTLongterm1541159054034Zmg' format 'DBTLongterm1541159054034Zmg__%d_%I_%U_%T_%t' keep until time 'sysdate+29000' restore point 'DBTLongterm1541159054034Zmg';
10> }
11>

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Nov 2 12:44:03 2018

Version 18.3.0.0.0

connected to target database: ORA18 (DBID=1281053557)

RMAN> set echo on

2> set encryption on identified by * only;

3> run {

4> allocate channel odbms0 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';

5> allocate channel odbms1 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';

6> allocate channel odbms2 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';

7> allocate channel odbms3 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';

8> allocate channel odbms4 type sbt PARMS='SBT_LIBRARY=/home/oracle/migrate/onprem_upload/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/migrate/onprem_upload/opcORA18.ora)';

10> }

11>

OCI Cloud Console the Backup called testimport is available to create a new DaBaaS Database

Listed as Standalone Backup.

Now we create a new database based on the Standalon Backup.

Enter the RMAN backup password from the CLI job.

The database will be re-created now.

CLI Error Messages

oracle@ol7ora18:~/migrate/oci-cli-scripts/ [ORA18] ./create_backup_from_onprem --config-file /home/oracle/.oci/config --display-name testimport01 --availability-domain $AD --edition STANDARD_EDITION --opc-installer-dir /home/oracle/migrate --tmp-dir /home/oracle/migrate/onprem_upload --compartment-id $C --rman-password *****
Connecting to Oracle database
Oracle version is:18.3.0.0.0
OMF is required for the script to work.

oracle@ol7ora18:~/migrate/oci-cli-scripts/ [ORA18] ./create_backup_from_onprem --config-file /home/oracle/.oci/config --display-name testimport01 --availability-domain $AD --edition STANDARD_EDITION --opc-installer-dir /home/oracle/migrate --tmp-dir /home/oracle/migrate/onprem_upload --compartment-id $C --rman-password *****

Connecting to Oracle database

Oracle version is:18.3.0.0.0

OMF is required for the script to work.

oracle@ol7ora18:~/migrate/oci-cli-scripts/ [ORA18] ./create_backup_from_onprem --config-file /home/oracle/.oci/config --display-name testimport01 --availability-domain $AD --edition STANDARD_EDITION --opc-installer-dir /home/oracle/migrate --tmp-dir /home/oracle/migrate/onprem_upload --compartment-id $C --rman-password *****
Connecting to Oracle database
Oracle version is:18.3.0.0.0
Checking the archive log mode of the database
Database should be in archivelog mode

Connecting to Oracle database

Oracle version is:18.3.0.0.0

Checking the archive log mode of the database

Database should be in archivelog mode

Summary

The new CLI script makes OCI migrations much easier. than before. Depending on the database size and your network bandwith, it works smart and fast. Take time to read the manual carefully to fullfill the prerequisites,

18c, Oracle Cloud Infrastructure, Oracle Tools

Oracle Autonomous Transaction Processing – Move your Data with Oracle Data Pump – Part 3

Autor: Martin Berger 5. Oktober 2018

In this blog post serie which has three parts, I want to describe how data will be uploaded from an on-premises environment into the Oracle Autonomous Transaction Processing database using Oracle Data Pump.

Part 1: Creation of the ATP database and connection verification
Part 2: Export of on-premises data with Oracle Data Pump and move into into the Oracle cloud
Part 3: Import of data into the ATP database with Oracle Data Pump

Oracle Import Prerequisites

Credentials

To get acccess to the dump file on the Oracle Object Storage, a credential has to be created in the Oracle Autonomous Transaction Processing database with the DBMS_CLOUD procedure. For more information about the package, see the blog post from Christian Antognini – DBMS_CLOUD Package – A Reference Guide.

The DBMS_CLOUD procedure needs a password value which is the token from the user account. If you don’t now your token, create a new one. Go to Identity – Users – your username and click on the left side on „Auth Tokens“. Create a new token by click on „Generate Token“. The random generated string is the value for the DBMS_CLOUD password.

Enter a name for the token and click on „Generate Token“.

Note your generated token and „Close“ the window.

C:\oracle\product\instantclient_18_3>sqlplus admin/***MY_PASSWORD***@atphrdata_high

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Oct 5 13:43:58 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Last Successful login time: Fri Oct 05 2018 10:47:04 +02:00

Connected to:
Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> BEGIN
2      DBMS_CLOUD.CREATE_CREDENTIAL(
3        credential_name => 'ATPCS_CRED',
4        username => 'martin.berger@trivadis.com',
5        password => '***MY_TOKEN***'
6      );
7 END;
8 /

PL/SQL procedure successfully completed.

C:\oracle\product\instantclient_18_3>sqlplus admin/***MY_PASSWORD***@atphrdata_high

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Oct 5 13:43:58 2018

Version 18.3.0.0.0

Last Successful login time: Fri Oct 05 2018 10:47:04 +02:00

Connected to:

Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> BEGIN

2 DBMS_CLOUD.CREATE_CREDENTIAL(

3 credential_name => 'ATPCS_CRED',

4 username => 'martin.berger@trivadis.com',

5 password => '***MY_TOKEN***'

6 );

7 END;

8 /

PL/SQL procedure successfully completed.

A new Database User called HRATP

In the ATP, we create a new user called HRATP. The tablespace DATA is the default tablespace in an Autonomous Transaction Processing database and does not have to be defined.

SQL> CREATE USER hratp IDENTIFIED BY "***MY_PASSWORD***";
SQL> GRANT connect,resource TO hratp;
SQL> ALTER USER hratp QUOTA UNLIMITED ON data;

SQL> CREATE USER hratp IDENTIFIED BY "***MY_PASSWORD***";

SQL> GRANT connect,resource TO hratp;

SQL> ALTER USER hratp QUOTA UNLIMITED ON data;

Oracle Data Pump Import

The impdp tool is part of my Instant Client installation in Part 1 of this blog serie. Oracle recommends to set parameters like

partition_options=merge
transform=segment_attributes:n
transform=dwcs_cvt_iots:y
transform=constraint_use_default_index:y
exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link

Two new Oracle Data Pump parameters to work with the Oracle cloud databases are credential and and dumpfile.

credential: The DBM_CLOUD created credential
dumpfile: The URL where the dumpfile is located

Attention

The URL provided by the Object Storage menu cannot be accessed directly by impdp:

ORA-31640: unable to open dump file "https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<identity_domain>/b/datapump_transfer/o/expdpHR05102018.dmp" for read
ORA-17503: ksfdopn:11 Failed to open file https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<identity_domain>/b/datapump_transfer/o/expdpHR05102018.dmp
ORA-17500: ODM err:ODM HTTP Unauthorized

ORA-31640: unable to open dump file "https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<identity_domain>/b/datapump_transfer/o/expdpHR05102018.dmp" for read

ORA-17503: ksfdopn:11 Failed to open file https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<identity_domain>/b/datapump_transfer/o/expdpHR05102018.dmp

ORA-17500: ODM err:ODM HTTP Unauthorized

The URL has to be mapped from objectstorage to swiftobjectstorage:

https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<identity_domain>/b/datapump_transfer/o/expdpHR05102018.dmp

1	https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<identity_domain>/b/datapump_transfer/o/expdpHR05102018.dmp

https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/<identity_domain>/datapump_transfer/expdpHR05102018.dmp

1	https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/<identity_domain>/datapump_transfer/expdpHR05102018.dmp

Data Pump Import Execution with REMAP of Schema and Tablespace

Start of the Oracle Data Pump job from my Windows client:

$ impdp admin/***ADMIN_PASSWORD***@atphrdata_high directory=data_pump_dir credential=ATPCS_CRED dumpfile=https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/<identity_domain>/datapump_transfer/expdpHR05102018.dmp remap_schema=HR:HRATP remap_tablespace=HRDATA:DATA partition_options=merge transform=segment_attributes:n transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link

Import: Release 18.0.0.0.0 - Production on Fri Oct 5 14:17:17 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_FULL_01": admin/********@atphrdata_high directory=data_pump_dir credential=ATPCS_CRED dumpfile=https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/<identity_domain>/datapump_transfer/expdpHR05102018.dmp remap_schema=HR:HRATP remap_tablespace=HRDATA:DATA partition_options=merge transform=segment_attributes:n transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HRATP" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HRATP"."EMPLOYEES" 17.08 KB 107 rows
. . imported "HRATP"."LOCATIONS" 8.437 KB 23 rows
. . imported "HRATP"."JOB_HISTORY" 7.195 KB 10 rows
. . imported "HRATP"."JOBS" 7.109 KB 19 rows
. . imported "HRATP"."DEPARTMENTS" 7.125 KB 27 rows
. . imported "HRATP"."COUNTRIES" 6.367 KB 25 rows
. . imported "HRATP"."REGIONS" 5.546 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "ADMIN"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Oct 5 12:19:29 2018 elapsed 0 00:00:27

$ impdp admin/***ADMIN_PASSWORD***@atphrdata_high directory=data_pump_dir credential=ATPCS_CRED dumpfile=https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/<identity_domain>/datapump_transfer/expdpHR05102018.dmp remap_schema=HR:HRATP remap_tablespace=HRDATA:DATA partition_options=merge transform=segment_attributes:n transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link

Import: Release 18.0.0.0.0 - Production on Fri Oct 5 14:17:17 2018

Version 18.3.0.0.0

Connected to: Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Master table "ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "ADMIN"."SYS_IMPORT_FULL_01": admin/********@atphrdata_high directory=data_pump_dir credential=ATPCS_CRED dumpfile=https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/<identity_domain>/datapump_transfer/expdpHR05102018.dmp remap_schema=HR:HRATP remap_tablespace=HRDATA:DATA partition_options=merge transform=segment_attributes:n transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"HRATP" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "HRATP"."EMPLOYEES" 17.08 KB 107 rows

. . imported "HRATP"."LOCATIONS" 8.437 KB 23 rows

. . imported "HRATP"."JOB_HISTORY" 7.195 KB 10 rows

. . imported "HRATP"."JOBS" 7.109 KB 19 rows

. . imported "HRATP"."DEPARTMENTS" 7.125 KB 27 rows

. . imported "HRATP"."COUNTRIES" 6.367 KB 25 rows

. . imported "HRATP"."REGIONS" 5.546 KB 4 rows

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job "ADMIN"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Oct 5 12:19:29 2018 elapsed 0 00:00:27

The message about the existing user can be ignored.

Data Pump Logfile

The logfile of the import process cannot be access directly, it has to be moved into the Object Storage with the DBMS_CLOUD package first.

SQL> BEGIN
2          DBMS_CLOUD.PUT_OBJECT(
3              credential_name => 'ATPCS_CRED',
4              object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/<identity_domain>/datapump_transfer/import.log',
5              directory_name => 'DATA_PUMP_DIR',
6              file_name => 'import.log'
7          );
8    END;
9 /

SQL> BEGIN

2 DBMS_CLOUD.PUT_OBJECT(

3 credential_name => 'ATPCS_CRED',

4 object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/<identity_domain>/datapump_transfer/import.log',

5 directory_name => 'DATA_PUMP_DIR',

6 file_name => 'import.log'

7 );

8 END;

9 /

Now the file can be access in the Object Storage menu:

Connect as new User HRATP to verify the Data

C:\oracle\product\instantclient_18_3>sqlplus hratp/***MY_PASSWORD***@atphrdata_high

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Oct 5 14:37:27 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select tname from tab;

TNAME
--------------------------------------------------------------------------------
COUNTRIES
DEPARTMENTS
JOB_HISTORY
REGIONS
LOCATIONS
EMPLOYEES
JOBS
EMP_DETAILS_VIEW

8 rows selected.

C:\oracle\product\instantclient_18_3>sqlplus hratp/***MY_PASSWORD***@atphrdata_high

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Oct 5 14:37:27 2018

Version 18.3.0.0.0

Connected to:

Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select tname from tab;

TNAME

--------------------------------------------------------------------------------

COUNTRIES

DEPARTMENTS

JOB_HISTORY

REGIONS

LOCATIONS

EMPLOYEES

JOBS

EMP_DETAILS_VIEW

8 rows selected.

Summary of Part 3

If all prerequistes are met, the data transfer with Oracle Data Pump is easy to configure and easy to handle. Take care about the token, only 2 token per user can be generated. If you losed it, you have to delete a existing token, rebuild it and re-create the credentials before you can import data again.

Autonomous Database, Oracle Cloud Infrastructure, Oracle RDBMS

Neueste Beiträge

OCI Compute Instances – Stop SSH Brute Force Attacks with fail2ban & UseDNS

fail2ban

Installation

Configuration

Verification

firewall-cmd

sshd Configuration

Summary

Links

Easy Database Migration to Oracle Cloud Infrastructure OCI by Creating a Backup in the Cloud

My Test Setup

Database and Server Prerequisites

The OCI CLI Directory

Set Environment Variables

Execute the Database Migration Job

Created Files for Backup and Transfer and the RMAN Logfile

Excerpt from the rman.log

RMAN List Backup – Excerpt and Verification

OCI Cloud Console the Backup called testimport is available to create a new DaBaaS Database

CLI Error Messages

Summary

Oracle Autonomous Transaction Processing – Move your Data with Oracle Data Pump – Part 3

Oracle Import Prerequisites

Credentials

A new Database User called HRATP

Oracle Data Pump Import

Attention

Data Pump Import Execution with REMAP of Schema and Tablespace

Data Pump Logfile

Connect as new User HRATP to verify the Data

Summary of Part 3

Beiträge Navigation