Pythian Group

Subscribe to Pythian Group feed
Love Your Data
Updated: 15 hours 26 min ago

The Brand New Exadata X8M Deployment Process Revealed

Sun, 2020-07-05 09:41

Here we will see how the deployment process of the new Exadata X8M works.

 

RoCE issues from the factory

Exadata X8M servers are coming from the factory with the RoCE private network disabled. In case the Field Engineer assigned to work on the physical setup of the Exadata did not enable the RoCE network it is your job to do so.

RoCE network must be enabled on all Compute Nodes and also on all Storage Servers.

In Exadata X8M the private network is not on InfiniBand switches anymore, but on RoCE (RDMA over Converged Ethernet) Fabric switches. The interface cards we see in the operating system are re0 and re1.

When checking the active interface cards we cannot see re0 and re1:

[root@ex03db01 ~]# ifconfig
bondeth0: flags=5187<up,broadcast,running,master,multicast>  mtu 1500
        inet 10.201.80.54  netmask 255.255.254.0  broadcast 10.201.81.255
        ether bc:97:e1:68:b2:10  txqueuelen 1000  (Ethernet)
        RX packets 54309  bytes 3744342 (3.5 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 14088  bytes 1318384 (1.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
eth0: flags=4163<up,broadcast,running,multicast>  mtu 1500
        inet 10.201.84.190  netmask 255.255.254.0  broadcast 10.201.85.255
        ether 00:10:e0:ee:c5:6c  txqueuelen 1000  (Ethernet)
        RX packets 279171  bytes 18019054 (17.1 MiB)
        RX errors 0  dropped 1  overruns 0  frame 0
        TX packets 9553  bytes 1693920 (1.6 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        device memory 0x9ca00000-9cafffff
 
eth3: flags=6211<up,broadcast,running,slave,multicast>  mtu 1500
        ether bc:97:e1:68:b2:10  txqueuelen 1000  (Ethernet)
        RX packets 31847  bytes 2396622 (2.2 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 14088  bytes 1318384 (1.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
eth4: flags=6211<up,broadcast,running,slave,multicast>  mtu 1500
        ether bc:97:e1:68:b2:10  txqueuelen 1000  (Ethernet)
        RX packets 22492  bytes 1349520 (1.2 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 2  bytes 104 (104.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
lo: flags=73<up,loopback,running>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 136405  bytes 6139347 (5.8 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 136405  bytes 6139347 (5.8 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

Most of the InfiniBand related commands/tools do not work anymore, but ibstat still does, so we can use that tool to check the state of the private network state:

[root@ex03db01 ~]# ibstat | grep -i 'state\|rate'
                State: Down
                Physical state: Disabled
                Rate: 100
                State: Down
                Physical state: Disabled
                Rate: 100

Checking the config of RoCE interface cards:

[root@ex03db01 ~]# cat /etc/sysconfig/network-scripts/ifcfg-re0
#### DO NOT REMOVE THESE LINES ####
#### %GENERATED BY CELL% ####
DEVICE=re0
BOOTPROTO=none
ONBOOT=no
HOTPLUG=no
IPV6INIT=no
 
[root@ex03db01 ~]# cat /etc/sysconfig/network-scripts/ifcfg-re1
#### DO NOT REMOVE THESE LINES ####
#### %GENERATED BY CELL% ####
DEVICE=re1
BOOTPROTO=none
ONBOOT=no
HOTPLUG=no
IPV6INIT=no

Bringing RoCE interface cards up:

[root@ex03db01 ~]# ifup re0
/sbin/ifup-local: /sbin/ifup-local re0:
/sbin/ifup-local:  + RoCE configuration...
/sbin/ifup-local:  + Matched (wildcard) interface re0.
/sbin/ifup-local:  + RoCE Configuration: /bin/roce_config -i re0...
 
NETDEV=re0; IBDEV=mlx5_0; PORT=1
 + RoCE v2 is set as default rdma_cm preference
 + Tos mapping is set
 + Default roce tos is set to 32
 + Trust mode is set to dscp
 + PFC is configured as 0,1,1,1,1,1,0,0
 + Congestion control algo/mask are set as expected
 + Buffers are configured as 32768,229120,0,0,0,0,0,0
 
Finished configuring "re0" ã½(â¢â¿â¢)ã
 
/sbin/ifup-local:  + Non-RoCE Configuration...
/sbin/ifup-local: Non-RoCE Configuration: Nothing to do for re0.
 
 
[root@ex03db01 ~]# ifup re1
/sbin/ifup-local: /sbin/ifup-local re1:
/sbin/ifup-local:  + RoCE configuration...
/sbin/ifup-local:  + Matched (wildcard) interface re1.
/sbin/ifup-local:  + RoCE Configuration: /bin/roce_config -i re1...
 
NETDEV=re1; IBDEV=mlx5_0; PORT=2
 + RoCE v2 is set as default rdma_cm preference
 + Tos mapping is set
 + Default roce tos is set to 32
 + Trust mode is set to dscp
 + PFC is configured as 0,1,1,1,1,1,0,0
 + Congestion control algo/mask are set as expected
 + Buffers are configured as 32768,229120,0,0,0,0,0,0
 
Finished configuring "re1" ã½(â¢â¿â¢)ã
 
/sbin/ifup-local:  + Non-RoCE Configuration...
/sbin/ifup-local: Non-RoCE Configuration: Nothing to do for re1.

Now we can see that the interfaces re0 and re1 are up, but with no IPs assigned:

[root@ex03db01 ~]# ifconfig
bondeth0: flags=5187<up,broadcast,running,master,multicast>  mtu 1500
        inet 10.201.80.54  netmask 255.255.254.0  broadcast 10.201.81.255
        ether bc:97:e1:68:b2:10  txqueuelen 1000  (Ethernet)
        RX packets 54533  bytes 3767354 (3.5 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 14414  bytes 1349944 (1.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
eth0: flags=4163<up,broadcast,running,multicast>  mtu 1500
        inet 10.201.84.190  netmask 255.255.254.0  broadcast 10.201.85.255
        ether 00:10:e0:ee:c5:6c  txqueuelen 1000  (Ethernet)
        RX packets 279584  bytes 18051211 (17.2 MiB)
        RX errors 0  dropped 1  overruns 0  frame 0
        TX packets 9727  bytes 1720009 (1.6 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        device memory 0x9ca00000-9cafffff
 
eth3: flags=6211<up,broadcast,running,slave,multicast>  mtu 1500
        ether bc:97:e1:68:b2:10  txqueuelen 1000  (Ethernet)
        RX packets 32071  bytes 2419634 (2.3 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 14414  bytes 1349944 (1.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
eth4: flags=6211<up,broadcast,running,slave,multicast>  mtu 1500
        ether bc:97:e1:68:b2:10  txqueuelen 1000  (Ethernet)
        RX packets 22492  bytes 1349520 (1.2 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 2  bytes 104 (104.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
lo: flags=73<up,loopback,running>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 136804  bytes 6157123 (5.8 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 136804  bytes 6157123 (5.8 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
re0: flags=4163<up,broadcast,running,multicast>  mtu 1500
        ether 0c:42:a1:3b:45:12  txqueuelen 1000  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
re1: flags=4163<up,broadcast,running,multicast>  mtu 1500
        ether 0c:42:a1:3b:45:13  txqueuelen 1000  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

We can use ibstat again to confirm the interfaces are enabled:

[root@ex03db01 ~]# ibstat | grep -i 'state\|rate'
                State: Active
                Physical state: LinkUp
                Rate: 100
                State: Active
                Physical state: LinkUp
                Rate: 100
OEDA specifics

To start any Exadata deployment you need the OEDA configuration files. They are a set of files generated by the OEDA (Oracle Exadata Deployment Assistant) tool. OEDA tool is currently a web-based tool that will allow the client to fill up all the IP addresses and hostnames that the new Exadata will be assigned. Normally this step is taken by the client with the support of their network team.

Configuration files needed:

  • Clientname-clustername.xml
  • Clientname-clustername-InstallationTemplate.html
  • Clientname-clustername-preconf.csv

The OEDA tool for Linux is also needed and can be downloaded from the Patch ID 30640393. It is recommended to go with the latest version available, but if the configuration files were generated with a different/older version go with that version to avoid warnings during the execution of the onecommand.

Stage the OEDA for Linux in /u01/onecommand/ and unzip it:

[root@ex03db01 ~]# mkdir -p /u01/onecommand/
[root@ex03db01 ~]# unzip -q p30640393_193800_Linux-x86-64.zip -d /u01/onecommand/
[root@ex03db01 ~]# cd /u01/onecommand/linux-x64

Once in the correct directory run onecommand to list the steps just to make sure it is working:

[root@ex03db01 linux-x64]# ./install.sh -cf /root/config/client-ex03.xml -l
 Initializing
 
1. Validate Configuration File
2. Setup Required Files
3. Create Users
4. Setup Cell Connectivity
5. Verify Infiniband
6. Calibrate Cells
7. Create Cell Disks
8. Create Grid Disks
9. Install Cluster Software
10. Initialize Cluster Software
11. Install Database Software
12. Relink Database with RDS
13. Create ASM Diskgroups
14. Create Databases
15. Apply Security Fixes
16. Install Autonomous Health Framework
17. Create Installation Summary
18. Resecure Machine
applyElasticConfig.sh preparation and execution Technical background

applyElasticConfig.sh is a script, provided by Oracle within the OEDA, which performs the initial setup of the compute nodes and storage servers. That script works with the factory IP range and hostnames by default, but we found a way to trick it and make it work even when the client had already changed the IP addresses and hostnames. The initial setup is basically defining the network configuration, IP addresses, hostnames, DNS and NTP configuration and the script will look for nodes in the IP range of the 172.x.x.x network, so if the client had already changed the IPs and hostnames the script will not find anything. It is worth to mention that there is no documentation about this anywhere in the docs.oracle.com. You can find something here:

  • Configuring Oracle Exadata Database Machine
  • ApplyElasticConfig failed during the execution of elasticConfig.sh (Doc ID 2175587.1)
  • Bug 23064772 OEDA: applyelasticconfig.sh fails with error unable to locate rack item with ulocation

Even though these documents briefly mention the applyElasticConfig.sh script they do not mention how to overcome the issue when the IPs and hostnames were already changed.

Preparation

In order to make the script look for the servers when their hostnames and IPs were changed, you have to edit the es.properties file which is located under /u01/onecommand/linux-x64/properties. Consider changing only the parameters related to the IPs, Subnets, and Hostnames. The variables we care about are: ROCEELASTICNODEIPRANGE, ROCEELASTICILOMIPRANGE, ELASTICSUBNETS and SKIPHOSTNAMECHECK. Change those to the range of IPs found in the Clientname-clustername-InstallationTemplate.html for each network:

  • ROCEELASTICNODEIPRANGE expects the range of IPs in the management network.
  • ROCEELASTICILOMIPRANGE expects the range of IPs of the ILOM of the servers.
  • ELASTICSUBNETS expects the subnet of the management network.
  • SKIPHOSTNAMECHECK defaults to false, so if the hostnames were also changed you want to set this to true.

Find some examples below:

[root@ex03db01 linux-x64]# cat properties/es.properties|grep ELASTIC
#ROCEELASTICNODEIPRANGE=192.168.1.1:192.168.1.99
ROCEELASTICNODEIPRANGE=10.201.84.190:10.201.84.206
ROCEELASTICILOMIPRANGE=10.201.84.196:10.201.84.201
ELASTICCONFIGMARKERFILE=/.elasticConfig
ELASTICRACKNAMES=x5,x6,sl6,x7,x8
QINQELASTICCONFIGMINVERION=20.1.0.0.0.200323
#ELASTICSUBNETS=172.16.2:172.16.3:172.16.4:172.16.5:172.16.6:172.16.7
ELASTICSUBNETS=10.201.84
 
[root@ex03db01 linux-x64]# grep SKIPHOST properties/es.properties
#SKIPHOSTNAMECHECK=false
SKIPHOSTNAMECHECK=true
Execution

Now that you have the es.properties ELASTIC* parameters matching your infrastructure configuration you are ready to execute the applyElasticConfig.sh script. To execute it you just need to call the script passing the Clientname-clustername.xml configuration file to it:

[root@ex03db01 linux-x64]# ./applyElasticConfig.sh -cf /root/config/Client-ex03.xml
 Applying Elastic Config...
 Discovering pingable nodes in IP Range of 10.201.84.190 - 10.201.84.206.....
 Found 6 pingable hosts..[10.201.84.193, 10.201.84.194, 10.201.84.195, 10.201.84.190, 10.201.84.191, 10.201.84.192]
 Validating Hostnames..
 Discovering ILOM IP Addresses..
 Getting uLocations...
 Getting Mac Addressess..
 Getting uLocations...
 Mapping Machines with local hostnames..
 Mapping Machines with uLocations..
 Checking if Marker file exists..
 Updating machines with Mac Address for 6 valid machines.
 Creating preconf..
 Writing host-specific preconf files..
 Writing host specific file /u01/onecommand2/linux-x64/WorkDir/ex03cel02_preconf.csv for ex03cel02 ....
 Preconf file copied to ex03cel02 as /var/log/exadatatmp/firstconf/ex03cel02_preconf.csv
 Writing host specific file /u01/onecommand2/linux-x64/WorkDir/ex03db01_preconf.csv for ex03db01 ....
 Preconf file copied to ex03db01 as /var/log/exadatatmp/firstconf/ex03db01_preconf.csv
 Writing host specific file /u01/onecommand2/linux-x64/WorkDir/ex03db03_preconf.csv for ex03db03 ....
 Preconf file copied to ex03db03 as /var/log/exadatatmp/firstconf/ex03db03_preconf.csv
 Writing host specific file /u01/onecommand2/linux-x64/WorkDir/ex03cel03_preconf.csv for ex03cel03 ....
 Preconf file copied to ex03cel03 as /var/log/exadatatmp/firstconf/ex03cel03_preconf.csv
 Writing host specific file /u01/onecommand2/linux-x64/WorkDir/ex03cel01_preconf.csv for ex03cel01 ....
 Preconf file copied to ex03cel01 as /var/log/exadatatmp/firstconf/ex03cel01_preconf.csv
 Writing host specific file /u01/onecommand2/linux-x64/WorkDir/ex03db02_preconf.csv for ex03db02 ....
 Preconf file copied to ex03db02 as /var/log/exadatatmp/firstconf/ex03db02_preconf.csv
 Running Elastic Configuration on ex03cel02.client.com
 Running Elastic Configuration on ex03db01.client.com
 Running Elastic Configuration on ex03db03.client.com
 Running Elastic Configuration on ex03cel03.client.com
 Running Elastic Configuration on ex03cel01.client.com
 Running Elastic Configuration on ex03db02.client.com
 /////
OEDA onecommand preparation and execution Technical background

OEDA is a set of scripts, files, and a form we use to plan and deploy an Exadata. Sometimes we refer to it as the onecommand utility. It is called onecommand because with just one command we can deploy everything. This onecommand is the install.sh script.

Preparation

To be able to run the install.sh script we have to prepare some things first in the environment. Some prerequisites:

  • The switches must have been already set up by the Field Engineer responsible for the physical installation of the hardware.
  • The applyElasticConfig.sh script must have been run and completed successfully.
  • The files listed in the “Appendix B” of the Clientname-clustername-InstallationTemplate.html must be staged to /u01/onecommand/linux-x64/WorkDir.

Stage the files listed in the “Appendix B” of the Clientname-clustername-InstallationTemplate.html to /u01/onecommand/linux-x64/WorkDir:

[root@ex03db01 ~]# ls -lh /u01/onecommand/linux-x64/WorkDir
total X.9G
-rwxr-xr-x 1 root root 355M Jun  9 12:34 ahf_setup
-rw-r--r-- 1 root root 2.9G Jun  9 12:54 V982063-01.zip
-rw-r--r-- 1 root root 2.7G Jun  9 12:57 V982068-01.zip
-rw-r--r-- 1 root root 2.4G Jun  9 12:57 p30805684_190000_Linux-x86-64.zip
-rw-r--r-- 1 root root 600M Jun  9 12:57 p6880880_180000_Linux-x86-64.zip
-rw-r--r-- 1 root root 1.3G Jun  9 12:57 p30899722_190000_Linux-x86-64.zip

After all of this is done you can run the step 1 to validate the configuration files with the environment:

[root@ex03db01 linux-x64]# ./install.sh -cf /root/config/client-ex03.xml -s 1
 Initializing
 Executing Validate Configuration File
 Validating cluster: ex03-clu1
  Locating machines...
 Validating platinum...
 Checking Disk Tests Status....
 Disks Tests are not running/active on any of the Storage Servers or not applicable for this Image Version.
 Validating nodes for database readiness...
 Completed validation...
 
 SUCCESS: Ip address: 10.201.84.190 is configured correctly
 SUCCESS: Ip address: 10.201.80.54 is configured correctly
 SUCCESS: Ip address: 10.201.84.191 is configured correctly
 SUCCESS: Ip address: 10.201.80.55 is configured correctly
 SUCCESS: Ip address: 10.201.84.192 is configured correctly
 SUCCESS: Ip address: 10.201.80.56 is configured correctly
 SUCCESS: Ip address: 10.201.80.60 is configured correctly
 SUCCESS: Ip address: 10.201.80.62 is configured correctly
 SUCCESS: Ip address: 10.201.80.61 is configured correctly
 SUCCESS: Ip address: 10.201.80.58 is configured correctly
 SUCCESS: Ip address: 10.201.80.59 is configured correctly
 SUCCESS: Ip address: 10.201.80.57 is configured correctly
 SUCCESS: Validated NTP server 10.248.1.1
 SUCCESS: Required file /u01/onecommand/linux-x64/WorkDir/V982063-01.zip exists...
 SUCCESS: Required file /u01/onecommand/linux-x64/WorkDir/p30805684_190000_Linux-x86-64.zip exists...
 SUCCESS: Required file /u01/onecommand/linux-x64/WorkDir/V982068-01.zip exists...
 SUCCESS: Required file /u01/onecommand/linux-x64/WorkDir/p6880880_180000_Linux-x86-64.zip exists...
 SUCCESS: Required file /u01/onecommand/linux-x64/WorkDir/p30899722_190000_Linux-x86-64.zip exists...
 SUCCESS: Required file /u01/onecommand/linux-x64/WorkDir/ahf_setup exists...
 SUCCESS: Disks Tests are not running/active on any of the Storage Servers or not applicable for this Image Version.
 SUCCESS: Required Kernel Version 4.14.35.1902.9.2 for Oracle19c found on ex03db01
 SUCCESS: Required Kernel Version 4.14.35.1902.9.2 for Oracle19c found on ex03db02
 SUCCESS: Required Kernel Version 4.14.35.1902.9.2 for Oracle19c found on ex03db03
 SUCCESS: Cluster Version 19.7.0.0.200414 is compatible with UEK5 on  ex03db01
 SUCCESS: Cluster Version 19.7.0.0.200414 is compatible with UEK5 on  ex03db02
 SUCCESS: Cluster Version 19.7.0.0.200414 is compatible with UEK5 on  ex03db03
 SUCCESS: Cluster Version 19.7.0.0.200414 is compatible with image version 19.3.6.0.0 on Cluster ex03-clu1
 SUCCESS: DatabaseHome Version 19.7.0.0.200414 is compatible with image version 19.3.6.0.0 on Cluster ex03-clu1
 SUCCESS: Disk size 14000GB on cell ex03cel01.client.com matches the value specified in the OEDA configuration file
 SUCCESS: Disk size 14000GB on cell ex03cel02.client.com matches the value specified in the OEDA configuration file
 SUCCESS: Disk size 14000GB on cell ex03cel03.client.com matches the value specified in the OEDA configuration file
 SUCCESS: Number of physical disks on ex03cel01.client.com matches the value specified in OEDA configuration file
 SUCCESS: Number of physical disks on ex03cel02.client.com matches the value specified in OEDA configuration file
 SUCCESS: Number of physical disks on ex03cel03.client.com matches the value specified in OEDA configuration file
 Successfully completed execution of step Validate Configuration File [elapsed Time [Elapsed = 85395 mS [1.0 minutes] Tue Jun 09 22:51:44 PDT 2020]]

If it finishes successfully you are good to move forward.

Execution

Now we just need to execute the remaining steps. You can execute one-by-one or all in a row. I normally do the step 1 and step 2 separate from the others just because they tend to fail easier than others. Running all of them in a row would not cause any harm since once any step fails the execution will immediately stop. So it is up to you how you would like to execute it.

In case you need to undo any of the steps you can use the -u and the step you would like to undo. You can use the install.sh -h to help you on that:

[root@ex03db01 linux-x64]# ./install.sh -cf /root/config/Client-ex03.xml -h
 Warning: Invalid input(s) for {-h=null}
 **********************************
 
  install.sh -cf <config.xml> -l [options]
  install.sh -cf <config.xml> -s <step #=''> | -r <num-num>
  install.sh
  ARGUMENTS:
   -l                 List all the steps that exist
   -cf                Use to specify the full path for the config file
   -s <step #=''>        Run only the specified step
   -r <num-num>       Run the steps one after the other as long as no errors
                      are encountered
   -u <num-num> | <step#> Undo a range of steps or a particular step
                      For a range of steps, specify the steps in reverse order
   -h                 Print usage information
   -override          Force to run undo steps related to celldisk and grid disk
   -force             Delete binaries under grid home and database home when
                      uninstalling clusterware and database software
   -delete            Delete staging area/directories
   -nocalibratecell   Create the installation summary file without running the calibrate cell command
   -noinfinicheck     Create the installation summary file without running InfiniBand verification
   -p                 Prompts for root password for each or all the nodes. This option allows
                      deployments in Exadata environments with non-default and/or different
                       root passwords on each of the nodes in the rack
   -usesu             Use SU with root account to run commands for grid/oracle users
   -sshkeys           Run deployment with root SSH Keys that are setup by setuprootssh.sh or oedacli. Must be used with "-usesu"
   -customstep        Run custom actions. Actions can be:
                           updatecellroute:  generate cellroute.ora in domUs
   -clustername       Specify the cluster name, or All. Only used with -customstep to specify
                       the cluster on which to run the custom action
   -upgradeNetworkFirmware  X7 Broadcom network card Firmware upgrade
  Version : 200519

To undo a step simply execute this one to undo step 2:

[root@ex03db01 linux-x64]# ./install.sh -cf /root/config/Client-ex03.xml -u 2

Or to undo from step 2 to step 4:

[root@ex03db01 linux-x64]# ./install.sh -cf /root/config/Client-ex03.xml -u 2-4

Here is the execution of step 2:

[root@ex03db01 linux-x64]# ./install.sh -cf /root/config/client-ex03.xml -s 2
 Initializing
 Executing Setup Required Files
 Copying and extracting required files...
 Required files are:
 /u01/onecommand/linux-x64/WorkDir/p30899722_190000_Linux-x86-64.zip
 /u01/onecommand/linux-x64/WorkDir/p6880880_180000_Linux-x86-64.zip
 /u01/onecommand/linux-x64/WorkDir/p30805684_190000_Linux-x86-64.zip
 /u01/onecommand/linux-x64/WorkDir/V982068-01.zip
 /u01/onecommand/linux-x64/WorkDir/V982063-01.zip
 Copying required files...
 Checking status of remote files...
 Checking status of existing files on remote nodes...
 Getting status of local files...
 Creating symbolic link for file /u01/onecommand/linux-x64/WorkDir/V982063-01.zip at /u01/app/oracle/Oeda/Software/V982063-01.zip
 Creating symbolic link for file /u01/onecommand/linux-x64/WorkDir/V982068-01.zip at /u01/app/oracle/Oeda/Software/V982068-01.zip
 Creating symbolic link for file /u01/onecommand/linux-x64/WorkDir/p30805684_190000_Linux-x86-64.zip at /u01/app/oracle/Oeda/Software/p30805684_190000_Linux-x86-64.zip
 Creating symbolic link for file /u01/onecommand/linux-x64/WorkDir/p30899722_190000_Linux-x86-64.zip at /u01/app/oracle/Oeda/Software/p30899722_190000_Linux-x86-64.zip
 Creating symbolic link for file /u01/onecommand/linux-x64/WorkDir/p6880880_180000_Linux-x86-64.zip at /u01/app/oracle/Oeda/Software/Patches/p6880880_180000_Linux-x86-64.zip
 Copying file: p30805684_190000_Linux-x86-64.zip to node ex03db02.client.com
 Copying file: p30899722_190000_Linux-x86-64.zip to node ex03db02.client.com
 Copying file: p6880880_180000_Linux-x86-64.zip to node ex03db02.client.com
 Copying file: p30805684_190000_Linux-x86-64.zip to node ex03db03.client.com
 Copying file: p30899722_190000_Linux-x86-64.zip to node ex03db03.client.com
 Copying file: p6880880_180000_Linux-x86-64.zip to node ex03db03.client.com
 Completed copying files...
 Extracting required files...
 Copying resourcecontrol and other required files
 No config Keys in the configuration file..
 Creating databasemachine.xml for EM discovery
 Done Creating databasemachine.xml for EM discovery
 Successfully completed execution of step Setup Required Files [elapsed Time [Elapsed = 325110 mS [5.0 minutes] Wed Jun 10 12:16:46 CDT 2020]]

Here is the execution of steps from 3 to 8:

[root@ex03db01 linux-x64]# ./install.sh -cf /root/config/client-ex03.xml -r 3-8
 Initializing
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Create Users
 Creating users...
 Creating users in cluster ex03-clu1
 Validating existing users and groups...
 Creating required directories on nodes in cluster ex03-clu1
 Updating /etc/hosts on nodes in cluster ex03-clu1
 Setting up ssh for users in cluster ex03-clu1
 Creating cell diag collection user CELLDIAG on cell servers..
 Completed creating all users...
 Successfully completed execution of step Create Users [elapsed Time [Elapsed = 77818 mS [1.0 minutes] Wed Jun 10 12:20:31 CDT 2020]]
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Setup Cell Connectivity
 Creating cellip.ora and cellinit.ora  ...
 Creating cellip.ora for cluster ex03-clu1
 Creating cellinit.ora for cluster ex03-clu1
 Done creating cellip.ora and cellinit.ora...
 Successfully completed execution of step Setup Cell Connectivity [elapsed Time [Elapsed = 14675 mS [0.0 minutes] Wed Jun 10 12:20:52 CDT 2020]]
 Executing Verify Infiniband
 Validating infiniband network with rds-ping...
 Check Admin network connectivity...
 Running infinicheck to verify infiniband fabric for cluster ex03-clu1...
 Running verify topology to verify infiniband network...
 No Infiniband link errors found...
 SUCCESS: Verify topology does not report any errors on node ex03db01.client.com...
 ****************ex03db01*****************
 Command: /opt/oracle.SupportTools/ibdiagtools/verify-topology
 Verify topology is not supported on RoCE
 ********************************************
 SUCCESS: Verify topology does not report any errors on node ex03db02.client.com...
 ****************ex03db02*****************
 Command: /opt/oracle.SupportTools/ibdiagtools/verify-topology
 Verify topology is not supported on RoCE
 ********************************************
 SUCCESS: Verify topology does not report any errors on node ex03db03.client.com...
 ****************ex03db03*****************
 Command: /opt/oracle.SupportTools/ibdiagtools/verify-topology
 Verify topology is not supported on RoCE
 ********************************************
 Successfully completed execution of step Verify Infiniband [elapsed Time [Elapsed = 280227 mS [4.0 minutes] Wed Jun 10 12:25:37 CDT 2020]]
 Executing Calibrate Cells
 Calibrating cells...
 Successfully completed execution of step Calibrate Cells [elapsed Time [Elapsed = 461064 mS [7.0 minutes] Wed Jun 10 12:33:18 CDT 2020]]
 Executing Create Cell Disks
 Validating Self-Signed Certificates on cell servers...
 Fixing Cell Certificates on [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com]
 Reconfiguring WLS...
 Cell name attribute does not match hostnames
 Cell ex03cel03 has cell name ru06, cell name attribute will be reset to ex03cel03
 Cell ex03cel01 has cell name ru02, cell name attribute will be reset to ex03cel01
 Cell ex03cel02 has cell name ru04, cell name attribute will be reset to ex03cel02
 Checking physical disks for errors before creating celldisks
 Creating cell disks...
 Dropping Flash Cache before enabling WriteBack on cells [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com]
 Enable FlashCache mode to WriteBack in [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com]
 Creating flashcache on cells...
 Successfully completed execution of step Create Cell Disks [elapsed Time [Elapsed = 218067 mS [3.0 minutes] Wed Jun 10 12:36:56 CDT 2020]]
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Create Grid Disks
 Creating grid disks for cluster ex03-clu1
 Checking Cell Disk status...
 Successfully completed execution of step Create Grid Disks [elapsed Time [Elapsed = 123858 mS [2.0 minutes] Wed Jun 10 12:39:04 CDT 2020]]
[root@ex03db01 linux-x64]#

Here is the execution of steps from 9 to 16:

[root@ex03db01 linux-x64]# ./install.sh -cf /root/config/client-ex03.xml -r 9-16
 Initializing
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Install Cluster Software
 Installing cluster ex03-clu1
 Getting grid disks using utility in /u01/app/19.0.0.0/grid/bin
 Writing grid response file for cluster ex03-clu1
 Running clusterware installer...
 Setting up Opatch for cluster ex03-clu1
 Patching cluster ex03-clu1...
 Successfully completed execution of step Install Cluster Software [elapsed Time [Elapsed = 667497 mS [11.0 minutes] Wed Jun 10 12:51:15 CDT 2020]]
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Initialize Cluster Software
 Initializing cluster ex03-clu1
 Getting grid disks using utility in /u01/app/19.0.0.0/grid/bin
 Writing grid response file for cluster ex03-clu1
 Running root.sh on node ex03db01.client.com
 Checking file root_ex03db01.client.com_2020-06-10_12-54-03-631071286.log on node ex03db01.client.com
 Running root.sh on node ex03db02.client.com
 Checking file root_ex03db02.client.com_2020-06-10_13-02-42-916817198.log on node ex03db02.client.com
 Running root.sh on node ex03db03.client.com
 Checking file root_ex03db03.client.com_2020-06-10_13-05-42-659221162.log on node ex03db03.client.com
 Generating response file for Configuration Tools...
 Getting grid disks using utility in /u01/app/19.0.0.0/grid/bin
 Writing grid response file for cluster ex03-clu1
 Running Configuration Assistants on ex03db01.client.com
 Checking status of cluster...
 Cluster Verification completed successfully
 Successfully completed execution of step Initialize Cluster Software [elapsed Time [Elapsed = 1184567 mS [19.0 minutes] Wed Jun 10 13:11:06 CDT 2020]]
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Install Database Software
 Installing database software ...
 Validating nodes for database readiness...
 Installing database software with database home name DbHome1
 Installing database software ...
 Extracting Database Software file /u01/app/oracle/Oeda/Software/V982063-01.zip into /u01/app/oracle/product/19.0.0.0/dbhome_1
 Running database installer on node ex03db01.client.com ... Please wait...
 After running database installer...
 Patching Database Home /u01/app/oracle/product/19.0.0.0/dbhome_1
 Successfully completed execution of step Install Database Software [elapsed Time [Elapsed = 717961 mS [11.0 minutes] Wed Jun 10 13:23:11 CDT 2020]]
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Relink Database with RDS
 Successfully completed execution of step Relink Database with RDS [elapsed Time [Elapsed = 36009 mS [0.0 minutes] Wed Jun 10 13:23:54 CDT 2020]]
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Create ASM Diskgroups
 Getting grid disks using utility in /u01/app/19.0.0.0/grid/bin
 Getting grid disks using utility in /u01/app/19.0.0.0/grid/bin
 Validating ASM Diskgroups..
 Successfully completed execution of step Create ASM Diskgroups [elapsed Time [Elapsed = 138147 mS [2.0 minutes] Wed Jun 10 13:26:20 CDT 2020]]
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Create Databases
 Setting up Huge Pages for Database..[test]
 Creating database [test]...
 Patch 30805684 requires specific post-installation steps. Databases will be restarted ...
 Running datapatch on database [test]
 Recompiling Invalid Objects (if any) on database [test]
 Successfully completed execution of step Create Databases [elapsed Time [Elapsed = 1252604 mS [20.0 minutes] Wed Jun 10 13:47:19 CDT 2020]]
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Apply Security Fixes
 Setting up Huge Pages for ASM Instance..
 Bouncing clusterware to set required parameters...
 Checking and enabling turbo mode if required...
 ex03db03.client.com Command: /opt/oracle.SupportTools/fix_17898503_Enable_Turbo_Mode.sh produced null output but executed successfully on ex03db03.client.com
 ex03db02.client.com Command: /opt/oracle.SupportTools/fix_17898503_Enable_Turbo_Mode.sh produced null output but executed successfully on ex03db02.client.com
 ex03db01.client.com Command: /opt/oracle.SupportTools/fix_17898503_Enable_Turbo_Mode.sh produced null output but executed successfully on ex03db01.client.com
 Copying over /root/config/client-ex03.xml to all nodes under /etc/exadata/config
 Successfully completed execution of step Apply Security Fixes [elapsed Time [Elapsed = 436720 mS [7.0 minutes] Wed Jun 10 13:54:43 CDT 2020]]
 Disabling Exadata AIDE on  [ex03cel01.client.com, ex03cel02.client.com, ex03cel03.client.com, ex03db01.client.com, ex03db02.client.com, ex03db03.client.com]
 Executing Install Autonomous Health Framework
 Copying over AHF to all nodes in the Cluster..[ex03db01, ex03db02, ex03db03]
 Configuring Autonomous Health Framework(AHF) on all computes nodes..
 AHF has been installed on all compute nodes at: /opt/oracle.ahf . EXAchk can be run by invoking ./exachk
 Generating an EXAchk report...
 EXAchk zip file in ex03db01:/u01/app/oracle.ahf/data/ex03db01/exachk/exachk_ex03db01_test_061020_13567.zip
 Generating the EXAchk Infrastructure Report...
 EXAchk zip file in ex03db01:/u01/app/oracle.ahf/data/ex03db01/exachk/exachk_ex03db01_test_061020_141143_infrastructure.zip
 Successfully completed execution of step Install Autonomous Health Framework [elapsed Time [Elapsed = 2234216 mS [37.0 minutes] Wed Jun 10 14:32:04 CDT 2020]]
[root@ex03db01 linux-x64]#

Here is the execution of step 17:

[root@ex03db01 linux-x64]# ./install.sh -cf /root/config/client-ex03.xml -s 17
 Initializing
 Executing Create Installation Summary
 Getting system details...
 Generating Installation Summary report: /u01/onecommand2/linux-x64/ExadataConfigurations/client-Development-InstallationReport.xml...
 Creating Installation template /u01/onecommand2/linux-x64/ExadataConfigurations/client-InstallationTemplate.html...
 Created Installation template /u01/onecommand2/linux-x64/ExadataConfigurations/client-InstallationTemplate.html
 All deployment reports are stored in /u01/onecommand2/linux-x64/ExadataConfigurations/client-AK00625423-deploymentfiles.zip
 Generating Platinum CSV file and copying it over to /opt/oracle.SupportTools on all compute nodes
 Writing platinum file  : /u01/onecommand2/linux-x64/WorkDir/client_null-platinum.csv
 Successfully completed execution of step Create Installation Summary [elapsed Time [Elapsed = 53311 mS [0.0 minutes] Wed Jun 10 14:36:07 CDT 2020]]

Just a rac-status.sh run to check how the cluster was setup (learn more about rac-status.sh here):

[root@ex03db01 ~]# ./pythian/rac-status.sh -a
 
                Cluster ex03-clu1 is a X8M-2 Elastic Rack HC 14TB
 
        Type      |      Name      |   db01   |   db02   |   db03   |
  ------------------------------------------------------------------
   asm            | asm            |  Online  |  Online  |  Online  |
   asmnetwork     | asmnet1        |  Online  |  Online  |  Online  |
   chad           | chad           |  Online  |  Online  |  Online  |
   cvu            | cvu            |  Online  |     -    |     -    |
   dg             | DATA           |  Online  |  Online  |  Online  |
   dg             | RECO           |  Online  |  Online  |  Online  |
   dg             | SPARSE         |  Online  |  Online  |  Online  |
   network        | net1           |  Online  |  Online  |  Online  |
   ons            | ons            |  Online  |  Online  |  Online  |
   proxy_advm     | proxy_advm     | Offline x| Offline x| Offline x|
   qosmserver     | qosmserver     |  Online  |     -    |     -    |
   vip            | db01           |  Online  |     -    |     -    |
   vip            | db02           |     -    |  Online  |     -    |
   vip            | db03           |     -    |     -    |  Online  |
   vip            | scan1          |     -    |  Online  |     -    |
   vip            | scan2          |     -    |     -    |  Online  |
   vip            | scan3          |  Online  |     -    |     -    |
  ------------------------------------------------------------------
    x  : Resource is disabled
       : Has been restarted less than 24 hours ago
 
      Listener    |      Port      |   db01   |   db02   |   db03   |     Type     |
  ---------------------------------------------------------------------------------
   ASMNET1LSNR_ASM| TCP:1525       |  Online  |  Online  |  Online  |   Listener   |
   LISTENER       | TCP:1521       |  Online  |  Online  |  Online  |   Listener   |
   LISTENER_SCAN1 | TCP:1864       |     -    |  Online  |     -    |     SCAN     |
   LISTENER_SCAN2 | TCP:1864       |     -    |     -    |  Online  |     SCAN     |
   LISTENER_SCAN3 | TCP:1864       |  Online  |     -    |     -    |     SCAN     |
  ---------------------------------------------------------------------------------
       : Has been restarted less than 24 hours ago
 
         DB       |     Version    |   db01   |   db02   |   db03   |    DB Type   |
  ---------------------------------------------------------------------------------
   test           | 19.0.0.0   (1) |   Open   |   Open   |   Open   |    RAC (P)   |
  ---------------------------------------------------------------------------------
  ORACLE_HOME references listed in the Version column
 
         1 : /u01/app/oracle/product/19.0.0.0/dbhome_1  oracle oinstall
 
       : Has been restarted less than 24 hours ago
 
 
[root@ex03db01 ~]# ps -ef|grep pmon
root     362094  50259  0 14:40 pts/1    00:00:00 grep --color=auto pmon
oracle   364290      1  0 13:52 ?        00:00:00 asm_pmon_+ASM1
oracle   367756      1  0 13:53 ?        00:00:00 ora_pmon_test1
[root@ex03db01 ~]#

That’s it. The deployment is finished. Now you just need to patch the compute nodes, storage servers, RoCE switches, GI, and DBs to whatever version you would like to go up to.

You might be thinking “what about step 18”. Well, step 18 “Resecure the machine” means you will harden the servers by dropping SSH keys, enhancing password complexity, expire current passwords, and implement password expiration time, etc. Sometimes those changes make the administration a bit harder and also you might want to implement your own security policies. So we normally skip this step, but again, it is up to you.

See you next time, sincerely,

Franky Faust

 

Categories: DBA Blogs

Oracle Database and Two-Factor Authentication (2FA)

Tue, 2020-06-16 16:36
Background

A common question from Oracle customers is whether they can configure the Oracle Database to use:

  • Two-Factor Authentication (2FA)
  • Multi-Factor Authentication (MFA)
  • A “Time-based One-time Password” (TOTP) code, which is usually a six-digit code generated from a hardware or software application.

The short answer is, yes!(though, not natively). You can implement it through other directory service technologies, namely RADIUS.

Previous blog posts discussed the setup and testing of the new Oracle 18c+ feature of Oracle “Centrally Managed Users” (CMU). It allows you to manage Oracle database users through Microsoft Active Directory (AD), and effectively offloads user management to AD. Oracle RADIUS authentication isn’t part of CMU. It’s a similar but slightly different implementation, and each has its own benefits and limitations.

However, by leveraging Oracle’s compatibility with the RADIUS protocol and an external directory service, you can achieve true 2FA capabilities for the Oracle database (and on a per-user basis, meaning that application and/or service accounts remain unaffected).

This post describes how to implement Oracle database 2FA using FreeRADIUS. The subsequent post extends the setup to use the commercially available Cisco Duo platform instead.

RADIUS Quick Summary

The RADIUS (Remote Authentication Dial-in Service) protocol is based on “AAA:” Authentication, Authorization, and Accounting. This post is based mainly on the Authentication part. Furthermore, RADIUS can operate in two modes: synchronous mode and challenge-response (asynchronous) mode. Oracle Database is actually capable of both (and has been since at least Oracle 8i). However, the most practical and applicable form is “synchronous,” and will therefore be the focus of this post.

RADIUS can authenticate against numerous sources including its own “users” flat file, LDAP directory services, Microsoft Active Directory, and others. RADIUS refers to “clients” and “NASs” (Network Access Servers, which broker the credentials). When authenticating against a RADIUS server, the Oracle Database acts as the “RADIUS client” and “NAS.”

From the Oracle Database “Database Licensing Information User Manual” (part number E94254-18 dated April 2020):

Network encryption (native network encryption, network data integrity, and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of Oracle Database.

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/database-licensing-information-user-manual.pdf

However, to use RADIUS authentication, the Oracle client software must include the Advanced Security option. Therefore, a full client installation is required and the InstantClient is not sufficient. Verify Oracle client software compatibility using the adapters command.

Authentication Process

The process flow is relatively simple, yet important to understand:

  1. The Oracle client attempts to connect to the database and provides credentials (username and password) along with a 2FA TOTP code.
  2. Oracle Database receives this information, finds the user in the local catalog, and determines that the authentication type is EXTERNAL.
  3. Oracle Database then scans the SQLNET.ORA file to determine where to authenticate EXTERNAL users.
  4. Using the RADIUS connectivity information from the SQLNET.ORA file, the Oracle Database passes the credential details onto the RADIUS server.
  5. The RADIUS server first authenticates the username/password with a directory service which could be a local file, Active Directory, an LDAP service, etc. This is “Primary Authentication.”
  6. If validated, the RADIUS server then authenticates the TOTP with the multi-factor authentication service (for example, the Google Authenticator PAM module, Cisco Duo, etc). This is “Secondary Authentication.”
  7. If also validated, the RADIUS server passes back the “Access-Accept” response to the Oracle Database which then accepts and completes the connection.

Here’s an illustration of the process:

Prerequisite Setup

To use 2FA with a TOTP, a “RADIUS server” is required. Some common options include:

This post focuses on setting up and configuring Oracle Database 2FA through FreeRADIUS. FreeRADIUS is a pretty simple software suite for DBAs to set up for testing and experimentation purposes without worrying about licensing costs or third-party vendors.

The first requirement is to find a location to run FreeRADIUS. This can be a local server or a Virtual Machine (for example, created within VirtualBox or a cloud VM). For examples on how to create a cloud VM in OCI for DBA testing and experimentation, go to https://blog.pythian.com/part-1-creating-an-oracle-18c-centrally-managed-users-testbed-using-oracle-cloud-infrastructure/.

VM requirements are minimal for testing/experimentation purposes. One or two vCPUs with 2 GB of memory should be sufficient.

For a base OS, many versions of Linux are supported including CentOS 7 and Oracle Linux 7.

Oracle Database and RADIUS

For an explanation about how Oracle Database interacts with RADIUS (and specifically FreeRADIUS), see Jared Still’s blog post: https://blog.pythian.com/using-freeradius-to-authorize-oracle-connections/

A key takeaway from that post: FreeRADIUS users need to be in UPPERCASE for Oracle Database compatibility.

Installing FreeRADIUS

This section summarizes the installation steps for FreeRADIUS on CentOS 7 or Oracle Linux 7. Perform all steps as “root” user, unless otherwise indicated:

If you are installing on Oracle Linux 7, add the necessary YUM repo (not required for CentOS 7):

cat << EOF > /etc/yum.repos.d/networkradius.repo
[networkradius]
name=NetworkRADIUS-7
baseurl=http://packages.networkradius.com/centos/7/repo/
gpgcheck=0
EOF

Perform other Linux prerequisites:

sed -i 's/=enforcing/=permissive/g' /etc/selinux/config
setenforce Permissive

yum -y update

Install the core FreeRADIUS package and the utilities package. The later adds testing tools:

yum -y install freeradius freeradius-utils

Adjust the Linux firewall (if required):

firewall-cmd --permanent --zone=public --add-port=1812/udp
firewall-cmd --permanent --zone=public --add-port=1813/udp
firewall-cmd --reload

Create a service for automatic-start (but don’t start it yet):

systemctl enable radiusd.service
Initial RADIUS Testing

To perform a basic test of the RADIUS functionality, a test user is required. FreeRADIUS is configured based on several files, including an “authorize” file. This used to be a “users” file. “users” is now just a symbolic link to “authorize“.

IMPORTANT: Directives in the “authorize” file are processed sequentially. Therefore, when testing, it’s recommended you put the entries near or at the top to ensure they aren’t skipped due to some preceding and/or superseding directive.

Add a test user called “testuser” with a password of “Passw0rd” to the file:

sed -i '1itestuser Cleartext-Password := \"Passw0rd\"' /etc/raddb/mods-config/files/authorize
head -4 /etc/raddb/mods-config/files/authorize

To test, it’s recommended to manually start the FreeRADIUS software (instead of through the Linux service) in debug mode:

radiusd -X

From a different window/session, test the connection using the command:

radtest testuser Passw0rd localhost 0 testing123

IMPORTANT: The “testing123” argument is a “RADIUS secret” configured in the default “RADIUS client.” This secret is used for secure communication between RADIUS clients and the server. See Jared Still’s blog post for more information.

The result should be “Received Access-Accept …

FreeRADIUS Linux-PAM Setup

The next step is to set up FreeRADIUS to use Linux-PAM (Pluggable Authentication Modules).

IMPORTANT: For initial proof of concept experimentation and testing, local Linux users are authenticated using RADIUS and 2FA. Therefore, in this test scenario, the RADIUS server runs with root permissions so it can read all 2FA configuration files. In a production or hardened environment (including those using external directory services) you should configure FreeRADIUS to run in a “lower-permission” environment.

Change both user and group to root (in the test environment):

grep 'user\|group' /etc/raddb/radiusd.conf | grep = | grep -v ^[[:space:]]\#
sed -i '/user =/  s/\(\#\)\{0,1\}[[:space:]]user = radius.*/\t user = root/'   /etc/raddb/radiusd.conf
sed -i '/group =/ s/\(\#\)\{0,1\}[[:space:]]group = radius.*/\t group = root/' /etc/raddb/radiusd.conf
grep 'user\|group' /etc/raddb/radiusd.conf | grep = | grep -v ^[[:space:]]\#

Then, enable Pluggable Authentication Modules (PAM) including adding the required symbolic links:

grep pam /etc/raddb/sites-enabled/default
sed -i '/^\#[[:space:]]pam/ s/^\#//' /etc/raddb/sites-enabled/default
grep pam /etc/raddb/sites-enabled/default

ln -s /etc/raddb/mods-available/pam /etc/raddb/mods-enabled/pam
ls -l /etc/raddb/mods-enabled/pam
ls -l /etc/raddb/mods-available/pam

Change auth-type to PAM in the authorize file. Remember that file directives are processed sequentially, so place it near the top:

sed -i '2iDEFAULT Auth-Type := PAM' /etc/raddb/mods-config/files/authorize
head -5 /etc/raddb/mods-config/files/authorize

Finally, add a new Linux user on the same system for testing (remember that this user must be in uppercase for Oracle Database usage):

useradd RADUSER
passwd RADUSER
# Assume the set password is Passw0rd

Test FreeRADIUS and Linux-PAM authorization by running the FreeRADIUS server in debugging mode again:

radiusd -X

From another session/window, test a connection:

radtest RADUSER Passw0rd localhost 0 testing123

The result should again be “Received Access-Accept …

Google Authenticator PAM Module Setup

Google provides an example PAM module for 2FA through https://github.com/google/google-authenticator-libpam (Cisco Duo or Otka provide alternative options).

Install the Google Authenticator PAM module:

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

Perform a basic configuration for the RADUSER local Linux user:

sudo -u RADUSER google-authenticator \
   --time-based \
   --allow-reuse \
   --no-rate-limit \
   --window-size=3 \
   --force

IMPORTANT: The command above uses configuration options optimized for testing. In an actual production environment, I recommend reviewing all options and deploying in a more more secure manner. For example, using “–disallow-reuse” and similar.

Sample output:

You can generate the TOTP codes using a mobile phone application such as “Google Authenticator” or “Authy.” Scan the generated QR code with the phone app or manually enter the “secret key” into the app.

However, when testing, constantly generating codes on a phone can become cumbersome. Generating the codes using a Linux command might be more efficient.

To generate codes from the Linux bash shell, install the oathtool package:

yum -y install oathtool

Then, you can generate codes using the “secret key” from the user’s ~/.google-authenticator file. For example:

oathtool --base32 --totp "`head -1 /home/RADUSER/.google_authenticator`"

Finally, you must adjust the Linux-PAM configuration to reference the new Google Authenticator PAM Module:

cat << EOF > /etc/pam.d/radiusd
#%PAM-1.0
auth       requisite    pam_google_authenticator.so forward_pass
auth       required     pam_sepermit.so
auth       substack     password-auth
auth       include      postlogin
account    required     pam_nologin.so
account    include      password-auth
session    include      password-auth
EOF
Testing Using the Local User and One-time Password

With the FreeRADIUS and Google Authenticator PAM Module implementation of 2FA, the password must be a single string that is comprised of the actual user password and the passcode. So, the format is: <password><TOTP>.

Other implementations (such as Cisco Duo) might allow for a comma between the two, or push notification to a mobile device. However, for FreeRADIUS and the Google Authenticator PAM Module, the format is one continuous string with no additional characters.

So, to test:

MY_PASSWD=Passw0rd
MY_OTP=$(oathtool --base32 --totp "`head -1 /home/RADUSER/.google_authenticator`")
radtest RADUSER ${MY_PASSWD}${MY_OTP} localhost 0 testing123

Example successful output:

 

 

 

 

 

Testing from the Oracle Database

The previously referenced blog post by Jared Still covers Oracle setup for FreeRADIUS. Review this post for additional information about the Oracle setup steps in this section. Repeating the steps in a simplified format:

As “root” on the FreeRADIUS server, add a FreeRADIUS client that the Oracle Databases will use to connect:

cat << EOF >> /etc/raddb/clients.conf

client oracle-db {
    ipaddr = 192.168.1.0/24
    secret = secretoracle
    shortname = oracle
    nastype = other
}
EOF

Restart the FreeRADIUS service to ensure all changes take effect:

systemctl restart radiusd.service
systemctl status radiusd.service

As the “oracle” user (or Oracle software owner) on the Oracle Database server, save the RADIUS secret to a file:

mkdir -p ${ORACLE_HOME}/network/security
echo "secretoracle" > ${ORACLE_HOME}/network/security/radius.key
chmod 600 ${ORACLE_HOME}/network/security/radius.key

Add the required entries to the SQLNET.ORA file and verify the file (add the appropriate IP address for the FreeRADIUS server):

cat <<EOF >> ${ORACLE_HOME}/network/admin/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS,RADIUS)
SQLNET.RADIUS_AUTHENTICATION=192.168.1.212
SQLNET.RADIUS_AUTHENTICATION_PORT=1812
SQLNET.RADIUS_SECRET=${ORACLE_HOME}/network/security/radius.key
EOF

If you want, you can include other parameters such as the RADIUS timeout and retries. Alternatively, for higher availability and redundancy, use the SQLNET.RADIUS_ALTERNATE parameter to specify a backup RADIUS server to use if the primary becomes unavailable.

IMPORTANT: The SQLNET.ORA configuration above is for the database server only (RDBMS home). For Oracle clients, all that’s required is that the SQLNET.AUTHENTICATION_SERVICES parameter includes RADIUS. The other SQLNET.ORA parameters are not required/used in Oracle client homes.

Make sure that the database initialization parameters are set properly:

sqlplus -s / as sysdba << EOF
alter system set os_authent_prefix='' scope=spfile sid='*';
alter system set remote_os_authent=false scope = spfile sid='*';
shutdown immediate
startup
alter pluggable database all open;
EOF

Add the test user to the database:

sqlplus -s / as sysdba << EOF
alter session set container=PDB1;
create user raduser identified externally;
grant create session to raduser;
grant select on v_\$database to raduser;
EOF

Testing is as simple as providing the TOTP in the password field. Because the user was added to the database using “ IDENTIFIED EXTERNALLY,” this is the Linux user’s password.

Using a connection string such as <username>/<password><passcode>, here’s an example of a full connection using SQLPlus:

Success! An Oracle Database connection to a database user using an external credential and 2FA!

If the password or the TOTP code is incorrect, then the standard “ORA-01017: invalid username/password; logon denied” error occurs.

Next Steps: Microsoft Active Directory

For many implementations, the next logical step is implemented using Microsoft Active Directory (AD) users instead of local Linux users. Switching this test setup to use AD instead of local users for authentication is relatively easy and involves the following:

cat << EOF > /etc/pam.d/radiusd
#%PAM-1.0
auth       requisite    pam_google_authenticator.so forward_pass
auth       required     pam_sss.so use_first_pass
account    required     pam_nologin.so
account    include      password-auth
session    include      password-auth
EOF
  • Logging to the FreeRADIUS server using the AD domain user and generating the ~/.google-authenticator configuration file using the previously described process.
  • Creating the domain user in the database and testing.
Conclusion

Configuring the Oracle Database to use 2FA is something that many inquire about but few actually do. However, doing so isn’t overly complex and allows you to leverage open source software such as FreeRADIUS. All that’s required is a small VM to act as a RADIUS server.

In a real production environment, this server could become a single point of failure and therefore might require you to set it up with reliable infrastructure and/or in a redundant configuration (by including the SQLNET.RADIUS_ALTERNATE parameter in the SQLNET.ORA file).

This environment is simple and flexible for DBA testing. However, most enterprise users will instead want to leverage a more robust and flexible solution such as the commercial offerings from Cisco Duo or Otka. Configuring Oracle Database authentication using Cisco Duo is covered in the next post in this series.

Categories: DBA Blogs

AWS RDS: 5 Must-Know Actions for Oracle DBAs

Wed, 2020-06-03 00:00

Managing Oracle on AWS has some twists. Here are five daily DBA activities that have changed on AWS:

Kill Sessions:

begin
rdsadmin.rdsadmin_util.kill(
sid => &sid,
serial => &serial,
method => 'IMMEDIATE');
end;
/

 

Flush shared_pool or buffer_cache:

exec rdsadmin.rdsadmin_util.flush_shared_pool;
exec rdsadmin.rdsadmin_util.flush_buffer_cache;

 

Perform RMAN Operations:

BEGIN
 rdsadmin.rdsadmin_rman_util.validate_database(
 p_validation_type => 'PHYSICAL+LOGICAL',
 p_parallel => 4,
 p_section_size_mb => 10,
 p_rman_to_dbms_output => FALSE);
END;
/

 

Grant Privileges to SYS Objects

# Grant

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_grantee   => 'PYTHIAN',
        p_privilege => 'SELECT');
end;
/

# Grant with Grant Option

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name     => 'V_$SESSION',
        p_grantee      => 'PYTHIAN',
        p_privilege    => 'SELECT',
        p_grant_option => true);
end;
/

# Revoke

begin
    rdsadmin.rdsadmin_util.revoke_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_revokee   => 'PYTHIAN',
        p_privilege => 'SELECT');
end;
/

 

Create Custom Functions to Verify Passwords:

begin
    rdsadmin.rdsadmin_password_verify.create_verify_function(
        p_verify_function_name => 'CUSTOM_PASSWORD_FUNCTION', 
        p_min_length           => 12, 
        p_min_uppercase        => 2, 
        p_min_digits           => 1, 
        p_min_special          => 1,
        p_disallow_at_sign     => true);
end;
/

If you want to double-check the generated code, here’s simple trick: Check on DBA_SOURCE:

col text format a150
select TEXT  from DBA_SOURCE 
where OWNER = 'SYS' and NAME = 'CUSTOM_PASSWORD_FUNCTION' order by LINE;

I hope this helps!

Categories: DBA Blogs

OATUG Forum Online (or, the 2020 Version of Collaborate Las Vegas)

Fri, 2020-05-29 08:58

Hi everyone!

It’s been a very busy year for me, so I haven’t had much time to blog. But, here we go!

This isn’t a technical post, but more of an opinion piece. I’ve split this post into two parts. Part one was written before I spoke at the OATUG (Oracle Applications and Technology Users Group) Forum online. Part two was written after I spoke.

Part 1: Before…

The global pandemic has disrupted virtually everything in our world. The same goes for the world of IT, including one of the best things about our industry: Conferences and live events.

I was supposed to attend two major conferences in the first quarter of 2020. One was cancelled and the other became an online event. Aborting major events like these is no trivial undertaking. Not only for the organizers themselves but also for all the sponsors and attendees (customers and vendors included).

Huge events like Oracle Open World, Google Next, or the one that will be the focus of most of this post, Collaborate, have a long-lasting impact that extends throughout the year. At these events, a casual meeting or a chat during a coffee break can kickstart a relationship. It might develop into a large services agreement, a partnership, or a whole new successful startup.

You have to agree that these events are important, if not crucial, for our industry.

Collaborate

As for Collaborate, well, I was very excited to not only attend but also speak at the event. After attending DOAG, BIWA, and UKOUG the previous years, Collaborate is the next big Oracle-related event in which I’d be participating. Then, of course, came COVID-19.

However, Collaborate isn’t cancelled. Instead, it’s now a fully virtual event. Here I’d like to give a massive KUDOS to the event organizers for their commitment to the event and the attendees. It certainly couldn’t have been easy to make the initial decision to proceed with Collaborate. Nor could it have been easy to completely undo and transform months and months of planning and logistics into a fully virtual conference in a matter of weeks.

I was very pleased to still be selected to speak at the (now) online conference. And, a big thanks to Pythian for their speaker program. It enables me to attend these gatherings, virtual or otherwise.

So, here I am, a few hours prior to my first big online presentation to an unknown number of people, and crossing my fingers that everything works as expected. This is basically how I feel before any “typical” presentation. The main difference? Well, the presentation won’t be quite the same. Interaction with the listening public is close to zero, and there is little practical opportunity for people to reach out to you with questions afterwards.

My Thoughts…

I know, I know… there’s always social media, and so on. However, it’s not the same, and you know it. There’s no immediate feedback from the attendees, and you can’t really tell if they find the content interesting or boring. Nor is it easy to tell whether you should slow down, or speed up. It definitely feels more impersonal.

This is my opinion from a “presenter” standpoint. As an “attendee,” there are two major issues for me: One is that it’s no longer a live in-person event, it’s awkward to ask your manager for time to attend it. The second big issue for me is the time zone. I’m in the Eastern time zone, and the event is based in Las Vegas, so sessions can go all the way from 4:30 PM to 10:30 PM EDT. This, on top of my regular eight-hour workday, is simply too much.

So, I might be able to attend a session or two and watch saved sessions at a later time. This takes me back to my previous point about lost face-to-face interactions with people.

Anyway, I’m really interested to see how this plays out. I really hope the event is a big success because, unfortunately, our current “situation” might last a long time. We need to prepare for that possibility.

Part 2: After…

And… the event is over. Unfortunately, it wasn’t very satisfactory, which makes me a little sad. You see, Collaborate is one of the biggest Oracle events that I know of with a great many attendees, but only four showed up to my live presentation. Yes, the feedback was very good, and surely those people who attended were very interested in the topic I was speaking about. I thank each and every one of them.

A few days later, I checked my presentation statistics and there were a few views and downloads. But overall, the number is far below the 20 to 50 people that attended this same presentation at other on-site conferences.

Of course, I’m not a renowned speaker and the subject matter might not be of broader interest. However, the online experience is just “not the same,” and that takes a toll on the number of attendees.

Regardless, it was a good overall experience. I give my sincere congratulations to the organizers for the huge effort they clearly put into the entire event.

Hopefully, we can all meet in person soon and enjoy a truly magnificent Collaborate.

Categories: DBA Blogs

Datascape Podcast Episode 41 – Choosing a Public Cloud for Oracle Database

Thu, 2020-04-23 00:00

You can run Oracle anywhere. It’s very flexible, which means anywhere you can get a VM, you can run it, including a virtual box on your laptop. There’s even have a free edition (XE) if you don’t have large data needs. In this episode, we’ll discuss why you would choose to host your Oracle Database anywhere other than its native cloud. We look at the feasibility of doing so, along with some of the benefits and drawbacks that you’ll likely encounter moving to a public cloud.

Simon Pane, a friend of the show and Principal Consultant at Pythian, is here to walk us through this topic. As usual, he provides excellent insights. We start by discussing Oracle’s cloud (OCI) and the reasons to stay with it, which include its flexibility and very good support. However, the platform is in its relative infancy which means that there will likely some problems.

We then move on to the big three: Azure, AWS, and Google Cloud. Moving to a public cloud provider might be part of an organizational “bigger picture” and other non-functional reasons. With Azure and AWS, clients are likely hoping to take advantage of the other features, such as Azure’s Office 365 and AWS’s RDS. Despite being licensed for Oracle, Azure is not optimized for it because there are no special shapes or options. AWS does have managed services, but still has drawbacks, which Simon explores in-depth.

We then move on to Google Cloud, the newest kid on the block. There are certainly great features (like BigQuery) that come along with it, but there is a major discrepancy: Licensing. The Oracle licensing policy does not include GC, so Simon offers some potential workarounds for this problem.

Ultimately, there is no slam-dunk solution for where to host Oracle databases. If you put your blinders on and focus on one area, it might seem that one cloud resolves all of your needs. However, it really comes down to making trade-offs and deciding which need is most important. Be sure to tune in today!

Key Points from this Episode
  • Discover reasons someone might move an Oracle on-premises database to a public cloud.
  • How Oracle-supported versions work and why this might be a reason for using public clouds.
  • Insights into Oracle cloud (OCI), its bad name, and the two reasons people don’t use it.
  • The motivators for utilizing OCI: It’s best at home, supportability, and unique needs.
  • Learn why you would use Azure as a home for your Oracle Database.
  • The pain points you might encounter hosting an Oracle Database on Azure.
  • Developments between Microsoft and Oracle. Two former enemies now building bridges.
  • Reasons to go with AWS: Brand recognition, first to market, and managed service.
  • Oracle licenses are incredibly expensive and drive the cost up in cloud and on-premises.
  • Discover some of the shortcomings of hosting an Oracle Database on AWS.
  • An overview of the special Oracle licensing considerations to remember.
  • The benefits and drawbacks of using Google Cloud for Oracle Databases.
  • Cloud SQL: Another example of Oracle’s terrible product names!
  • A universal cloud challenge: None of them are great with upgrades.
  • Why having cloud as backup or DR can be a good way to navigate the cloud decision matrix.
  • What public clouds have in store for Oracle going into the future.

Links Mentioned in Today’s Episode
Categories: DBA Blogs

101 Series of Oracle in Google Cloud – Part I : Building ASM and Database

Wed, 2020-04-08 10:57

About a year ago, I worked on a project with some amazing teammates (Simon Pane and Karun Dutt) to collaborate on a POC to move their Oracle environment into Google Cloud. This series reflects some of the things I learned while working on this project, and provides a guide for testing Oracle in Google Cloud.

Note: that I am not a licensing expert, so before you do this, make sure you consult with your Google/Oracle Sales representative to be clear about what’s allowed and the costs involved.

Because this is a “101” series, let’s start by logging into Google Cloud. By now you should already have an account in cloud.google.com and installed the Google Cloud SDK. Because I use a Mac, I downloaded it from this location. Then, I logged into my Google Cloud account:

Renes-MacBook-Pro-2:~ rene$ gcloud auth login **********@*****.com

Because I’m using Google Free Tier, there are limitations on disk sizes, instance type, quotas, etc. These are the specifications I used:

  • Google Cloud instance name: rene-ace-test-inst1
  • n1-standard-8 VM which has 8 vCPU and 30GB of memory
  • Centos 7
  • 1 150GB SSD disk for ASM, disk group name: DATA
  • 1 100GB HD disk for the boot disk
  • Default subnet network
  • No quotas
  • Oracle 19.3 for OHAS and RDBMS
  • Oracle DB instance name: DBTEST

First, create the disk to use for ASM. In this case, I used a 150GB SSD disk:

Renes-MacBook-Pro-2:~ rene$ gcloud compute disks create rene-ace-disk-asm1 \
>    --project=oracle-migration \
>    --type=pd-ssd \
>    --size=150GB \
>    --labels=item=rene-ace \
>    --zone=us-central1-c
Created [https://www.googleapis.com/compute/v1/projects/oracle-migration/zones/us-central1-c/disks/rene-ace-disk-asm1].
NAME                ZONE           SIZE_GB  TYPE    STATUS
rene-ace-disk-asm1  us-central1-c  150      pd-ssd  READY

Create a 100GB HD disk for the boot disk with Centos 7:

Renes-MacBook-Pro-2:~ rene$ gcloud compute disks create rene-ace-inst1-boot-disk \
> --project=oracle-migration \
> --type=pd-standard \
> --size=100GB \
> --zone=us-central1-c \
> --image=centos-7-v20200309 \
> --image-project=centos-cloud 
WARNING: You have selected a disk size of under [200GB]. This may result in poor I/O performance. For more information, see: https://developers.google.com/compute/docs/disks#performance.
Created [https://www.googleapis.com/compute/v1/projects/oracle-migration/zones/us-central1-c/disks/rene-ace-inst1-boot-disk].
WARNING: Some requests generated warnings:
 - Disk size: '100 GB' is larger than image size: '10 GB'. You might need to resize the root repartition manually if the operating system does not support automatic resizing. See https://cloud.google.com/compute/docs/disks/add-persistent-disk#resize_pd for details.

NAME                      ZONE           SIZE_GB  TYPE         STATUS
rene-ace-inst1-boot-disk  us-central1-c  100      pd-standard  READY

After creating the ASM disk, create the VM with the boot disk that you just created:

Renes-MacBook-Pro-2:~ rene$ gcloud compute instances create rene-ace-test-inst1 \
>    --project=oracle-migration \
>    --zone=us-central1-c \
>    --machine-type=n1-standard-8 \
>    --subnet=default \
>    --network-tier=PREMIUM \
>    --no-restart-on-failure \
>    --maintenance-policy=TERMINATE \
>    --no-service-account \
>    --no-scopes \
>    --disk=name=rene-ace-inst1-boot-disk,device-name=rene-ace-inst1-boot-disk,mode=rw,boot=yes,auto-delete=yes \
>    --tags=allow-ssh,egress-nat-gce \
>    --labels=item=rene-ace
Created [https://www.googleapis.com/compute/v1/projects/oracle-migration/zones/us-central1-c/instances/rene-ace-test-inst1].
NAME                 ZONE           MACHINE_TYPE   PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP   STATUS
rene-ace-test-inst1  us-central1-c  n1-standard-8               10.***.***.***   34.***.***.***  RUNNING

Attach the disk to use for ASM:

Renes-MacBook-Pro-2:~ rene$ gcloud compute instances attach-disk rene-ace-test-inst1 \
>    --disk=rene-ace-disk-asm1 \
>    --device-name=rene-ace-disk-asm1 \
>    --mode=rw \
>    --zone=us-central1-c
Updated [https://www.googleapis.com/compute/v1/projects/oracle-migration/zones/us-central1-c/instances/rene-ace-test-inst1].
Renes-MacBook-Pro-2:~ rene$ gcloud compute ssh rene-ace-test-inst1 --zone=us-central1-c
Updating project ssh metadata...?Updated [https://www.googleapis.com/compute/v1/projects/oracle-migration].                                                                            
Updating project ssh metadata...done.                                                                                                                                                  
Waiting for SSH key to propagate.
Warning: Permanently added 'compute.5519028484974783249' (ECDSA) to the list of known hosts.
/etc/profile.d/lang.sh: line 19: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory
-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory
[rene@rene-ace-test-inst1 ~]$ 

Unless otherwise mentioned, perform all steps as the root user. Now, install the 19c pre-install RPM:

[rene@rene-ace-test-inst1 ~]$ sudo su -
[root@rene-ace-test-inst1 ~]# curl -o ./oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm \
>    https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 18204  100 18204    0     0  55201      0 --:--:-- --:--:-- --:--:-- 55331
[root@rene-ace-test-inst1 ~]# yum -y localinstall ./oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
Loaded plugins: fastestmirror
Examining ./oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm: oracle-database-preinstall-19c-1.0-1.el7.x86_64
Marking ./oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-19c.x86_64 0:1.0-1.el7 will be installed
--> Processing Dependency: bc for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
Determining fastest mirrors
epel/x86_64/metalink                                                                                                                                             |  18 kB  00:00:00     
 * base: us.mirror.nsec.pt
 * epel: mirror.steadfastnet.com
 * extras: mirrors.gigenet.com
 * updates: bay.uchicago.edu
base                                                                                                                                                             | 3.6 kB  00:00:00     
epel                                                                                                                                                             | 4.7 kB  00:00:00     
extras                                                                                                                                                           | 2.9 kB  00:00:00     
google-cloud-sdk/signature                                                                                                                                       |  454 B  00:00:00     
google-cloud-sdk/signature                                                                                                                                       | 1.4 kB  00:00:00 !!! 
google-compute-engine/signature                                                                                                                                  |  454 B  00:00:00     
google-compute-engine/signature                                                                                                                                  | 1.4 kB  00:00:00 !!! 
updates                                                                                                                                                          | 2.9 kB  00:00:00     
(1/9): epel/x86_64/group_gz                                                                                                                                      |  95 kB  00:00:00     
(2/9): epel/x86_64/updateinfo                                                                                                                                    | 1.0 MB  00:00:00     
(3/9): base/7/x86_64/group_gz                                                                                                                                    | 165 kB  00:00:00     
(4/9): extras/7/x86_64/primary_db                                                                                                                                | 164 kB  00:00:00     
(5/9): epel/x86_64/primary_db                                                                                                                                    | 6.7 MB  00:00:00     
(6/9): base/7/x86_64/primary_db                                                                                                                                  | 6.0 MB  00:00:00     
(7/9): google-cloud-sdk/primary                                                                                                                                  | 168 kB  00:00:00     
(8/9): google-compute-engine/primary                                                                                                                             | 3.9 kB  00:00:00     
(9/9): updates/7/x86_64/primary_db                                                                                                                               | 7.6 MB  00:00:00     
google-cloud-sdk                                                                                                                                                              1224/1224
google-compute-engine                                                                                                                                                             11/11
--> Processing Dependency: bind-utils for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: compat-libcap1 for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: compat-libstdc++-33 for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: glibc-devel for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: ksh for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: libaio for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: libaio-devel for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: libstdc++-devel for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: nfs-utils for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: psmisc for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: smartmontools for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: sysstat for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: unzip for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: xorg-x11-utils for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: xorg-x11-xauth for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Running transaction check
---> Package bc.x86_64 0:1.06.95-13.el7 will be installed
---> Package bind-utils.x86_64 32:9.11.4-9.P2.el7 will be installed
--> Processing Dependency: bind-libs-lite(x86-64) = 32:9.11.4-9.P2.el7 for package: 32:bind-utils-9.11.4-9.P2.el7.x86_64
--> Processing Dependency: bind-libs(x86-64) = 32:9.11.4-9.P2.el7 for package: 32:bind-utils-9.11.4-9.P2.el7.x86_64
--> Processing Dependency: liblwres.so.160()(64bit) for package: 32:bind-utils-9.11.4-9.P2.el7.x86_64
--> Processing Dependency: libisccfg.so.160()(64bit) for package: 32:bind-utils-9.11.4-9.P2.el7.x86_64
--> Processing Dependency: libisc.so.169()(64bit) for package: 32:bind-utils-9.11.4-9.P2.el7.x86_64
--> Processing Dependency: libirs.so.160()(64bit) for package: 32:bind-utils-9.11.4-9.P2.el7.x86_64
--> Processing Dependency: libdns.so.1102()(64bit) for package: 32:bind-utils-9.11.4-9.P2.el7.x86_64
...
Installed:
  oracle-database-preinstall-19c.x86_64 0:1.0-1.el7                                                                                                                                     

Dependency Installed:
  GeoIP.x86_64 0:1.5.0-14.el7                  bc.x86_64 0:1.06.95-13.el7           bind-libs.x86_64 32:9.11.4-9.P2.el7                     bind-libs-lite.x86_64 32:9.11.4-9.P2.el7 
  bind-license.noarch 32:9.11.4-9.P2.el7       bind-utils.x86_64 32:9.11.4-9.P2.el7 compat-libcap1.x86_64 0:1.10-7.el7                      compat-libstdc++-33.x86_64 0:3.2.3-72.el7
  geoipupdate.x86_64 0:2.5.0-1.el7             glibc-devel.x86_64 0:2.17-292.el7    glibc-headers.x86_64 0:2.17-292.el7                     gssproxy.x86_64 0:0.7.0-26.el7           
  kernel-headers.x86_64 0:3.10.0-1062.18.1.el7 keyutils.x86_64 0:1.5.8-3.el7        ksh.x86_64 0:20120801-140.el7_7                         libICE.x86_64 0:1.0.9-9.el7              
  libSM.x86_64 0:1.2.2-2.el7                   libX11.x86_64 0:1.6.7-2.el7          libX11-common.noarch 0:1.6.7-2.el7                      libXau.x86_64 0:1.0.8-2.1.el7            
  libXext.x86_64 0:1.3.3-3.el7                 libXi.x86_64 0:1.7.9-1.el7           libXinerama.x86_64 0:1.1.3-2.1.el7                      libXmu.x86_64 0:1.1.2-2.el7              
  libXrandr.x86_64 0:1.5.1-2.el7               libXrender.x86_64 0:0.9.10-1.el7     libXt.x86_64 0:1.1.5-3.el7                              libXtst.x86_64 0:1.2.3-1.el7             
  libXv.x86_64 0:1.0.11-1.el7                  libXxf86dga.x86_64 0:1.1.4-2.1.el7   libXxf86misc.x86_64 0:1.0.3-7.1.el7                     libXxf86vm.x86_64 0:1.1.4-1.el7          
  libaio.x86_64 0:0.3.109-13.el7               libaio-devel.x86_64 0:0.3.109-13.el7 libbasicobjects.x86_64 0:0.1.1-32.el7                   libcollection.x86_64 0:0.7.0-32.el7      
  libdmx.x86_64 0:1.1.3-3.el7                  libevent.x86_64 0:2.0.21-4.el7       libini_config.x86_64 0:1.3.1-32.el7                     libnfsidmap.x86_64 0:0.25-19.el7         
  libpath_utils.x86_64 0:0.2.1-32.el7          libref_array.x86_64 0:0.1.5-32.el7   libstdc++-devel.x86_64 0:4.8.5-39.el7                   libtirpc.x86_64 0:0.2.4-0.16.el7         
  libverto-libevent.x86_64 0:0.2.5-4.el7       libxcb.x86_64 0:1.13-1.el7           lm_sensors-libs.x86_64 0:3.4.0-8.20160601gitf9185e5.el7 mailx.x86_64 0:12.5-19.el7               
  nfs-utils.x86_64 1:1.3.0-0.65.el7            psmisc.x86_64 0:22.20-16.el7         quota.x86_64 1:4.01-19.el7                              quota-nls.noarch 1:4.01-19.el7           
  rpcbind.x86_64 0:0.2.0-48.el7                smartmontools.x86_64 1:7.0-1.el7_7.1 sysstat.x86_64 0:10.1.5-18.el7_7.1                      tcp_wrappers.x86_64 0:7.6-77.el7         
  unzip.x86_64 0:6.0-20.el7                    xorg-x11-utils.x86_64 0:7.5-23.el7   xorg-x11-xauth.x86_64 1:1.0.9-1.el7                    

Complete!

You must install some additional packages and update what you have already installed:

[root@rene-ace-test-inst1 ~]# yum -y install wget java-11-openjdk gcc gcc-c++
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: us.mirror.nsec.pt
...
Installed:
  gcc.x86_64 0:4.8.5-39.el7              gcc-c++.x86_64 0:4.8.5-39.el7              java-11-openjdk.x86_64 1:11.0.6.10-1.el7_7              wget.x86_64 0:1.14-18.el7_6.1             

Dependency Installed:
  avahi-libs.x86_64 0:0.6.31-19.el7        copy-jdk-configs.noarch 0:3.3-10.el7_5               cpp.x86_64 0:4.8.5-39.el7                  cups-libs.x86_64 1:1.6.3-40.el7             
  dejavu-fonts-common.noarch 0:2.33-6.el7  dejavu-sans-fonts.noarch 0:2.33-6.el7                fontconfig.x86_64 0:2.13.0-4.3.el7         fontpackages-filesystem.noarch 0:1.44-8.el7 
  giflib.x86_64 0:4.1.6-9.el7              java-11-openjdk-headless.x86_64 1:11.0.6.10-1.el7_7  javapackages-tools.noarch 0:3.4.1-11.el7   libfontenc.x86_64 0:1.1.3-3.el7             
  libjpeg-turbo.x86_64 0:1.2.90-8.el7      libmpc.x86_64 0:1.0.1-3.el7                          libxslt.x86_64 0:1.1.28-5.el7              lksctp-tools.x86_64 0:1.0.17-2.el7          
  mpfr.x86_64 0:3.1.1-4.el7                pcsc-lite-libs.x86_64 0:1.8.8-8.el7                  python-javapackages.noarch 0:3.4.1-11.el7  python-lxml.x86_64 0:3.2.1-4.el7            
  ttmkfdir.x86_64 0:3.0.9-42.el7           tzdata-java.noarch 0:2019c-1.el7                     xorg-x11-font-utils.x86_64 1:7.5-21.el7    xorg-x11-fonts-Type1.noarch 0:7.5-9.el7     

Complete!
[root@rene-ace-test-inst1 ~]# yum -y update
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: us.mirror.nsec.pt
 * epel: mirror.steadfastnet.com
 * extras: mirrors.gigenet.com
 * updates: bay.uchicago.edu
Resolving Dependencies
...
Installed:
  kernel.x86_64 0:3.10.0-1062.18.1.el7                                                                                                                                                  

Updated:
  binutils.x86_64 0:2.27-41.base.el7_7.3      firewalld.noarch 0:0.6.3-2.el7_7.4               firewalld-filesystem.noarch 0:0.6.3-2.el7_7.4  google-cloud-sdk.noarch 0:286.0.0-1    
  kernel-tools.x86_64 0:3.10.0-1062.18.1.el7  kernel-tools-libs.x86_64 0:3.10.0-1062.18.1.el7  kmod.x86_64 0:20-25.el7_7.1                    kmod-libs.x86_64 0:20-25.el7_7.1       
  libicu.x86_64 0:50.2-4.el7_7                python-firewall.noarch 0:0.6.3-2.el7_7.4         python-perf.x86_64 0:3.10.0-1062.18.1.el7      python-requests.noarch 0:2.6.0-9.el7_7 
  rsyslog.x86_64 0:8.24.0-41.el7_7.4          systemd.x86_64 0:219-67.el7_7.4                  systemd-libs.x86_64 0:219-67.el7_7.4           systemd-sysv.x86_64 0:219-67.el7_7.4   

Complete!

[root@rene-ace-test-inst1 ~]# yum clean all
Loaded plugins: fastestmirror
Cleaning repos: base epel extras google-cloud-sdk google-compute-engine updates
Cleaning up list of fastest mirrors

Make sure that there is at least 16GB of swap space:

[root@rene-ace-test-inst1 ~]# dd if=/dev/zero of=/swapfile bs=1M count=16384
16384+0 records in
16384+0 records out
17179869184 bytes (17 GB) copied, 80.5556 s, 213 MB/s
[root@rene-ace-test-inst1 ~]# mkswap /swapfile
Setting up swapspace version 1, size = 16777212 KiB
no label, UUID=50136cfb-9ab7-4d5a-8ed7-726da89a7ede
[root@rene-ace-test-inst1 ~]# chmod 0600 /swapfile
[root@rene-ace-test-inst1 ~]# swapon /swapfile
[root@rene-ace-test-inst1 ~]# echo "/swapfile          swap            swap    defaults        0 0" >> /etc/fstab
[root@rene-ace-test-inst1 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
[root@rene-ace-test-inst1 ~]# echo "transparent_hugepage=never" >> /etc/default/grub
[root@rene-ace-test-inst1 ~]# grub2-mkconfig -o /boot/grub2/grub.cfg
Generating grub configuration file ...
Found linux image: /boot/vmlinuz-3.10.0-1062.18.1.el7.x86_64
Found initrd image: /boot/initramfs-3.10.0-1062.18.1.el7.x86_64.img
Found linux image: /boot/vmlinuz-3.10.0-1062.12.1.el7.x86_64
Found initrd image: /boot/initramfs-3.10.0-1062.12.1.el7.x86_64.img
Found linux image: /boot/vmlinuz-3.10.0-1062.el7.x86_64
Found initrd image: /boot/initramfs-3.10.0-1062.el7.x86_64.img
Found linux image: /boot/vmlinuz-0-rescue-2a464dc72a01028f0cd989f1d687b484
Found initrd image: /boot/initramfs-0-rescue-2a464dc72a01028f0cd989f1d687b484.img
done

Disable the firewall and change SELINUX to permissive:

[root@rene-ace-test-inst1 ~]# systemctl stop firewalld
[root@rene-ace-test-inst1 ~]# systemctl disable firewalld
[root@rene-ace-test-inst1 ~]# sed -i.bak '/^SELINUX=/ s/SELINUX=.*/SELINUX=permissive/' /etc/selinux/config^C
[root@rene-ace-test-inst1 ~]# cat /etc/selinux/config | grep SELINUX
# SELINUX= can take one of these three values:
SELINUX=permissive
# SELINUXTYPE= can take one of three values:
SELINUXTYPE=targeted 

Next, enable hugepages by changing the value in /etc/sysctl.conf and restarting the VM:

[root@rene-ace-test-inst1 ~]# echo "vm.nr_hugepages=4096" >> /etc/sysctl.conf
[root@rene-ace-test-inst1 ~]# shutdown -r now
Connection to 34.66.112.87 closed by remote host.
Connection to 34.66.112.87 closed.
ERROR: (gcloud.compute.ssh) [/usr/bin/ssh] exited with return code [255].
Renes-MacBook-Pro-2:~ rene$ gcloud compute ssh rene-ace-test-inst1 --zone=us-central1-c
Last login: Tue Mar 31 01:31:10 2020 from 189.216.207.2
-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory
[rene@rene-ace-test-inst1 ~]$ sudo su -
Last login: Tue Mar 31 01:31:21 UTC 2020 on pts/0
[root@rene-ace-test-inst1 ~]# grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:    4096
HugePages_Free:     4096
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

Create the directories for the binaries, the additional OS groups, and update the bash profile:

[root@rene-ace-test-inst1 ~]# mkdir -p /u01/app/19.0.0.0/grid
[root@rene-ace-test-inst1 ~]# mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1
[root@rene-ace-test-inst1 ~]# mkdir -p /u01/app/oraInventory
[root@rene-ace-test-inst1 ~]# mkdir -p /u01/app/oracle/fast_recovery_area
[root@rene-ace-test-inst1 ~]# mkdir -p /u01/app/oracle_software
[root@rene-ace-test-inst1 ~]# mkdir -p /home/oracle/working
[root@rene-ace-test-inst1 ~]# chown -R oracle:oinstall /u01
[root@rene-ace-test-inst1 ~]# chown -R oracle:oinstall /home/oracle/working
[root@rene-ace-test-inst1 ~]# chmod -R 775 /u01

[root@rene-ace-test-inst1 ~]# 
[root@rene-ace-test-inst1 ~]# 
[root@rene-ace-test-inst1 ~]# cat <<EOT >> /home/oracle/.bash_profile
>     
> #  Oracle RDBMS Settings:
> export ORACLE_SID=ORCL
> export ORACLE_BASE=/u01/app/oracle
> export ORACLE_HOME=\${ORACLE_BASE}/product/19.0.0.0/dbhome_1
> export GRID_HOME=/u01/app/19.0.0.0/grid
> export PATH=\${ORACLE_HOME}/bin:\${PATH}
> export SWLIB=/u01/app/oracle_software
> EOT
[root@rene-ace-test-inst1 ~]# groupadd -g 54327 asmdba
[root@rene-ace-test-inst1 ~]# groupadd -g 54328 asmoper
[root@rene-ace-test-inst1 ~]# groupadd -g 54329 asmadmin
[root@rene-ace-test-inst1 ~]# usermod -u 54321 -g oinstall -G dba,asmadmin,asmdba,asmoper oracle
[root@rene-ace-test-inst1 ~]# 
[root@rene-ace-test-inst1 ~]# echo "umask 022" >> /home/oracle/.bashrc

Log in as the oracle use and download the latest OPatch using Maris Elsins getMOSpatch:

[rene@rene-ace-test-inst1 ~]$ sudo su - oracle
Last login: Tue Mar 31 01:09:04 UTC 2020 on pts/0
[oracle@rene-ace-test-inst1 ~]$ cd ${SWLIB}
[oracle@rene-ace-test-inst1 oracle_software]$ wget https://github.com/MarisElsins/getMOSPatch/raw/master/getMOSPatch.jar

...
(raw.githubusercontent.com)|151.101.0.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 8932 (8.7K) [application/octet-stream]
Saving to: ‘getMOSPatch.jar’

100%[==============================================================================================================================================>] 8,932       --.-K/s   in 0s      

2020-03-31 01:46:46 (80.6 MB/s) - ‘getMOSPatch.jar’ saved [8932/8932]

[oracle@rene-ace-test-inst1 oracle_software]$ java -jar getMOSPatch.jar patch=6880880 platform=226P regexp=.*190.* download=all
Enter your MOS username: ********@*********.com
Enter your MOS password: 
|
Enter Comma separated platforms to list: 226P

We're going to download patches for the following Platforms/Languages:
 226P - Linux x86-64

Processing patch 6880880 for Linux x86-64 and applying regexp .*190.* to the filenames:
 1 - p6880880_190000_Linux-x86-64.zip
 Enter Comma separated files to download: all
 All files will be downloadad because download=all was specified.

Downloading all selected files:
 Downloading p6880880_190000_Linux-x86-64.zip: 110MB at average speed of 16744KB/s - DONE!  

Download Oracle base software using wget from edelivery.oracle.com. Download files V982063-01.zip (ORACLE RDBMS) and V982068-01.zip (ORACLE GI). You should have the following files:

[oracle@rene-ace-test-inst1 oracle_software]$ ls -ltr
total 5931508
-rw-r--r--. 1 oracle oinstall 3059705302 Apr 23  2019 V982063-01.zip
-rw-r--r--. 1 oracle oinstall 2889184573 Apr 23  2019 V982068-01.zip
-rw-r--r--. 1 oracle oinstall  115653541 Mar 31 01:49 p6880880_190000_Linux-x86-64.zip

Unzip the files to the corresponding location and update the OPatch:

[oracle@rene-ace-test-inst1 oracle_software]$ unzip -q ${SWLIB}/V982063-01.zip -d ${ORACLE_HOME}
[oracle@rene-ace-test-inst1 oracle_software]$ unzip -q ${SWLIB}/V982068-01.zip -d ${GRID_HOME}
[oracle@rene-ace-test-inst1 oracle_software]$ rm -rf ${ORACLE_HOME}/OPatch
[oracle@rene-ace-test-inst1 oracle_software]$ rm -rf ${GRID_HOME}/OPatch
[oracle@rene-ace-test-inst1 oracle_software]$ unzip -q ${SWLIB}/p6880880_190000_Linux-x86-64.zip -d ${ORACLE_HOME}
[oracle@rene-ace-test-inst1 oracle_software]$ unzip -q ${SWLIB}/p6880880_190000_Linux-x86-64.zip -d ${GRID_HOME}

As the root user, install the cvuqdisk:

[rene@rene-ace-test-inst1 ~]$ sudo su -
Last login: Tue Mar 31 01:35:45 UTC 2020 on pts/0
[root@rene-ace-test-inst1 ~]# cd /u01/app/19.0.0.0/grid/cv/rpm
[root@rene-ace-test-inst1 rpm]# rpm -qi cvuqdisk
package cvuqdisk is not installed
[root@rene-ace-test-inst1 rpm]# CVUQDISK_GRP=oinstall; export CVUQDISK_GRP
[root@rene-ace-test-inst1 rpm]# rpm -iv cvuqdisk-*.rpm
Preparing packages...
cvuqdisk-1.0.10-1.x86_64

As the oracle user, run cluvfy pre crsinst to make sure there is nothing missing so that you can successfully install OHAS and ASM. Because this is not a RAC environment, you only need to use ASM.

[oracle@rene-ace-test-inst1 ~]$ cd ${GRID_HOME}
[oracle@rene-ace-test-inst1 grid]$ ./runcluvfy.sh stage -pre crsinst -n `hostname -s`

ERROR: 
PRVG-10467 : The default Oracle Inventory group could not be determined.

Verifying Physical Memory ...PASSED
Verifying Available Physical Memory ...PASSED
Verifying Swap Size ...PASSED
Verifying Free Space: rene-ace-test-inst1:/usr,rene-ace-test-inst1:/var,rene-ace-test-inst1:/etc,rene-ace-test-inst1:/sbin,rene-ace-test-inst1:/tmp ...PASSED
Verifying User Existence: oracle ...
  Verifying Users With Same UID: 54321 ...PASSED
Verifying User Existence: oracle ...PASSED
...
CVU operation performed:      stage -pre crsinst
Date:                         Apr 4, 2020 2:09:38 PM
CVU home:                     /u01/app/19.0.0.0/grid/
User:                         oracle

Modify the response file so that you can use the GI software as a CRS_SWONLY installation:

[oracle@rene-ace-test-inst1 grid]$ cp ${GRID_HOME}/inventory/response/grid_install.rsp ${SWLIB}/grid_install.rsp
[oracle@rene-ace-test-inst1 grid]$   
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.option/ s~oracle.install.option=$~oracle.install.option=CRS_SWONLY~' ${SWLIB}/grid_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^ORACLE_HOSTNAME/ s~ORACLE_HOSTNAME=$~ORACLE_HOSTNAME=`hostname -A`~' ${SWLIB}/grid_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^INVENTORY_LOCATION/ s~INVENTORY_LOCATION=$~INVENTORY_LOCATION=/u01/app/oraInventory~' ${SWLIB}/grid_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^ORACLE_BASE/ s~ORACLE_BASE=$~ORACLE_BASE='${ORACLE_BASE}'~' ${SWLIB}/grid_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.asm.OSDBA/ s~oracle.install.asm.OSDBA=$~oracle.install.asm.OSDBA=asmdba~' ${SWLIB}/grid_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.asm.OSOPER/ s~oracle.install.asm.OSOPER=$~oracle.install.asm.OSOPER=asmoper~' ${SWLIB}/grid_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.asm.OSASM/ s~oracle.install.asm.OSASM=$~oracle.install.asm.OSASM=asmadmin~' ${SWLIB}/grid_install.rsp
[oracle@rene-ace-test-inst1 grid]$   
[oracle@rene-ace-test-inst1 grid]$ diff ${GRID_HOME}/inventory/response/grid_install.rsp ${SWLIB}/grid_install.rsp
60c60
< INVENTORY_LOCATION=
---
> INVENTORY_LOCATION=/u01/app/oraInventory
75c75
< oracle.install.option=
---
> oracle.install.option=CRS_SWONLY
80c80
< ORACLE_BASE=
---
> ORACLE_BASE=/u01/app/oracle
98c98
< oracle.install.asm.OSDBA=
---
> oracle.install.asm.OSDBA=asmdba
105c105
< oracle.install.asm.OSOPER=
---
> oracle.install.asm.OSOPER=asmoper
111c111
< oracle.install.asm.OSASM=
---
> oracle.install.asm.OSASM=asmadmin

Launch the software installation:

[oracle@rene-ace-test-inst1 grid]$ ${GRID_HOME}/gridSetup.sh -silent -responseFile ${SWLIB}/grid_install.rsp
Launching Oracle Grid Infrastructure Setup Wizard...

The response file for this session can be found at:
 /u01/app/19.0.0.0/grid/install/response/grid_2020-04-04_02-12-20PM.rsp

You can find the log of this install session at:
 /tmp/GridSetupActions2020-04-04_02-12-20PM/gridSetupActions2020-04-04_02-12-20PM.log

As a root user, execute the following script(s):
	1. /u01/app/oraInventory/orainstRoot.sh
	2. /u01/app/19.0.0.0/grid/root.sh

Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes: 
[rene-ace-test-inst1]
Execute /u01/app/19.0.0.0/grid/root.sh on the following nodes: 
[rene-ace-test-inst1]

Successfully Setup Software.
Moved the install session logs to:
 /u01/app/oraInventory/logs/GridSetupActions2020-04-04_02-12-20PM

When the software installation is complete, run orainstRoot.sh and root.sh as root user. Also, run roothas.pl to setup the HAS stack:

[rene@rene-ace-test-inst1 ~]$ sudo su -
Last login: Sat Apr  4 14:06:06 UTC 2020 on pts/0
[root@rene-ace-test-inst1 ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@rene-ace-test-inst1 ~]# /u01/app/19.0.0.0/grid/root.sh
Check /u01/app/19.0.0.0/grid/install/root_rene-ace-test-inst1_2020-04-04_14-15-37-955415144.log for the output of root script

[root@rene-ace-test-inst1 ~]# export GRID_HOME=/u01/app/19.0.0.0/grid
[root@rene-ace-test-inst1 ~]# ${GRID_HOME}/perl/bin/perl -I ${GRID_HOME}/perl/lib -I ${GRID_HOME}/crs/install ${GRID_HOME}/crs/install/roothas.pl
Using configuration parameter file: /u01/app/19.0.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/oracle/crsdata/rene-ace-test-inst1/crsconfig/roothas_2020-04-04_02-16-37PM.log
LOCAL ADD MODE 
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE 
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node rene-ace-test-inst1 successfully pinned.
2020/04/04 14:16:47 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'

rene-ace-test-inst1     2020/04/04 14:17:24     /u01/app/oracle/crsdata/rene-ace-test-inst1/olr/backup_20200404_141724.olr     724960844     
2020/04/04 14:17:24 CLSRSC-327: Successfully configured Oracle Restart for a standalone server
[root@rene-ace-test-inst1 ~]# sleep 10

[root@rene-ace-test-inst1 ~]# ${GRID_HOME}/bin/crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ons
               OFFLINE OFFLINE      rene-ace-test-inst1      STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       rene-ace-test-inst1      STABLE
--------------------------------------------------------------------------------

As the root user, prepare the disks for ASM using UDEV rules:

[root@rene-ace-test-inst1 ~]# if [ ! -e /dev/sdb1 ]; then
>   echo -e "n\np\n1\n\n\nw" | fdisk /dev/sdb
> fi
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xae81dcbe.

The device presents a logical sector size that is smaller than
the physical sector size. Aligning to a physical sector (or optimal
I/O) size boundary is recommended, or performance may be impacted.

Command (m for help): Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): Partition number (1-4, default 1): First sector (2048-314572799, default 2048): Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-314572799, default 314572799): Using default value 314572799
Partition 1 of type Linux and of size 150 GiB is set

Command (m for help): The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@rene-ace-test-inst1 ~]#  
[root@rene-ace-test-inst1 ~]# ASM_DISK1=`/usr/lib/udev/scsi_id -g -u -d /dev/sdb`
[root@rene-ace-test-inst1 ~]#  
[root@rene-ace-test-inst1 ~]# cat > /etc/udev/rules.d/99-oracle-asmdevices.rules <<EOF
> KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/\$parent", RESULT=="${ASM_DISK1}", SYMLINK+="oracleasm/asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
> EOF
[root@rene-ace-test-inst1 ~]# cat /etc/udev/rules.d/99-oracle-asmdevices.rules
KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="0Google_PersistentDisk_rene-ace-disk-asm1", SYMLINK+="oracleasm/asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
[root@rene-ace-test-inst1 ~]# /sbin/partprobe /dev/sdb1
[root@rene-ace-test-inst1 ~]# sleep 10

[root@rene-ace-test-inst1 ~]# /sbin/udevadm control --reload-rules
[root@rene-ace-test-inst1 ~]# sleep 10
[root@rene-ace-test-inst1 ~]# /sbin/partprobe /dev/sdb1
[root@rene-ace-test-inst1 ~]# sleep 10
[root@rene-ace-test-inst1 ~]# /sbin/udevadm control --reload-rules
[root@rene-ace-test-inst1 ~]# sleep 10
[root@rene-ace-test-inst1 ~]# ls -al /dev/oracleasm/*
lrwxrwxrwx. 1 root root 7 Apr  4 14:20 /dev/oracleasm/asm-disk1 -> ../sdb1

As the oracle user, prepare the RDBMS install software response file:

[oracle@rene-ace-test-inst1 grid]$ cp ${ORACLE_HOME}/install/response/db_install.rsp ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$   
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.option/ s~oracle.install.option=$~oracle.install.option=INSTALL_DB_SWONLY~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.InstallEdition/ s~oracle.install.db.InstallEdition=$~oracle.install.db.InstallEdition=EE~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^ORACLE_HOSTNAME/ s~ORACLE_HOSTNAME=$~ORACLE_HOSTNAME=`hostname -A`~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^UNIX_GROUP_NAME/ s~UNIX_GROUP_NAME=$~UNIX_GROUP_NAME=oinstall~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^INVENTORY_LOCATION/ s~INVENTORY_LOCATION=$~INVENTORY_LOCATION=/u01/app/oraInventory~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^ORACLE_HOME/ s~ORACLE_HOME=$~ORACLE_HOME='${ORACLE_HOME}'~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^ORACLE_BASE/ s~ORACLE_BASE=$~ORACLE_BASE='${ORACLE_BASE}'~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.DBA_GROUP/ s~oracle.install.db.DBA_GROUP=$~oracle.install.db.DBA_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.OPER_GROUP/ s~oracle.install.db.OPER_GROUP=$~oracle.install.db.OPER_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.BACKUPDBA_GROUP/ s~oracle.install.db.BACKUPDBA_GROUP=$~oracle.install.db.BACKUPDBA_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.DGDBA_GROUP/ s~oracle.install.db.DGDBA_GROUP=$~oracle.install.db.DGDBA_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.KMDBA_GROUP/ s~oracle.install.db.KMDBA_GROUP=$~oracle.install.db.KMDBA_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.OSDBA_GROUP/ s~oracle.install.db.OSDBA_GROUP=$~oracle.install.db.OSDBA_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.OSOPER_GROUP/ s~oracle.install.db.OSOPER_GROUP=$~oracle.install.db.OSOPER_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.OSBACKUPDBA_GROUP/ s~oracle.install.db.OSBACKUPDBA_GROUP=$~oracle.install.db.OSBACKUPDBA_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.OSDGDBA_GROUP/ s~oracle.install.db.OSDGDBA_GROUP=$~oracle.install.db.OSDGDBA_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.OSKMDBA_GROUP/ s~oracle.install.db.OSKMDBA_GROUP=$~oracle.install.db.OSKMDBA_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^oracle.install.db.OSRACDBA_GROUP/ s~oracle.install.db.OSRACDBA_GROUP=$~oracle.install.db.OSRACDBA_GROUP=dba~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^SECURITY_UPDATES_VIA_MYORACLESUPPORT/ s~SECURITY_UPDATES_VIA_MYORACLESUPPORT$=~SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$ sed -i '/^DECLINE_SECURITY_UPDATES/ s~DECLINE_SECURITY_UPDATES=$~DECLINE_SECURITY_UPDATES=TRUE~' ${SWLIB}/db_install.rsp
[oracle@rene-ace-test-inst1 grid]$   
[oracle@rene-ace-test-inst1 grid]$ diff ${ORACLE_HOME}/install/response/db_install.rsp ${SWLIB}/db_install.rsp
29c29
< oracle.install.option=
---
> oracle.install.option=INSTALL_DB_SWONLY
34c34
< UNIX_GROUP_NAME=
---
> UNIX_GROUP_NAME=oinstall
41c41
< INVENTORY_LOCATION=
---
> INVENTORY_LOCATION=/u01/app/oraInventory
45c45
< ORACLE_HOME=
---
> ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
50c50
< ORACLE_BASE=
---
> ORACLE_BASE=/u01/app/oracle
62c62
< oracle.install.db.InstallEdition=
---
> oracle.install.db.InstallEdition=EE
79c79
< oracle.install.db.OSDBA_GROUP=
---
> oracle.install.db.OSDBA_GROUP=dba
85c85
< oracle.install.db.OSOPER_GROUP=
---
> oracle.install.db.OSOPER_GROUP=dba
90c90
< oracle.install.db.OSBACKUPDBA_GROUP=
---
> oracle.install.db.OSBACKUPDBA_GROUP=dba
95c95
< oracle.install.db.OSDGDBA_GROUP=
---
> oracle.install.db.OSDGDBA_GROUP=dba
100c100
< oracle.install.db.OSKMDBA_GROUP=
---
> oracle.install.db.OSKMDBA_GROUP=dba
105c105
< oracle.install.db.OSRACDBA_GROUP=
---
> oracle.install.db.OSRACDBA_GROUP=dba

Next, install the RDBMS software:

[oracle@rene-ace-test-inst1 ~]$ ${ORACLE_HOME}/runInstaller -silent -waitforcompletion -responseFile ${SWLIB}/db_install.rsp
Launching Oracle Database Setup Wizard...

The response file for this session can be found at:
 /u01/app/oracle/product/19.0.0.0/dbhome_1/install/response/db_2020-04-04_02-22-21PM.rsp

You can find the log of this install session at:
 /u01/app/oraInventory/logs/InstallActions2020-04-04_02-22-21PM/installActions2020-04-04_02-22-21PM.log

As a root user, execute the following script(s):
	1. /u01/app/oracle/product/19.0.0.0/dbhome_1/root.sh

Execute /u01/app/oracle/product/19.0.0.0/dbhome_1/root.sh on the following nodes: 
[rene-ace-test-inst1]

Successfully Setup Software.

As the root user, run RDBMS root.sh:

[root@rene-ace-test-inst1 ~]#  /u01/app/oracle/product/19.0.0.0/dbhome_1/root.sh
Check /u01/app/oracle/product/19.0.0.0/dbhome_1/install/root_rene-ace-test-inst1_2020-04-04_20-07-58-636555961.log for the output of root script

After the RDBMS software installation completes, setup the listener as the oracle user. Note that from this point forward, all tasks are performed as the oracle user:

[oracle@rene-ace-test-inst1 ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-APR-2020 14:39:21

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/rene-ace-test-inst1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rene-ace-test-inst1.us-central1-c.c.oracle-migration.internal)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                04-APR-2020 14:39:22
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/rene-ace-test-inst1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rene-ace-test-inst1.us-central1-c.c.oracle-migration.internal)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@rene-ace-test-inst1 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-APR-2020 14:39:29

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@rene-ace-test-inst1 ~]$  
[oracle@rene-ace-test-inst1 ~]$ srvctl add listener
[oracle@rene-ace-test-inst1 ~]$ srvctl start listener

Create and start the ASM Instance:

[oracle@rene-ace-test-inst1 ~]$ export ORACLE_SID=+ASM
[oracle@rene-ace-test-inst1 ~]$  
[oracle@rene-ace-test-inst1 ~]$ cat <<EOT > ${GRID_HOME}/dbs/init+ASM.ora
> instance_type=ASM
> asm_diskstring='/dev/oracleasm/*'
> large_pool_size=12M
> remote_login_passwordfile='EXCLUSIVE'
> memory_target=0
> sga_target=3G
> pga_aggregate_target=400M
> processes=1024
> EOT
[oracle@rene-ace-test-inst1 ~]$   
[oracle@rene-ace-test-inst1 ~]$ srvctl add asm -d '/dev/oracleasm/*'
[oracle@rene-ace-test-inst1 ~]$ srvctl start asm
[oracle@rene-ace-test-inst1 ~]$  
[oracle@rene-ace-test-inst1 ~]$ export ORAENV_ASK=NO
[oracle@rene-ace-test-inst1 ~]$ . oraenv
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rene-ace-test-inst1 ~]$   
[oracle@rene-ace-test-inst1 ~]$ echo "CREATE spfile FROM pfile;" | sqlplus -s / as sysasm

File created.

[oracle@rene-ace-test-inst1 ~]$ srvctl stop asm
[oracle@rene-ace-test-inst1 ~]$ srvctl start asm

Create the ASM diskgroup (DATA):

[oracle@rene-ace-test-inst1 ~]$ echo "
> SELECT header_status,path FROM v\$asm_disk;
>   
> CREATE DISKGROUP DATA EXTERNAL REDUNDANCY
>    DISK '/dev/oracleasm/asm-disk1'
>    ATTRIBUTE
>       'compatible.asm'   = '19.0.0.0.0',
>       'compatible.rdbms' = '19.0.0.0.0';
>   
> SELECT header_status,path FROM v\$asm_disk;
> " | sqlplus -s / as sysasm

HEADER_STATU
------------
PATH
--------------------------------------------------------------------------------
CANDIDATE
/dev/oracleasm/asm-disk1

Diskgroup created.

HEADER_STATU
------------
PATH
--------------------------------------------------------------------------------
MEMBER
/dev/oracleasm/asm-disk1

Create the database (DBTEST):

[oracle@rene-ace-test-inst1 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/DBTEST
[oracle@rene-ace-test-inst1 ~]$ 
[oracle@rene-ace-test-inst1 ~]$ export ORACLE_SID=DBTEST
[oracle@rene-ace-test-inst1 ~]$ dbca -silent \
>    -createDatabase \
>    -templateName General_Purpose.dbc \
>    -gdbName ${ORACLE_SID} \
>    -sid ${ORACLE_SID} \
>    -createAsContainerDatabase FALSE \
>    -sysPassword welcome1 \
>    -systemPassword welcome1 \
>    -emConfiguration NONE \
>    -datafileDestination '+DATA' \
>    -recoveryAreaDestination '/u01/app/oracle/fast_recovery_area' \
>    -redoLogFileSize 128 \
>    -storageType ASM \
>    -diskGroupName DATA \
>    -characterSet AL32UTF8 \
>    -nationalCharacterSet AL16UTF16 \
>    -registerWithDirService FALSE \
>    -automaticMemoryManagement FALSE \
>    -initparams \
>    db_create_file_dest='+DATA', \
>    db_create_online_log_dest_1='+DATA', \
>    filesystemio_options='SETALL', \
>    log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST', \
>    log_archive_format='DBTEST_%s%t%r.arc', \
>    pga_aggregate_target=200, \
>    processes=150, \
>    sga_target=1200, \
>    streams_pool_size=64M, \
>    undo_retention=7200
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
10% complete
Registering database with Oracle Restart
14% complete
Copying database files
43% complete
Creating and starting Oracle instance
45% complete
49% complete
53% complete
56% complete
62% complete
Completing Database Creation
68% complete
70% complete
71% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/DBTEST.
Database Information:
Global Database Name:DBTEST
System Identifier(SID):DBTEST
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/DBTEST/DBTEST.log" for further details.

Adjust redo log settings to 128MB:

[oracle@rene-ace-test-inst1 ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 4 15:04:31 2020
Version 19.3.0.0.0

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name from v$database;

NAME
---------
DBTEST

SQL> SET serverout on
   
DECLARE
   file_bytes           NUMBER;
   max_group            INT;
   num_groups           INT;
   num_threads          INT;
   created              INT;
   str                  VARCHAR2(128);
BEGIN
   --SELECT MAX(bytes) INTO file_bytes FROM v$log;
   SELECT 128*1024*1024 INTO file_bytes FROM dual;
   SELECT COUNT(DISTINCT(thread#)) INTO num_threads FROM v$log;
   SELECT MAX(group#), COUNT(DISTINCT(group#)) INTO max_group, num_groups FROM v$logfile WHERE type = 'ONLINE';
   SELECT COUNT(*) INTO created FROM v$standby_log;
   IF (created = 0) THEN
      FOR i IN 1..num_threads LOOP
         --FOR j IN i..max_group+i LOOP
         FOR j IN 1..5 LOOP
            str := 'ALTER DATABASE ADD LOGFILE THREAD '||i||' GROUP '||TO_CHAR(j+num_groups*i)||' SIZE '||file_bytes;
            DBMS_OUTPUT.put_line(str);
            EXECUTE IMMEDIATE str;
         END LOOP;
      END LOOP;
   END IF;
END;
/SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25  
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 4 SIZE 134217728
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 SIZE 134217728
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 SIZE 134217728
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 7 SIZE 134217728
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 8 SIZE 134217728

PL/SQL procedure successfully completed.

Adjust the database to use large pages only, minimize SGA allocations, and adjust file system IO options:

[oracle@rene-ace-test-inst1 ~]$ echo "
>    alter system set USE_LARGE_PAGES=ONLY scope=spfile;
>    alter system set FILESYSTEMIO_OPTIONS=SETALL scope=spfile;
>    alter system set SGA_TARGET=650M scope=spfile;
>    alter system set SGA_MAX_SIZE=650M scope=spfile;
>    alter system set SHARED_POOL_SIZE=256M scope=spfile;
>    alter system set PGA_AGGREGATE_TARGET=50M scope=spfile;
>    alter system set DB_CACHE_SIZE=64M scope=spfile;
>    alter system set PARALLEL_MAX_SERVERS=0 scope=spfile;
>    alter system set RECYCLEBIN=OFF scope=spfile;
> " | sqlplus -s / as sysdba

System altered.


System altered.


System altered.


System altered.


System altered.


System altered.


System altered.


System altered.


System altered.

Stop and restart the database so that the new parameters take effect. Use crs_status.sh to verify the status of the OHAS environment:

[oracle@rene-ace-test-inst1 ~]$ srvctl stop database -d ${ORACLE_SID}
[oracle@rene-ace-test-inst1 ~]$ srvctl start database -d ${ORACLE_SID}

[oracle@rene-ace-test-inst1 ~]$ wget https://raw.githubusercontent.com/rene-ace/oracle_bash_scripts/master/crs_status.sh --output-document=${HOME}/working/crs_status.sh
...
(raw.githubusercontent.com)|151.101.0.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1320 (1.3K) [text/plain]
Saving to: ‘/home/oracle/working/crs_status.sh’

100%[==============================================================================================================================================>] 1,320       --.-K/s   in 0s      

2020-04-04 20:44:09 (224 MB/s) - ‘/home/oracle/working/crs_status.sh’ saved [1320/1320]

[oracle@rene-ace-test-inst1 working]$ chmod 750 ${HOME}/working/crs_status.sh
[oracle@rene-ace-test-inst1 working]$ sed -i '/^CRS_HOME/ s~CRS_HOME=$~CRS_HOME='${GRID_HOME}'~' ${HOME}/working/crs_status.sh
[oracle@rene-ace-test-inst1 working]$ ${HOME}/working/crs_status.sh
Name                                          Target     State           Server                    State details
------------------------------------------------------------------------------------------------------------------------
ora.DATA.dg                                   ONLINE     ONLINE          rene-ace-test-inst1       STABLE 
ora.LISTENER.lsnr                             ONLINE     ONLINE          rene-ace-test-inst1       STABLE 
ora.asm                                       ONLINE     ONLINE          rene-ace-test-inst1       Started,STABLE 
ora.ons                                       OFFLINE    OFFLINE         rene-ace-test-inst1       STABLE 
ora.cssd                                      ONLINE     ONLINE          rene-ace-test-inst1       STABLE 
ora.dbtest.db                                 ONLINE     ONLINE          rene-ace-test-inst1       Open,HOME=/u01/app/o 
ora.diskmon                                   OFFLINE    OFFLINE         STABLE                     
ora.evmd                                      ONLINE     ONLINE          rene-ace-test-inst1       STABLE 

Now that you have created the Google VM, installed OHAS, and created the Oracle DB, we’re set to perform several tasks that I’ll be describing in future series posts. I hope this helps expand your understanding of Google Cloud and how to install a database in it.

Note: This was originally published at rene-ace.

Categories: DBA Blogs

World Backup Day – Protect Your Oracle Databases

Tue, 2020-03-31 08:00
In recognition of World Backup Day, I will discuss backups as they pertain to Oracle databases.

There are a number of options for backing up, testing backups, and restoring them. In fact, the number of possibilities can be a little overwhelming. Backing up does not necessarily need to be too complex. It is best to start off simple, and then modify the backup procedures as needed.

This article is not a tutorial by any means, but just an overview of some of the methods for backing up your database, and testing those backups. For some readers, this may be a refresher, and for those new to Oracle, it is a brief introduction to some features of the Oracle RMAN utility.

Making a backup can be as simple as the RMAN command backup database. Most backups, however, are more complex than that. Rather than spend much time on backups though, I actually want to talk about testing backups.

Backups that do not work when a restore and recovery attempt is made are the things that nightmares are made of. Making sure the backups are actually useful is something that cannot be overstated.

My interest in backups

First a little background on why I tend to be rather particular about making backups and being certain that a backup can be used to successfully restore a database. The two incidents below are from long ago; pre-Pythian and pre-Oracle (at least for me). The technologies used will make this obvious.

Inventory System

Long ago I was responsible for an inventory system. The fact that this inventory software ran on 8-inch floppy disks will provide clues to a couple of things:

  • it was long ago
  • it could not have been a large inventory (thankfully, as you will see)

One day something happened to the inventory disk. I do not recall just what happened, but I do remember that the entire inventory had to be re-entered.

By hand.

By me.

You would think I would have learned my lesson and start making regular backups. But no. If you have never made backups on 8-inch floppy disks, you do not really understand the meaning of the word ‘tedium’.

But of course, there was another crash, the disk was trash, and I had to re-enter all of the inventory.

Again.

By hand.

That was powerful lesson. But wait, there’s more!

The Orders System

At a somewhat later stage, I was at a different job, working onsite at a client site where they ran our custom software. My job that day was to upgrade the version of Xenix (Google it!) on the client’s machine. And of course, before doing that I made a backup. This backup was to tape, and so fairly simple to make.

The backup was made via the Unix (Linux not yet invented) tar command, directly to the tape device. Pretty standard stuff for the time. When the backup was complete, I ran ‘tar tvf /dev/tape’ to get a directory listing of the backup as a means of verification. The command was successful, all the correct files were there.

Next steps:

  • format the system drive
  • install later version of Xenix
  • reload backup software and data files

The first two steps were fine. That final, and crucial step, however, was not so good. It seems a colleague from the employer had upgraded the RAM the previous day. One step in that process was missed. Everything appeared to be OK after that. The problem was the memory used by the tape driver was not handled correctly.

Every file on that backup was nothing but garbage, except for the header. The headers were fine, but the contents were not.

This was not a good day, week, or month.

Should I ever seem rather persnickety and nitpicky about backups and restores, there are good reasons for that.

Backups are not that important

I say that only in jest; of course backups are important. But are there some things more important than backups?

Restores: restores are very important. Even more important is the recovery, as the database cannot be opened until the recovery step completes.

How is recovery different than restore? Recovery is the process that brings the restored files to a point where the database can be opened. Often this point is a particular time or SCN (System Change Number) that is specified at the time the database is recovered. This is referred to as an ‘incomplete’ recovery.

A ‘complete recovery’ is one where ALL redo from the archive logs is applied to the datafiles, applying all changes to the database.

Sure, you need backups to do a restore. But if you never test your backups, you may as well just not make them. A backup that cannot be used when a restore is necessary can make life very difficult indeed. You want to do everything you can to protect your data.

There is much more to know about Oracle backups, but for the purpose of discussing the validation of backups, the backup will be kept simple.

Testing Oracle Backups

The best way to test a backup is to restore it.

While this is the ideal, it is not always practical, and sometimes not even possible due to the size of the database. Very few companies are willing to spend the money and time needed to have a server available just for testing a restore of a 152-terabyte database.

The next best thing is to validate the restores. Here, Oracle has you covered with RMAN methods to validate that backups can indeed be restored.

Backup Validate

First on the list is backup validate. I know, I said this would be about restoring databases. However, the backup validate command can help you determine the condition of your database, spotting physical corruption in the database, and if you choose, it can also locate and record logical discrepancies.

The command can be as simple as backup validate check logical database

This will take some time, depending on the size of the database, as Oracle will read all required database files. This operation can be performed online and is non-destructive, as no backup files are created, and no files are altered.

Read more here: Backup Validate

Make a Backup

The following is the RMAN script used to create a backup that is used for some following commands.

run {
  allocate channel ch1 device type DISK;
  allocate channel ch2 device type DISK;
  backup incremental level=0 tag "RMAN-TEST" format '/mnt/oracle-backups/rac19c01/%d_T%T_db_s%s_p%p_t%t' database filesperset 4 plus archivelog tag "RMAN-TEST";
  alter system switch logfile;
  backup format '/mnt/oracle-backups/rac19c01/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMAN-TEST";
  backup format '/mnt/oracle-backups/rac19c01/%d_T%T_cf%s_p%p_t%t' current controlfile tag "RMAN-TEST";
}
Restore Preview

Restore preview is fast, very fast. It is fast because it really doesn’t do much. The preview simply reads the metadata from the backup catalogue. This is useful just to ensure that the catalogue has captured the files necessary for a restore. This command does not read any files, just the metadata.

RMAN> restore preview database;

Starting restore at 2020-03-02 17:30:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=300 instance=cdb1 device type=DISK


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24      Incr 0  840.32M    DISK        00:00:20     2020-03-02 17:14:57
        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_db_s33_p1_t1034010877
  List of Datafiles in backup set 24
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1    0  Incr 80321492   2020-03-02 17:14:37              NO    +DATA/CDB/DATAFILE/system.259.1021126503
  4    0  Incr 80321492   2020-03-02 17:14:37              NO    +DATA/CDB/DATAFILE/undotbs1.261.1021126575
  9    0  Incr 80321492   2020-03-02 17:14:37              NO    +DATA/CDB/DATAFILE/undotbs2.274.1021127203

...


List of Archived Log Copies for database with db_unique_name CDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
810     1    457     A 2020-03-02 17:14:19
        Name: +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_457.346.1034010983

812     1    458     A 2020-03-02 17:16:23
        Name: +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_458.342.1034010991

813     1    459     A 2020-03-02 17:16:31
        Name: +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_459.340.1034010995

811     2    359     A 2020-03-02 17:14:19
        Name: +DATA/CDB/ARCHIVELOG/2020_03_02/thread_2_seq_359.344.1034010985

814     2    360     A 2020-03-02 17:16:25
        Name: +DATA/CDB/ARCHIVELOG/2020_03_02/thread_2_seq_360.339.1034010997

recovery will be done up to SCN 80321491
Media recovery start SCN is 80321491
Recovery must be done beyond SCN 80321782 to clear datafile fuzziness
Finished restore at 2020-03-02 17:30:52

At the end of the listing, you will find that media recovery must be done beyond SCN 80321782 for a successful recovery.

However, the listing does not include that SCN information. It is there, we just need to look at the archive logs explicitly:

RMAN> list backup of archivelog all;

List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
3       325.40M    DISK        00:00:06     2020-03-02 17:05:26
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_db_s11_p1_t1034010320

  List of Archived Logs in backup set 3
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    451     80282070   2020-03-02 15:00:03 80302686   2020-03-02 16:22:34
  1    452     80302686   2020-03-02 16:22:34 80318226   2020-03-02 17:05:18
  2    355     80302690   2020-03-02 16:22:36 80318223   2020-03-02 17:05:17

...

  List of Archived Logs in backup set 43
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    457     80321365   2020-03-02 17:14:19 80321856   2020-03-02 17:16:23
  1    458     80321856   2020-03-02 17:16:23 80321897   2020-03-02 17:16:31
  1    459     80321897   2020-03-02 17:16:31 80321912   2020-03-02 17:16:34
  2    360     80321860   2020-03-02 17:16:25 80321989   2020-03-02 17:16:37

...

We see 80321860, which is well past 80321782 and so we are confident that at least the required files have been recorded in the backup catalogue.

Note: By default, RMAN uses a date formate of ‘YYYY-MON-DD’.

This behavior can be altered by setting the NLS_DATE_FORMAT shell variable:

$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

$ rman target /
...

Restore Validate

Does the knowledge that your backup files are recorded in the backup catalogue give you a warm, fuzzy feeling of contentment?

It shouldn’t. A database cannot be restored with metadata alone. Not knowing that backups have been tested is the kind of thing that should keep a DBA awake at night.

Knowing that you need proper rest, let’s see another method to test backups, restore validate.

RMAN can validate the files used for a restore. This is not just a test to see if the files are available, but a read of all blocks in the backup files to ensure they are valid.

The following is an example, using the backup that was made previously:

RMAN> restore validate database;

Starting restore at 2020-03-02 17:52:37
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /mnt/oracle-backups/rac19c01/CDB_T20200302_db_s32_p1_t1034010877
channel ORA_DISK_1: piece handle=/mnt/oracle-backups/rac19c01/CDB_T20200302_db_s32_p1_t1034010877 tag=RMAN-TEST
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

...

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /mnt/oracle-backups/rac19c01/CDB_T20200302_db_s43_p1_t1034010975
channel ORA_DISK_1: piece handle=/mnt/oracle-backups/rac19c01/CDB_T20200302_db_s43_p1_t1034010975 tag=RMAN-TEST
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
Finished restore at 2020-03-02 17:54:11

This is a small test database; the validation required only about two minutes.

It’s okay to get some warm and fuzzies now, but don’t get too cozy. There are other types of files that must be restored for a recovery to take place.

Let’s check on the archive log files. We know that all the required archive logs were created after 13:00 today, as the backup was made at about 17:14, and the alert log shows several log switches between 17:00 and 17:14

2020-03-02T17:07:24.248403-08:00
Thread 1 advanced to log sequence 455 (LGWR switch)
  Current log# 1 seq# 455 mem# 0: +DATA/CDB/ONLINELOG/group_1.265.1021126651
  Current log# 1 seq# 455 mem# 1: +DATA/CDB/ONLINELOG/group_1.268.1021126653
2020-03-02T17:07:24.320618-08:00
ARC2 (PID:30093): Archived Log entry 805 added for T-1.S-454 ID 0x7f6be175 LAD:1

So now we can validate that archive logs can be read:

RMAN>  restore validate archivelog  from time "to_date('2020-03-02 17:00:00','yyyy-mm-dd hh24:mi:ss')";

Starting restore at 2020-03-02 18:09:58
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_452.341.1034010319
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_453.349.1034010441
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_454.352.1034010445
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_455.351.1034010445
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_456.347.1034010859
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_457.346.1034010983
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_458.342.1034010991
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_1_seq_459.340.1034010995
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_2_seq_355.353.1034010317
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_2_seq_356.319.1034010441
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_2_seq_357.350.1034010447
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_2_seq_358.348.1034010859
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_2_seq_359.344.1034010985
channel ORA_DISK_1: scanning archived log +DATA/CDB/ARCHIVELOG/2020_03_02/thread_2_seq_360.339.1034010997
Finished restore at 2020-03-02 18:10:12

Let’s also see if the controlfile is there:

RMAN>  restore validate controlfile;

Starting restore at 2020-03-02 18:13:09
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece +DATA/CDB/AUTOBACKUP/2020_03_02/s_1034011019.338.1034011019
channel ORA_DISK_1: piece handle=+DATA/CDB/AUTOBACKUP/2020_03_02/s_1034011019.338.1034011019 tag=TAG20200302T171659
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 2020-03-02 18:13:11

Finally, a list of the backup by tag (you do use tags, don’t you?) can be used to show that all files are present:

RMAN> list backupset tag "RMAN-TEST";


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
3       325.40M    DISK        00:00:06     2020-03-02 17:05:26
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_db_s11_p1_t1034010320

...

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
44      4.32M      DISK        00:00:00     2020-03-02 17:16:54
        BP Key: 44   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_arch_s53_p1_t1034011014

  List of Archived Logs in backup set 44
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    453     80318226   2020-03-02 17:05:18 80318656   2020-03-02 17:07:20
  1    454     80318656   2020-03-02 17:07:20 80318683   2020-03-02 17:07:24
  2    356     80318223   2020-03-02 17:05:17 80318660   2020-03-02 17:07:21
  2    357     80318660   2020-03-02 17:07:21 80318695   2020-03-02 17:07:27

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
45      Full    19.08M     DISK        00:00:01     2020-03-02 17:16:58
        BP Key: 45   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_cf54_p1_t1034011017
  Control File Included: Ckp SCN: 80322102     Ckp time: 2020-03-02 17:16:57

Now you can rest easy, as the data files, archivelogs and controlfile are all present in the backup made earlier.

The only way to be more sure of the backup is to use it to restore and recover the database.

Docs here: RESTORE

Recover Validate Header

While recover validate may sound like restore validate on steroids, it really isn’t.

According to the documentation, this command is nearly the same as restore preview. The difference is that when recover validate is used, the file headers are read to ensure they match what is found in the backup catalogue.

This would provide a little more assurance than the restore preview, but not as much as the restore validate.

The problem here is that in this 19.3 database, recover database validate header is examining only the archive log backups:

MAN> recover database validate header;

Starting recover at 2020-03-02 18:43:11
using channel ORA_DISK_1
using channel ORA_DISK_1


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
40      173.87M    DISK        00:00:04     2020-03-02 17:16:49
        BP Key: 40   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_arch_s49_p1_t1034011005

  List of Archived Logs in backup set 40
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    449     80240390   2020-03-02 12:18:51 80261546   2020-03-02 13:39:51

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
38      199.81M    DISK        00:00:05     2020-03-02 17:16:43
        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_arch_s47_p1_t1034010998

  List of Archived Logs in backup set 38
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  2    354     80240394   2020-03-02 12:18:51 80302690   2020-03-02 16:22:36
  1    450     80261546   2020-03-02 13:39:51 80282070   2020-03-02 15:00:03

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
39      174.02M    DISK        00:00:05     2020-03-02 17:16:43
        BP Key: 39   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_arch_s48_p1_t1034010998

  List of Archived Logs in backup set 39
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    451     80282070   2020-03-02 15:00:03 80302686   2020-03-02 16:22:34

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
41      151.39M    DISK        00:00:05     2020-03-02 17:16:50
        BP Key: 41   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_arch_s50_p1_t1034011005

  List of Archived Logs in backup set 41
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    452     80302686   2020-03-02 16:22:34 80318226   2020-03-02 17:05:18
  2    355     80302690   2020-03-02 16:22:36 80318223   2020-03-02 17:05:17

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
44      4.32M      DISK        00:00:00     2020-03-02 17:16:54
        BP Key: 44   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_arch_s53_p1_t1034011014

  List of Archived Logs in backup set 44
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  2    356     80318223   2020-03-02 17:05:17 80318660   2020-03-02 17:07:21
  1    453     80318226   2020-03-02 17:05:18 80318656   2020-03-02 17:07:20
  1    454     80318656   2020-03-02 17:07:20 80318683   2020-03-02 17:07:24
  2    357     80318660   2020-03-02 17:07:21 80318695   2020-03-02 17:07:27

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
42      15.46M     DISK        00:00:00     2020-03-02 17:16:53
        BP Key: 42   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_arch_s51_p1_t1034011013

  List of Archived Logs in backup set 42
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    455     80318683   2020-03-02 17:07:24 80318691   2020-03-02 17:07:24
  1    456     80318691   2020-03-02 17:07:24 80321365   2020-03-02 17:14:19
  2    358     80318695   2020-03-02 17:07:27 80321362   2020-03-02 17:14:19
  2    359     80321362   2020-03-02 17:14:19 80321860   2020-03-02 17:16:25

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
43      5.46M      DISK        00:00:00     2020-03-02 17:16:53
        BP Key: 43   Status: AVAILABLE  Compressed: NO  Tag: RMAN-TEST
        Piece Name: /mnt/oracle-backups/rac19c01/CDB_T20200302_arch_s52_p1_t1034011013

  List of Archived Logs in backup set 43
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    457     80321365   2020-03-02 17:14:19 80321856   2020-03-02 17:16:23
  1    458     80321856   2020-03-02 17:16:23 80321897   2020-03-02 17:16:31
  2    360     80321860   2020-03-02 17:16:25 80321989   2020-03-02 17:16:37
  1    459     80321897   2020-03-02 17:16:31 80321912   2020-03-02 17:16:34
validation succeeded for backup piece
recovery will be done up to SCN 2163919
Media recovery start SCN is 2163919
Recovery must be done beyond SCN 80321989 to clear datafile fuzziness
Finished recover at 2020-03-02 18:43:13

At this time I do not know if this is due to a bug in the code, or a bug in the documentation.

Either way, restore validate gives me the kind of assurance I need that my backups can be restored.

Learn more about Pythian’s Oracle Services.

Categories: DBA Blogs

Backup Oracle to Amazon AWS S3 – Part 1

Mon, 2020-03-30 17:46
Backup Oracle to S3 – Part 1

Oracle has the ability to backup directly to Amazon S3 buckets.

This is a very attractive option for many reasons:

  • offsite backup
  • inexpensive storage
  • backups can automatically be migrated to archive storage (Amazon Glacier)

The ‘Cloud Backup’ capability of Oracle is found with the ‘Oracle Secure Backup’ package, which is an extra-cost option.

Circumstances may not allow you to purchase this software. Perhaps you are running a departmental server and there is no budget for this.

There could be many reasons.

That does not mean that your Oracle backups cannot be made directly to S3.

If your databases are running on a reasonably recent version of Linux, you may be able to use s3fs which uses the FUSE (Filesystem in Userspace) filesystem.

Requirements:

  • Linux kernel of 2.6.18-164+
  • The Linux Kernel must support FUSE

The system used for the test:

  • Oracle Linux 7.7
  • Oracle Database 19.3

Due to the nature of how s3 works (no random access) this method is only appropriate for static backup files.

The use of s3 would not be a good choice for updatable images copies for instance.

Another limitation is file size – 5T is the maximum file size allowed. More on that later.

Another limitation was discovered, as well as a workaround. This will also be discussed.

Let’s get started.

Install Amazon Utilities

This is not a tutorial on installing Amazon command line utilities, so only the necessary commands are shown for those components.

I first installed awscli and s3cmd

# yum install -y awscli s3cmd

AWS credentials were then set up for both oracle and root.

Now create an s3 bucket:

# s3cmd mb s3://pythian-oracle-backups

Now install s3fs-fuse from the yum repo:

$ yum install -y s3fs-fuse

Create a mount point, and give it to oracle:

# mkdir /mnt/s3-oracle-backup
# chown oracle:oinstall /mnt/s3-oracle-backup

Allow oracle to run fusermount

# chmod +x /usr/bin/fusermount

As oracle tries to mount an s3 ‘filesytem’:

$ s3fs pythian-oracle-backups /mnt/s3-oracle-backup

Checking for a mount at /mnt/s3-oracle-backup, there was not one:

# ls /mnt | grep s3
#

Next I tried the debug options:

$ s3fs pythian-oracle-backups /mnt/s3-oracle-backup -o dbglevel=info -f -o curldbg

A couple of things appeared here:

  • libcurl and libcurl-dev were out of date – newer versions were recommended for performance reasons
  • access was denied due to invalid credentials.

 

[WAN] curl.cpp:ResetHandle(1855): The CURLOPT_SSL_ENABLE_ALPN option could not be unset. S3 server does not support ALPN, then this option should be disabled to maximize performance. you need to use libcurl 7.36.0 or later.
[WAN] curl.cpp:ResetHandle(1858): The S3FS_CURLOPT_KEEP_SENDING_ON_ERROR option could not be set. For maximize performance you need to enable this option and you should use libcurl 7.51.0 or later.
...
[CRT] s3fs.cpp:s3fs_check_service(3864): invalid credentials(host=https://s3.amazonaws.com) - result of checking service.
[ERR] s3fs.cpp:s3fs_exit_fuseloop(3440): Exiting FUSE event loop due to errors

The credentials were correct, as s3cmd was working properly:

$ s3cmd ls
2015-12-28 21:29  s3://pythian-apps
2016-01-09 21:33  s3://pythian-aws-billing
2010-08-08 00:07  s3://pythian-data
2017-06-12 23:55  s3://pythian-dropbox-backup
2010-08-08 00:08  s3://pythian-fcedit
2010-08-07 19:32  s3://pythian-keepass
2011-09-19 17:28  s3://pythian-keepass2
2011-09-19 18:04  s3://pythian-keepass2-bkup
2010-08-08 00:08  s3://pythian-manuals
2010-11-06 23:25  s3://pythian-maps
2014-08-28 21:14  s3://pythian-music
2012-01-07 18:56  s3://pythian-oracle
2020-03-04 16:18  s3://pythian-oracle-backups
2018-01-15 00:46  s3://pythian-oracle-sw
2010-08-07 18:21  s3://pythian-photos
2016-03-05 16:42  s3://pythian-rx7
2018-12-14 15:49  s3://pythian-stc
2015-03-02 23:22  s3://linux-backups

So I removed s3fs-fuse.

# yum erase s3fs-fuse

Create s3fs-fuse from source

Building software from source is something I have found necessary a number of times on RedHat Linux and its variants.

While the software I need may be available in the repos, it is sometimes too old to be of use.

Such is the case with s3fs-fuse.

Before continuing, I obtained a more recent version of libcurl and libcurl-dev

Get Recent libcurl – instructions

rpm -Uvh http://www.city-fan.org/ftp/contrib/yum-repo/rhel7/x86_64/city-fan.org-release-2-1.rhel7.noarch.rpm
yum --enablerepo=city-fan.org install libcurl libcurl-devel

Installation instructions are all in the s3fs-fuse github repo, and can be easily referenced in a browser:

Compile s3fs-fuse

Platform Specific notes – install s3fs-fuse

Install other required libs:

# yum install gcc libstdc++-devel gcc-c++ fuse fuse-devel libxml2-devel mailcap git automake make

Now get the source

# git clone https://github.com/s3fs-fuse/s3fs-fuse.git

At this point the software can be compiled:

# cd s3fs-fuse/

# ./autogen.sh

# ./configure --prefix=/usr --with-openssl

# make install

Again, make it executable for others

# chmod +x /usr/bin/fusermount

As root, modify the file /etc/fuse.conf by uncommenting the user_allow_other line:

# mount_max = 1000
user_allow_other

As oracle, mount the filesystem and test it:

$ s3fs pythian-oracle-backups /mnt/s3-oracle-backup -o default_permissions -o allow_other -o 'umask=0007'


$ touch  /mnt/s3-oracle-backup/test-file-oracle

$ ls -l /mnt/s3-oracle-backup/
total 1
-rw-r--r-- 1 oracle oinstall 0 Mar  4 11:18 test-file-oracle

$ s3cmd ls s3://pythian-oracle-backups
2020-03-04 19:18         0   s3://pythian-oracle-backups/test-file-oracle

The extra options allow the grid user to also write to the mounted filesystem. In my case that was necessary, as the database under tests is using ASM, running as ‘grid’.

See FUSE Manpage for more on FUSE options.

Now for some backups.

RMAN Backups

At first, I attempted to use RMAN to create backups.

This failed. The following errors were seen:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch1 channel at 03/04/2020 16:49:58
ORA-19506: failed to create sequential file, name=\"CDB_T20200304_db_s15_p1_t1034182197
ORA-27028: skgfqcre: sbtbackup returned error\n", 46) = 46
ORA-19511: non RMAN, but media manager or vendor specific failure, error text

After running strace on the job I could see that errors were occurring on read() and lseek() calls.

It appeared that RMAN was trying to do block-level IO on the files, which is not going to work on S3 (object) storage.

So, it was time to go old school and use user-managed backups.

That was also not without some issues, but they can be handled.

User-Managed Backups

Before there was RMAN, there were User Managed Backups.

While not commonly used, this method of Oracle backup still works.

The documentation just referenced discusses all the considerations in creating backups.

Essentially, these are the steps

  • put the database in backup mode
  • backup data files
  • take the database out of the backup mode
  • switch logfile
  • backup archive logs
  • backup controlfiles (both binary and to trace)
  • backup spfile to pfile
Errors in User-Managed Backup

The first attempts to backup data files and archive logs failed, but in a different manner than the RMAN backups.

ASMCMD-8016: copy source '+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/undotbs1.276.1020807859' and target '/mnt/s3-oracle-backup/pdb1/2020-03-05_12-44-54/datafiles/undotbs1.276.1020807859' failed
ORA-19505: failed to identify file "/mnt/s3-oracle-backup/pdb1/2020-03-05_12-44-54/datafiles/undotbs1.276.1020807859"
ORA-27044: unable to write the header block of file
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
ORA-15120: ASM file name '/mnt/s3-oracle-backup/pdb1/2020-03-05_12-44-54/datafiles/undotbs1.276.1020807859' does not begin with the ASM prefix character
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 617
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

My hypothesis at this point was the asmcmd cp command has made the call to create the file, and then when it tried to write the file immediately afterwards, it was not available. This could be due to the nature of s3 – the filesystem hasn’t finished creating the file, and ASM is trying to write to it.

If that was the case, I speculated that pre-creating the files with touch would solve that problem; and it did.

Following is the output from a partial backup – the datafiles and archivelogs were skipped, and only controlfile and pfile backups created.

[grid@ora192rac01 RMAN]$ ./backup-db-s3.sh
s3fs on /mnt/s3-oracle-backup type fuse.s3fs (rw,nosuid,nodev,relatime,user_id=54321,group_id=54321,default_permissions,allow_other)
SID: cdb1
ORACLE_SID = [+ASM1] ? The Oracle base has been changed from /u01/app/19.0.0/grid_base to /u01/app/oracle

undotbs1.276.1020807859:+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/undotbs1.276.1020807859
sysaux.275.1020807859:+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/sysaux.275.1020807859
undo_2.279.1020807925:+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/undo_2.279.1020807925
system.277.1020807859:+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/system.277.1020807859
users.280.1020807941:+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/users.280.1020807941
ORACLE_SID = [cdb1] ? The Oracle base has been changed from /u01/app/oracle to /u01/app/19.0.0/grid_base
Datafile: undotbs1.276.1020807859:+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/undotbs1.276.1020807859
Skipping Datafile: +DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/undotbs1.276.1020807859
Touch: touch /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/datafiles/undotbs1.276.1020807859
Datafile: sysaux.275.1020807859:+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/sysaux.275.1020807859
Skipping Datafile: +DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/sysaux.275.1020807859
Touch: touch /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/datafiles/sysaux.275.1020807859
Datafile: undo_2.279.1020807925:+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/undo_2.279.1020807925
Skipping Datafile: +DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/undo_2.279.1020807925
Touch: touch /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/datafiles/undo_2.279.1020807925
Datafile: system.277.1020807859:+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/system.277.1020807859
Skipping Datafile: +DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/system.277.1020807859
Touch: touch /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/datafiles/system.277.1020807859
Datafile: users.280.1020807941:+DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/users.280.1020807941
Skipping Datafile: +DATA/CDB/9423786B7CAA4898E053DD01A8C04EF1/DATAFILE/users.280.1020807941
Touch: touch /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/datafiles/users.280.1020807941
ORACLE_SID = [+ASM1] ? The Oracle base has been changed from /u01/app/19.0.0/grid_base to /u01/app/oracle

System altered.

min scn: 62876556
max scn: 62877828
ORACLE_SID = [cdb1] ? The Oracle base remains unchanged with value /u01/app/oracle

thread_1_seq_436.284.1034250403:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_436.284.1034250403
thread_1_seq_437.283.1034251313:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_437.283.1034251313
thread_2_seq_345.281.1034251317:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_2_seq_345.281.1034251317
thread_1_seq_438.280.1034251757:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_438.280.1034251757
thread_1_seq_436.284.1034250403:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_436.284.1034250403
thread_1_seq_437.283.1034251313:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_437.283.1034251313
thread_2_seq_345.281.1034251317:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_2_seq_345.281.1034251317
thread_1_seq_438.280.1034251757:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_438.280.1034251757
ORACLE_SID = [cdb1] ? The Oracle base has been changed from /u01/app/oracle to /u01/app/19.0.0/grid_base
archive log: thread_1_seq_436.284.1034250403:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_436.284.1034250403
Skipping Archivelog: +FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_436.284.1034250403
archive log: thread_1_seq_437.283.1034251313:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_437.283.1034251313
Skipping Archivelog: +FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_437.283.1034251313
archive log: thread_2_seq_345.281.1034251317:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_2_seq_345.281.1034251317
Skipping Archivelog: +FRA/CDB/ARCHIVELOG/2020_03_05/thread_2_seq_345.281.1034251317
archive log: thread_1_seq_438.280.1034251757:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_438.280.1034251757
Skipping Archivelog: +FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_438.280.1034251757
archive log: thread_1_seq_436.284.1034250403:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_436.284.1034250403
Skipping Archivelog: +FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_436.284.1034250403
archive log: thread_1_seq_437.283.1034251313:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_437.283.1034251313
Skipping Archivelog: +FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_437.283.1034251313
archive log: thread_2_seq_345.281.1034251317:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_2_seq_345.281.1034251317
Skipping Archivelog: +FRA/CDB/ARCHIVELOG/2020_03_05/thread_2_seq_345.281.1034251317
archive log: thread_1_seq_438.280.1034251757:+FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_438.280.1034251757
Skipping Archivelog: +FRA/CDB/ARCHIVELOG/2020_03_05/thread_1_seq_438.280.1034251757

And here are the files created:

[grid@ora192rac01 RMAN]$ find /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/ -type f | xargs ls -ldtar
-rwxrwx--- 1 grid   oinstall      483 Mar  5 12:09 /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/datafiles/datafiles.txt
-rwxrwx--- 1 grid   oinstall      761 Mar  5 12:09 /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/archivelogs/archlogs.txt
-rwxrwx--- 1 grid   oinstall 19841024 Mar  5 12:09 /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/controlfile/controlfile.bkup
-rwxrwx--- 1 grid   oinstall    12646 Mar  5 12:09 /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/controlfile/controlfile.txt
-rwxrwx--- 1 oracle asmadmin     1950 Mar  5 12:09 /mnt/s3-oracle-backup/pdb1/2020-03-05_12-09-08/pfile/pfile.txt

As you may guess, even a small database takes some time to complete a backup to a remote destination when the uplink (10M in my case) is rather limited.

Archive logs have been successfully backed up. A full backup is currently running and will take some time to complete.

The backup-db-s3.sh script may be found at the end of this blog.

Un-mounting the s3 Filesystem

There is a bug in s3fs as regards unmounting a filesystem:

$ s3fs unmount  /mnt/s3-oracle-backup/ -o nonempty
fusermount: failed to access mountpoint /mnt/s3-oracle-backup: Permission denied

Running this fusermount command was successful, however:

fusermount -u -o  rw,nosuid,nodev,nonempty,subtype=s3fs /mnt/s3-oracle-backup

Wrap Up

There are other s3 limitations that may come into play.

In particular, remember that s3fs is simulating a file system on s3.

Large numbers of files in an s3 bucket can cause a directory listing to be quite slow.

Using sub-directories will help alleviate that issue.

And finally, the big question: Can this backup be restored?

Stay tuned, that will be addressed in a future installment.

backup-db-s3.sh
#!/usr/bin/env bash

: <<COMMENT

backup-db-s3.sh
Oracle User Managed Backup
https://docs.oracle.com/database/121/BRADV/osbackup.htm

Jared Still - Pythian 2020
still@pythian.com
jkstill@gmail.com

This script is S3 specific
It was written as part of a blog on backing up directly to Amazon AWS S3

COMMENT

: <<NOTES

This script makes the following assumptions:

- the database being backed up is a PDB (pluggable database) in a container database (CDB)
- ASM is used
- backup is made by grid user, as oracle does not have ASM privileges


The /mnt/s3-oracle-backup directory was initially created by the oracle user.
The dba group was added to the grid user so tha grid could also write to the directory

As both Oracle and Grid must write here, it is necessary for the mount point to have a common group
for the oracle and grid users

Note: spfile and controlfile backup come from the oracle users, even if run as grid, as it is the
oracle db instance creating the files

There is no warranty expressed or implied by the use of this script - use it at your own risk.

As per usual, be sure to test any backups made to verify they are complete and recoverable.

NOTES

declare -A sid
sid[ora]='cdb1'
sid[asm]='+ASM1'

declare dbname=pdb1
declare -A db
db[db]=dbserver/pdb1
db[cdb]=dbserver/cdb

declare -A username
username[db]=sys
username[cdb]=sys

declare -A password
password[db]=PASSWORD
password[cdb]=PASSWORD

# any value other than 0 will activate these flags
# skip datafile backup - for debugging
declare skipDatafileBackup=0

# skip archivelog backup - for debugging
declare skipArchlogBackup=0

#########################################
## user configurations above this line ##
#########################################

declare rc
declare touchSleep=1

declare timestamp=$(date '+%Y-%m-%d_%H-%M-%S')
declare s3Mount=/mnt/s3-oracle-backup
declare backupHome=${s3Mount}/${dbname}/${timestamp}

declare -A targetDirs
targetDirs[datafiles]=${backupHome}/datafiles
targetDirs[archivelogs]=${backupHome}/archivelogs
targetDirs[controlfile]=${backupHome}/controlfile
targetDirs[pfile]=${backupHome}/pfile

declare -A inventory
inventory[datafiles]=${targetDirs[datafiles]}/datafiles.txt
inventory[archivelogs]=${targetDirs[archivelogs]}/archlogs.txt

declare -A sql
declare -A scn
declare -A filenames
filenames[controlfile_backup]=${targetDirs[controlfile]}/controlfile.bkup
filenames[controlfile_backup_tmp]=/tmp/controlfile.tmp
filenames[controlfile_trace]=${targetDirs[controlfile]}/controlfile.txt
filenames[pfile_backup]=${targetDirs[pfile]}/pfile.txt

sql[minscn]='select min(checkpoint_change#) retval from gv\$datafile'
sql[maxscn]='select max(current_scn) retval from gv\$database'

sql[backup_controlfile]="alter database backup controlfile to '${filenames[controlfile_backup]}' reuse"
sql[backup_controlfile_tmp]="alter database backup controlfile to '${filenames[controlfile_backup_tmp]}' reuse"
sql[backup_controlfile_trace]="alter database backup controlfile to trace as '${filenames[controlfile_trace]}' reuse"

sql[backup_spfile]="create pfile='${filenames[pfile_backup]}' from spfile"
sql[begin_backup]="alter database begin backup"
sql[end_backup]="alter database end backup"

newdir () {
   local dirName="$1"

   [[ -z $dirName ]] && {
      echo
      echo "newdir(): dirName cannot be empty"
      echo
      exit 3
   }

   mkdir -p $dirName

   [[ $? -ne 0 ]] && {
      echo
      echo could not create $dirName
      echo
      exit 1
   }

   [[ -d $newDir ]] && {
      echo
      echo $dirName not available
      echo
      exit 2
   }

   # ownership is grid:oinstall
   # oracle must also be able to write here:
   chmod g+rwx $dirName
}


# pass in the key to username[], etc, not the actual db name
# pass in a SQL that returns a single value
# always runs as sysdba

getSqlVal () {
   local sqldb="$1"; shift
   local sql="$@"

eval "sqlplus  -L -s ${username[$sqldb]}/${password[$sqldb]}@${db[$sqldb]} as sysdba <<-EOF

   set pause off
   set echo off
   set timing off
   set trimspool on
   set verify off
   set  head off

   clear col
   clear break
   clear computes

   btitle ''
   ttitle ''

   btitle off
   ttitle off

   set newpage 1
   set pages 0 lines 200 term on feed off

   $sql;

   exit

EOF
"

}

# execute some sql - no return value expected
# currently the same as getSqlVal, but subject to change
# always runs as sysdba
exeSql () {
   local sqldb="$1"; shift
   local sql="$@"

eval "sqlplus  -L -s ${username[$sqldb]}/${password[$sqldb]}@${db[$sqldb]} as sysdba <<-EOF

   set pause off
   set echo off
   set timing off
   set trimspool on
   set verify off
   set  head off

   clear col
   clear break
   clear computes

   btitle ''
   ttitle ''

   btitle off
   ttitle off

   set newpage 1
   set pages 0 lines 200 term on feed off

   $sql;

   exit

EOF
"

}

cleanup () {
   echo
   echo "!!! $0 Interrupted - cleaning up !!"
   echo
   exeSql db ${sql[end_backup]}
   exit 4
}

exitIfError () {
   local errCode=$?

   if [[ $errCode -ne 0 ]]; then
      echo
      echo Error Encountered - exiting
      echo
      cleanup
   fi
}

# precreate file names on s3
# then sleep a bit to ensure it is available
s3Touch () {
   local file2touch="$@"

   touch $file2touch

   declare rc=$?

   [[ $rc -ne 0 ]] && {
      echo
      echo "touch failed with $rc (s3Touch)"
      echo
   }

   sleep $touchSleep

}

trap "cleanup" INT
trap "cleanup" TERM
trap "cleanup" ABRT
trap "cleanup" QUIT

# ensure that s3fs is mounted
/bin/mount | grep $s3Mount
[[ $? -ne 0 ]] && {

   echo
   echo "! $s3Mount is not Mounted !"
   echo
   exit 5

}


for dir2create in ${targetDirs[@]}
do
   newdir $dir2create
done


unset ORAENV_ASK

echo SID: ${sid[ora]}

. oraenv <<< ${sid[ora]}

scn[min]=$(getSqlVal db ${sql[minscn]})
exitIfError $?

# begin backup mode - get a list of datafiles

exeSql db ${sql[begin_backup]}

exitIfError $?

sqlplus -L -s ${username[db]}/${password[db]}@${db[db]} as sysdba <<-EOF

   set echo off term  on pause off verify off
   set feed off head off
   set linesize 1000 trimspool on
   col name_data format a1000

   spool ${inventory[datafiles]}
   select substr(file_name,instr(file_name,'/',-1)+1) ||':'|| file_name name_data from dba_data_files;
   spool off

   exit;

EOF

. oraenv <<< ${sid[asm]}

# backup the datafiles

for df in $(grep -v '^$' ${inventory[datafiles]} )
do
   echo Datafile: $df

   declare shortName=$(echo $df | cut -f1 -d:)
   declare fullName=$(echo $df | cut -f2 -d:)

   if [[ $skipDatafileBackup -ne 0 ]]; then
      echo "Skipping Datafile: $fullName"
   else
      # asmcmd cp expects the file to appear immediately after creating
      # and that is not occuring with s3
      # use 'touch' to precreate the file
      s3Touch "${targetDirs[datafiles]}/${shortName}"

      asmcmd cp "$fullName" ${targetDirs[datafiles]}
   fi
done

. oraenv <<< ${sid[ora]}


# end backup mode
exeSql db ${sql[end_backup]}

exitIfError $?

# switch logfile
exeSql cdb 'alter system switch logfile'

scn[max]=$(getSqlVal db ${sql[maxscn]})
exitIfError $?

echo min scn: ${scn[min]}
echo max scn: ${scn[max]}

. oraenv <<< ${sid[ora]}

# get list of archivelogs

sqlplus -L -s ${username[db]}/${password[db]}@${db[db]} as sysdba <<-EOF

   set echo off term  on pause off verify off
   set feed off head off
   col name format a200
   set linesize 1000 trimspool on
   col name_data format a1000

   spool ${inventory[archivelogs]}
   select  distinct substr(name,instr(name,'/',-1)+1) ||':'|| name name_date
   from gv\$archived_log
   where first_change# >= (select min(first_change#) min_change from gv\$archived_log where ${scn[min]} between first_change# and next_change#);
   spool off

   exit;

EOF

echo
echo "finished archivelog inventory"
echo

. oraenv <<< ${sid[asm]}

# backup the archivelogs

for archlog in $(grep -v '^$' ${inventory[archivelogs]})
do
   echo archive log: $archlog

   declare shortName=$(echo $archlog | cut -f1 -d:)
   declare fullName=$(echo $archlog | cut -f2 -d:)

   if [[ $skipArchlogBackup -ne 0 ]]; then
      echo "Skipping Archivelog: $fullName"
   else
      s3Touch "${targetDirs[archivelogs]}/${shortName}"
      asmcmd cp "$fullName" ${targetDirs[archivelogs]}
   fi
done

# backup the controlfile
touch ${filenames[controlfile_backup]}
touch ${filenames[controlfile_trace]}

exeSql cdb "${sql[backup_controlfile_tmp]}"
rc=$?
#exitIfError $rc

: <<'ORA-00245'

This error may occur on RAC when making a contfolfile backup:

  ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage

As we are not making the backup to shared storage, there is little to be done about it.

A trace controlfile backup is also made

ORA-00245

if [[ $rc -eq 0 ]]; then
   cp ${filenames[controlfile_backup_tmp]} ${filenames[controlfile_backup]}
else
   echo
   echo Failed to created temp backup controlfile
   echo This is probably due to ORA-00245 on RAC
   echo
   echo This can happen if the backup is on non-shared storage
   echo and the controlfile is currently being written by another instance
   echo
   echo A backup controfile to trace will still be created
   echo
fi

# this may work on S3, but often fails, much as datafiles do if not first 'touched'
# it also fails due to 'write past end of file'
# do not backup directly to s3, just copy the tmp backupcontrol file to s3 as seen previously

#exeSql cdb "${sql[backup_controlfile]}"
#exitIfError $?

exeSql cdb "${sql[backup_controlfile_trace]}"
exitIfError $?

# it is assumed that an spfile is in use
# modify accordingly if that is not the case
#touch $Pfilenames[pfile_backup]}
exeSql cdb "${sql[backup_spfile]}"
exitIfError $?

 

Categories: DBA Blogs

Using FreeRadius to Authorize Oracle Connections

Wed, 2020-03-18 08:58
Introduction

This article will show how to configure the FreeRadius RADIUS server for use in authorizing Oracle connections.

The implementation is very basic:

  • no 2FA
  • simple passwords

Clearly, this is not ready for production use. It is important to first get the basic system working.

With a known working baseline, the system can then be refined to add features:

  • 2FA
  • better passwords
  • more granularity. currently, the scope is the entire client network
  • use a database as a backing store for FreeRadius, rather than configuration files
References

References are listed early, for convenience.

Oracle notes

Installing and Configuring RADIUS and Oracle9i R2 (9.2.0) Advanced Security Option (ASO) on Linux (Doc ID 272804.1)
Enabling RADIUS Authentication and Accounting (Doc ID 132820.1)
Radius Overview (Doc ID 132812.1)

Online documentation

Configuring Radius Authentication
SQLNET.ORA Parameters

FreeRadius – Home
FreeRadius – Pre-built Packages
FreeRadius – github
FreeRadius – Wiki

Building the Radius Server

At first, I attempted to use Oracle Linux 7.6 to build a Radius Server using FreeRadius.

I could not locate any binary distributions that could be successfully installed on Linux 7.6.

Next, I tried building from source. There were a number of supporting libraries that needed to be installed before even attempting to compile FreeRadius.

When I saw that the dependency list included glibc, I gave up. The installed glibc was the latest version available from the Linux Repos. Having tried in the past to build a later version of glibc, and failing miserably, it was time to choose another platform.

As nearly everything on the system is dependent on glibc, it is quite difficult to build and install glibc from source.

So I decided to choose Ubuntu 19.1, in the form of Linux Mint 19.1 with the Mate interface. In case you are wondering why Linux Mint, it is because it is easy to install with the Mate interface, which I happen to prefer over others that I have tried.

I will not be covering the details of obtaining and building a VM will not be covered here. Suffice it today I used the following:

  • Virtual Box
  • Linux Mate 19.1

Once the VM was installed, ssh and FreeRadius were installed.

Created a VM from Linux Mate 19.1 (Ubuntu base)

Install software

Install OpenSSH and FreeRadius; it doesn’t get much easier than this:

# apt install -y openssh-server 
# apt install -y freeradius freeradius-utils

This installed FreeRadius 3.0.

OpenSSH was installed so I could use Putty to access the server.

Modifications to FreeRadius files

Not many changes were required.

Modify /etc/freeradius/3.0/mods-config/files/authorize to un-comment the ‘bob’ user for testing:

Validate that all is working (from the radius server).

root@radius-01:/etc/freeradius/3.0# radtest bob hello 127.0.0.1 0 testing123
Sent Access-Request Id 187 from 0.0.0.0:47728 to 127.0.0.1:1812 length 73
        User-Name = "bob"
        User-Password = "hello"
        NAS-IP-Address = 127.0.1.1
        NAS-Port = 0
        Message-Authenticator = 0x00
        Cleartext-Password = "hello"
Received Access-Accept Id 187 from 127.0.0.1:1812 to 0.0.0.0:0 length 32
        Reply-Message = "Hello, bob"
Configure for Oracle

Now the configuration for Oracle on the FreeRadius server can begin.

Add the client information

Modify the file /etc/freeradius/3.0/clients.conf

All that was needed was to add a client for Oracle.

client jks-oracle {
    # both ipaddr and ipv4addr will work as is
    ipaddr = 192.168.1.0/24
    #ipv4addr = *
    secret = secretoracle
    shortname = oracle
    nastype = other
}

This is a very broad rule, covering my entire network.

More fine grained rules can be created, but this is sufficient for now.

Create a user

A user can be added to the file /etc/freeradius/3.0/mods-config/files/authorize.

Added to the authorization file:

JKSTILL_RAD Cleartext-Password := "radius" 
    Reply-Message := "Welcome to Oracle, %{User-Name}"
Verify RADIUS is installed in Oracle
The system under test is a 19.3 19c 2 Node RAC.

Note: Changes to the DB server must be made on each node.

Some of the references are rather old, such as this note: Installing and Configuring RADIUS and Oracle9i R2 (9.2.0) Advanced Security Option (ASO) on Linux (Doc ID 272804.1). While there may be some useful information in older documentation, you may find parts that no longer apply.

For instance, the note specifies this lib should be present:

[oracle@hostname Oracle_Home/lib]$ ls -l libnrad9.so 
-rwxr-xr-x 1 oracle dba 41987 Jun 23 2003 libnrad9.so

This does not exist in a 19c install and is not required.

If you find something that doesn’t match your system when reviewing an old document such as 272804.1, don’t immediately assume there is a problem, as it may just be that some parts of old documentation no longer apply.

I suspect it is not really necessary to perform the check with adapters, but I did so anyway for completeness.

Login as the oracle user:

 

[oracle@19c01 admin]$ adapters

Installed Oracle Net transport protocols are:

    IPC
    BEQ
    TCP/IP
    SSL
    RAW
    SDP/IB
    ExaDirect

Installed Oracle Net naming methods are:

    Local Naming (tnsnames.ora)
    Oracle Directory Naming
    Oracle Host Naming

Installed Oracle Advanced Security options are:

    RC4 40-bit encryption
    RC4 56-bit encryption
    RC4 128-bit encryption
    RC4 256-bit encryption
    DES40 40-bit encryption
    DES 56-bit encryption
    3DES 112-bit encryption
    3DES 168-bit encryption
    AES 128-bit encryption
    AES 192-bit encryption
    AES 256-bit encryption
    MD5 crypto-checksumming
    SHA-1 crypto-checksumming
    Kerberos v5 authentication
    RADIUS authentication

[oracle@19c01 admin]$ cd $ORACLE_HOME/bin
[oracle@19c01 bin]$ adapters ./oracle

Oracle Net transport protocols linked with ./oracle are:

    IPC
    BEQ
    TCP/IP
    SSL
    SDP/IB
    ExaDirect
    RAW

Oracle Net naming methods linked with ./oracle are:

    Local Naming (tnsnames.ora)
    Oracle Directory Naming
    Oracle Host Naming

Oracle Advanced Security options linked with ./oracle are:

    RC4 40-bit encryption
    RC4 56-bit encryption
    RC4 128-bit encryption
    RC4 256-bit encryption
    DES40 40-bit encryption
    DES 56-bit encryption
    3DES 112-bit encryption
    3DES 168-bit encryption
    AES 128-bit encryption
    AES 192-bit encryption
    AES 256-bit encryption
    MD5 crypto-checksumming
    SHA-1 crypto-checksumming
    Kerberos v5 authentication
    RADIUS authentication
sqlnet.ora at the server

As per Configuring RADIUS Authentication:

These changes must be made in the sqlnet.ora for the database, not grid.

 

QLNET.RADIUS_AUTHENTICATION_PORT = 1812

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, RADIUS)

SQLNET.RADIUS_AUTHENTICATION = 192.168.1.216

SQLNET.RADIUS_SECRET=/u01/app/oracle/product/19.0.0/dbhome_1/network/security/radius.key

ADR_BASE = /u01/app/oracle
radius.key

The file radius.key contains the secret added earlier in clients.conf on the FreeRadius server:

oracle@rac19c01 admin]$ cat  /u01/app/oracle/product/19.0.0/dbhome_1/network/security/radius.key
secretoracle

[oracle@rac19c01 admin]$ chmod 600  /u01/app/oracle/product/19.0.0/dbhome_1/network/security/radius.key
Database parameters

The default for os_authent_prefix is ops$, which must be changed to an empty string.

Counterintuitively, the remote_os_authent parameter must be set to FALSE. As per the Oracle note Enabling RADIUS Authentication and Accounting (Doc ID 132820.1), this is due to a possible security breach if the parameter is set to TRUE.

The default is FALSE, but I include it here for completeness.

 

alter system set os_authent_prefix='' scope=spfile sid='*';
alter system set remote_os_authent = false scope = spfile sid='*';

Then restart the database.

Add an Oracle user to FreeRadius

A user JKSTILL_RAD was created in the authorization configuration file, with a completely non-secure password of radius. Please, don’t use simple passwords for production use.

The following was added to /etc/freeradius/3.0/mods-config/files/authorize:

## Oracle Users - username must be uppercase

JKSTILL_RAD     Cleartext-Password := "radius"
        Reply-Message := "Welcome to Oracle, %{User-Name}"

As noted in the comment, the username must be in uppercase. Radius usernames are case sensitive, and as Oracle stores usernames in upper case, the user name here must also be in upper case.

Test the username

The following test was performed on the FreeRadius server:

root@radius-01:/etc/freeradius/3.0# radtest jkstill_rad radius 127.0.0.1 0 testing123
Sent Access-Request Id 200 from 0.0.0.0:56423 to 127.0.0.1:1812 length 81
        User-Name = "jkstill_rad"
        User-Password = "radius"
        NAS-IP-Address = 127.0.1.1
        NAS-Port = 0
        Message-Authenticator = 0x00
        Cleartext-Password = "radius"
Received Access-Accept Id 200 from 127.0.0.1:1812 to 0.0.0.0:0 length 52
        Reply-Message = "Welcome to Oracle, jkstill_rad"

OK, that all worked!

Create a user in the database

This part is simple enough. I logged into the database of choice, and created the user jkstill_rad as follows:

SQL> create user jkstill_rad identified externally; 
SQL> grant create session, connect, resource to jkstill_rad;

In case you are wondering about that lower case username; Oracle will convert it to upper case internally.

Configure the client

The only thing left to do before testing is to modify the sqlnet.ora at the client-side.

The following line is all that needs to be added:

SQLNET.AUTHENTICATION_SERVICES= (radius)
Testing the connection

The final step: does it work?

echo "set heading off
> select
>    'DB_NAME                : ' || name name,
>    'SESSION_USER           : ' || sys_context('USERENV','SESSION_USER') session_user,
>    'AUTHENTICATED_IDENTITY : ' || sys_context('USERENV','AUTHENTICATED_IDENTITY') auth_id,
>    'AUTHENTICATION_METHOD  : ' || sys_context('USERENV','AUTHENTICATION_METHOD') auth_method,
>    'LDAP_SERVER_TYPE       : ' || sys_context('USERENV','LDAP_SERVER_TYPE') ldap_type,
>    'ENTERPRISE_IDENTITY    : ' || sys_context('USERENV','ENTERPRISE_IDENTITY') ent_id
> from v\$database;" | sqlplus -L jkstill_rad/radius@//rac19c-scan:1521/pdb1.jks.com

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 12 18:23:23 2020

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

Last Successful login time: Thu Mar 12 2020 18:08:08 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

JKSTILL_RAD@//rac19c-scan:1521/pdb1.jks.com > JKSTILL_RAD@//rac19c-scan:1521/pdb1.jks.com >   2    3    4    5    6    7    8
DB_NAME                : CDB
SESSION_USER           : JKSTILL_RAD
AUTHENTICATED_IDENTITY : JKSTILL_RAD
AUTHENTICATION_METHOD  : RADIUS
LDAP_SERVER_TYPE       :
ENTERPRISE_IDENTITY    : JKSTILL_RAD


1 row selected.

Success! If you have a need to use external authorization for your Oracle databases, the FreeRadius server is up to the task.

Really, this was not difficult to configure. The most difficult task is just locating the relevant documentation.

Later articles will detail how to add a database back-end and 2FA authentication to FreeRadius.

Categories: DBA Blogs

How to Read Oracle Traces from SQL*Plus

Wed, 2020-03-04 08:00

Let’s say you need to read trace files from Amazon Relational Database Service (RDS). How do you do it?

Here is an example of how to list and read those files using rdsadmin:

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
---------- ------------------------- ------------------------------------------------------------ -------------
SYS BDUMP /rdsdbdata/log/trace 0
SYS ADUMP /rdsdbdata/log/audit 0
SYS OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch 0
SYS OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch 0
SYS DATA_PUMP_DIR /rdsdbdata/datapump 0
SYS OPATCH_INST_DIR /rdsdbbin/oracle/OPatch 0
SYS TMP /rdsdbdata/userdirs/01 0

7 rows selected.


SQL> select * from table (rdsadmin.rds_file_util.read_text_file( p_directory => 'BDUMP', p_filename => 'trace/CPROD1_s003_81573.trc'));

From the trace file:
=========================
..................................................................................
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x000e289e.51da.47
Dump kdilk : itl=98, kdxlkflg=0x1 sdc=0 indexid=0x1c53db block=0x0019bdc0
(kdxlpu): purge leaf row
key :(24):
07 78 76 0c 17 17 09 32 08 c7 07 1a 02 28 15 01 18 06 00 1a 3c 99 00 1e

File 3 is not mirrored.

End dump previous blocks for kdsgrp
* kdsgrp1-2: ***********************************************
kdsDumpState: RID context dump

45511581 rows selected.



SQL> select * from table (rdsadmin.rds_file_util.listdir( p_directory => 'BDUMP')) order by mtime;

FILENAME TYPE FILESIZE MTIME
---------------------------------------- ---------- ---------- ---------
CPROD1_ora_48800.trm file 73 24-DEC-18
CPROD1_ora_48800.trc file 998 24-DEC-18
CPROD1_ora_86597.trc file 998 24-DEC-18
CPROD1_ora_86597.trm file 73 24-DEC-18
CPROD1_ora_7999.trc file 881 24-DEC-18
CPROD1_ora_7999.trm file 71 24-DEC-18
CPROD1_ora_7997.trm file 71 24-DEC-18
CPROD1_ora_7997.trc file 881 24-DEC-18
CPROD1_ora_8240.trm file 71 24-DEC-18
CPROD1_ora_8240.trc file 881 24-DEC-18
CPROD1_ora_8381.trm file 72 24-DEC-18
CPROD1_ora_8381.trc file 995 24-DEC-18
CPROD1_ora_8540.trc file 881 24-DEC-18
CPROD1_ora_8540.trm file 71 24-DEC-18
CPROD1_ora_9876.trc file 881 24-DEC-18
CPROD1_ora_9876.trm file 71 24-DEC-18
CPROD1_ora_11142.trm file 72 24-DEC-18
CPROD1_ora_11142.trc file 883 24-DEC-18
CPROD1_ora_11182.trc file 883 24-DEC-18
CPROD1_ora_11182.trm file 72 24-DEC-18
CPROD1_ora_55077.trm file 73 24-DEC-18
CPROD1_ora_55077.trc file 997 24-DEC-18
CPROD1_ora_92260.trm file 73 24-DEC-18
CPROD1_ora_92260.trc file 997 24-DEC-18
CPROD1_ora_123869.trc file 1000 24-DEC-18
CPROD1_ora_123869.trm file 74 24-DEC-18
CPROD1_ora_41305.trc file 998 24-DEC-18
CPROD1_ora_41305.trm file 73 24-DEC-18
CPROD1_j002_3293.trc file 114049 24-DEC-18
CPROD1_j002_3293.trm file 370 24-DEC-18
CPROD1_mmon_71739.trc file 7511332 24-DEC-18
CPROD1_mmon_71739.trm file 738330 24-DEC-18
CPROD1_ora_92888.trc file 997 24-DEC-18
CPROD1_ora_92888.trm file 73 24-DEC-18
trace/ directory 323584 24-DEC-18
alert_CPROD1.log file 204808 24-DEC-18
CPROD1_ora_70145.trc file 1470 24-DEC-18
CPROD1_ora_70145.trm file 109 24-DEC-18

3845 rows selected.



SQL> select * from table (rdsadmin.rds_file_util.listdir( p_directory => 'BDUMP')) where filename like '%CPROD1_s003_81573.trc%';

FILENAME TYPE FILESIZE MTIME
---------------------------------------- ---------- ---------- ---------
CPROD1_s003_81573.trc file 1948134047 23-DEC-18

We have, from 12.2 on, a different method for any platform, using Oracle views. This is the topic for our next post.

I hope it helps you today!

Categories: DBA Blogs

Use RMAN KEEP Backup To Roll Back From an Upgrade

Tue, 2020-02-25 09:42

There is a requirement to back up a database before an application upgrade to be able to rollback. Guarantee Restore Point (GRP) was first considered by us; however, it looks like there is insufficient space from Flash Recovery Area (FRA).

Further, the duration before the decision to rollback application upgrade is unknown, as it can be a week or a month. We made a decision to create RMAN KEEP UNTIL TIME backup to prevent deletion based on RETENTION POLICY.

Here is a demo showing how to perform a RMAN KEEP backup and to restore from a RMAN KEEP backup. Please note there is no standby configuration for this environment.

Please reference for standby environment Recovering Through the OPEN RESETLOGS Statement.

Perform RMAN KEEP backup:

[oracle@db-fs-1 ~]$ export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
[oracle@db-fs-1 ~]$ rman @ backup_keep.rman
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Feb 20 13:42:07 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN> spool log to rman_backup_keep_upgrade.log
2> connect target;
3> set echo on
4> show all;
5> run {
6> allocate channel c1 device type disk format '/u01/backup/%d_%I_%T_%U_UPGRADE_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
7> allocate channel c2 device type disk format '/u01/backup/%d_%I_%T_%U_UPGRADE_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
8> allocate channel c3 device type disk format '/u01/backup/%d_%I_%T_%U_UPGRADE_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
9> backup as compressed backupset incremental level 0
10> filesperset 1 check logical database tag='UPGRADE'
11> plus archivelog filesperset 8 tag='UPGRADE'
12> keep until time 'ADD_MONTHS(SYSDATE,6)';
13> }
14> run {
15> allocate channel d1 device type disk format '/u01/backup/CF_%d_%I_%T_%U_UPGRADE_%s';
16> backup current controlfile
17> keep until time 'ADD_MONTHS(SYSDATE,6)' tag='UPGRADE';
18> }
19> list backup of database summary tag='UPGRADE';
20> list backup of archivelog all summary tag='UPGRADE';
21> list backup of controlfile tag='UPGRADE';
22> list backup of spfile tag='UPGRADE';
23> report schema;
24> restore database validate preview from tag='UPGRADE';
25> exit
[oracle@db-fs-1 ~]$

Rename backup directory to prevent accidental deletion from RMAN:

[oracle@db-fs-1 ~]$ mv /u01/backup/ /u01/backup_keep

Review RMAN KEEP backup:

[oracle@db-fs-1 ~]$ ls -lrt /u01/backup_keep/
total 345040
-rw-r----- 1 oracle oinstall 1111552 Feb 20 13:42 HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17
-rw-r----- 1 oracle oinstall 4096 Feb 20 13:42 HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18
-rw-r----- 1 oracle oinstall 3042304 Feb 20 13:42 HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16
-rw-r----- 1 oracle oinstall 1138688 Feb 20 13:42 HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
-rw-r----- 1 oracle oinstall 1073152 Feb 20 13:42 HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
-rw-r----- 1 oracle oinstall 112328704 Feb 20 13:42 HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
-rw-r----- 1 oracle oinstall 213147648 Feb 20 13:42 HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
-rw-r----- 1 oracle oinstall 7168 Feb 20 13:42 HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23
-rw-r----- 1 oracle oinstall 10665984 Feb 20 13:42 CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25
-rw-r----- 1 oracle oinstall 8192 Feb 20 13:42 CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26
-rw-r----- 1 oracle oinstall 114688 Feb 20 13:42 CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27
-rw-r----- 1 oracle oinstall 10665984 Feb 20 13:42 CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28
[oracle@db-fs-1 ~]$

Delete RMAN KEEP backup to demonstrate backup is still safe:

[oracle@db-fs-1 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Feb 20 13:47:07 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: HAWK (DBID=3291536664)
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
13 B A A DISK 20-FEB-2020 13:41:06 1 1 YES UPGRADE
14 B A A DISK 20-FEB-2020 13:41:06 1 1 YES UPGRADE
15 B A A DISK 20-FEB-2020 13:42:09 1 1 YES UPGRADE
16 B A A DISK 20-FEB-2020 13:42:09 1 1 YES UPGRADE
17 B A A DISK 20-FEB-2020 13:42:09 1 1 YES UPGRADE
18 B 0 A DISK 20-FEB-2020 13:42:11 1 1 YES UPGRADE
19 B 0 A DISK 20-FEB-2020 13:42:14 1 1 YES UPGRADE
20 B 0 A DISK 20-FEB-2020 13:42:29 1 1 YES UPGRADE
21 B 0 A DISK 20-FEB-2020 13:42:34 1 1 YES UPGRADE
22 B A A DISK 20-FEB-2020 13:42:39 1 1 YES UPGRADE
23 B F A DISK 20-FEB-2020 13:42:40 1 1 NO TAG20200220T134240
24 B F A DISK 20-FEB-2020 13:42:43 1 1 NO UPGRADE
25 B A A DISK 20-FEB-2020 13:42:44 1 1 NO UPGRADE
26 B F A DISK 20-FEB-2020 13:42:45 1 1 NO UPGRADE
27 B F A DISK 20-FEB-2020 13:42:47 1 1 NO UPGRADE
RMAN> list backupset 13;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
13 1.06M DISK 00:00:00 20-FEB-2020 13:41:06
BP Key: 13 Status: AVAILABLE Compressed: YES Tag: UPGRADE
Piece Name: /u01/backup/HAWK_3291536664_20200220_0fup0qri_1_1_UPGRADE_15
Keep: BACKUP_LOGS Until: 20-AUG-2020 13:41:06
List of Archived Logs in backup set 13
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 2 1527374 20-FEB-2020 13:37:48 1528069 20-FEB-2020 13:41:06
RMAN> delete force noprompt backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
13 13 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0fup0qri_1_1_UPGRADE_15
14 14 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0eup0qri_1_1_UPGRADE_14
15 15 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17
16 16 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18
17 17 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16
18 18 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
19 19 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
20 20 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
21 21 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
22 22 1 1 AVAILABLE DISK /u01/backup/HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23
23 23 1 1 AVAILABLE DISK /u02/oradata/HAWK/autobackup/2020_02_20/o1_mf_s_1032874960_h4wzp07t_.bkp
24 24 1 1 AVAILABLE DISK /u01/backup/CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25
25 25 1 1 AVAILABLE DISK /u01/backup/CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26
26 26 1 1 AVAILABLE DISK /u01/backup/CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27
27 27 1 1 AVAILABLE DISK /u01/backup/CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0fup0qri_1_1_UPGRADE_15 RECID=13 STAMP=1032874866
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0eup0qri_1_1_UPGRADE_14 RECID=14 STAMP=1032874866
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17 RECID=15 STAMP=1032874929
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18 RECID=16 STAMP=1032874929
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16 RECID=17 STAMP=1032874929
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21 RECID=18 STAMP=1032874930
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22 RECID=19 STAMP=1032874933
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20 RECID=20 STAMP=1032874930
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19 RECID=21 STAMP=1032874930
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23 RECID=22 STAMP=1032874959
deleted backup piece
backup piece handle=/u02/oradata/HAWK/autobackup/2020_02_20/o1_mf_s_1032874960_h4wzp07t_.bkp RECID=23 STAMP=1032874960
deleted backup piece
backup piece handle=/u01/backup/CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25 RECID=24 STAMP=1032874963
deleted backup piece
backup piece handle=/u01/backup/CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26 RECID=25 STAMP=1032874964
deleted backup piece
backup piece handle=/u01/backup/CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27 RECID=26 STAMP=1032874965
deleted backup piece
backup piece handle=/u01/backup/CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28 RECID=27 STAMP=1032874967
Deleted 15 objects
RMAN> exit
Recovery Manager complete.
[oracle@db-fs-1 ~]$
[oracle@db-fs-1 ~]$ date; ls -lrt /u01/backup_keep/
Thu Feb 20 13:49:32 CET 2020
total 345040
-rw-r----- 1 oracle oinstall 1111552 Feb 20 13:42 HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17
-rw-r----- 1 oracle oinstall 4096 Feb 20 13:42 HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18
-rw-r----- 1 oracle oinstall 3042304 Feb 20 13:42 HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16
-rw-r----- 1 oracle oinstall 1138688 Feb 20 13:42 HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
-rw-r----- 1 oracle oinstall 1073152 Feb 20 13:42 HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
-rw-r----- 1 oracle oinstall 112328704 Feb 20 13:42 HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
-rw-r----- 1 oracle oinstall 213147648 Feb 20 13:42 HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
-rw-r----- 1 oracle oinstall 7168 Feb 20 13:42 HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23
-rw-r----- 1 oracle oinstall 10665984 Feb 20 13:42 CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25
-rw-r----- 1 oracle oinstall 8192 Feb 20 13:42 CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26
-rw-r----- 1 oracle oinstall 114688 Feb 20 13:42 CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27
-rw-r----- 1 oracle oinstall 10665984 Feb 20 13:42 CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28
[oracle@db-fs-1 ~]$

Create table after backup as a marker and table should not exist after restore from RMAN KEEP backup:

13:50:21 SYS @ HAWK:HAWK:>create table t as select name from v$database;
Table created.
13:50:54 SYS @ HAWK:HAWK:>desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(9)

Shutdown database:

13:50:59 SYS @ HAWK:HAWK:>shu abort;
ORACLE instance shut down.
13:51:09 SYS @ HAWK:HAWK:>exit

Perform RESTORE from RMAN KEEP backup:

[oracle@db-fs-1 ~]$ export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
[oracle@db-fs-1 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Feb 20 13:51:47 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)

Startup database:

RMAN> startup force nomount;
Oracle instance started
Total System Global Area 805306368 bytes
Fixed Size 8625856 bytes
Variable Size 314573120 bytes
Database Buffers 473956352 bytes
Redo Buffers 8151040 bytes

Restore controlfile:

RMAN> restore controlfile from '/u01/backup_keep/CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28';
Starting restore at 20-FEB-2020 13:53:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=256 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/fra/HAWK/controlfile/o1_mf_h4vvt81n_.ctl
Finished restore at 20-FEB-2020 13:53:05

Mount database:

RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1

Catalog backup:

RMAN> catalog start with '/u01/backup_keep' noprompt;
Starting implicit crosscheck backup at 20-FEB-2020 13:53:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 20-FEB-2020 13:53:49
Starting implicit crosscheck copy at 20-FEB-2020 13:53:49
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-FEB-2020 13:53:49
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/backup_keep
List of Files Unknown to the Database
=====================================
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0iup0qth_1_1_UPGRADE_18
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0hup0qth_1_1_UPGRADE_17
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0gup0qth_1_1_UPGRADE_16
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0sup0qum_1_1_UPGRADE_28
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0qup0quk_1_1_UPGRADE_26
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0pup0qui_1_1_UPGRADE_25
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0nup0quf_1_1_UPGRADE_23
File Name: /u01/backup_keep/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
File Name: /u01/backup_keep/CF_HAWK_3291536664_20200220_0rup0qul_1_1_UPGRADE_27

Preview restore to determine restore point:

RMAN> restore database preview summary from tag='UPGRADE';
Starting restore at 20-FEB-2020 13:54:24
using channel ORA_DISK_1
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
21 B 0 A DISK 20-FEB-2020 13:42:34 1 1 YES UPGRADE
20 B 0 A DISK 20-FEB-2020 13:42:29 1 1 YES UPGRADE
18 B 0 A DISK 20-FEB-2020 13:42:11 1 1 YES UPGRADE
19 B 0 A DISK 20-FEB-2020 13:42:14 1 1 YES UPGRADE
List of Archived Log Copies for database with db_unique_name HAWK
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - --------------------
8 1 4 A 20-FEB-2020 13:42:08
Name: /u02/oradata/HAWK/archivelog/2020_02_20/o1_mf_1_4_h4wzoywg_.arc
9 1 5 A 20-FEB-2020 13:42:38
Name: /u02/oradata/HAWK/archivelog/2020_02_20/o1_mf_1_5_h4wzp43z_.arc
recovery will be done up to SCN 1528156
Media recovery start SCN is 1528156
Recovery must be done beyond SCN 1528160 to clear datafile fuzziness
Finished restore at 20-FEB-2020 13:54:24

Restore database:

RMAN> restore database from tag='UPGRADE';
Starting restore at 20-FEB-2020 13:54:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/HAWK/datafile/o1_mf_undotbs1_h4vxh5k4_.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup_keep/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21
channel ORA_DISK_1: piece handle=/u01/backup_keep/HAWK_3291536664_20200220_0lup0qti_1_1_UPGRADE_21 tag=UPGRADE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u02/oradata/HAWK/datafile/o1_mf_users_h4vxh8lx_.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup_keep/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22
channel ORA_DISK_1: piece handle=/u01/backup_keep/HAWK_3291536664_20200220_0mup0qtl_1_1_UPGRADE_22 tag=UPGRADE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/HAWK/datafile/o1_mf_sysaux_h4vxh9n4_.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup_keep/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20
channel ORA_DISK_1: piece handle=/u01/backup_keep/HAWK_3291536664_20200220_0kup0qti_1_1_UPGRADE_20 tag=UPGRADE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/oradata/HAWK/datafile/o1_mf_system_h4vxhroh_.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup_keep/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19
channel ORA_DISK_1: piece handle=/u01/backup_keep/HAWK_3291536664_20200220_0jup0qti_1_1_UPGRADE_19 tag=UPGRADE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 20-FEB-2020 13:55:18

Recover database:

RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 HAWK 3291536664 PARENT 1 26-JAN-2017 13:52:29
2 2 HAWK 3291536664 PARENT 1408558 20-FEB-2020 03:30:34
3 3 HAWK 3291536664 CURRENT 1422706 20-FEB-2020 04:00:39
RMAN> recover database until scn 1528161;
Starting recover at 20-FEB-2020 13:55:57
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /u02/oradata/HAWK/archivelog/2020_02_20/o1_mf_1_4_h4wzoywg_.arc
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_20/o1_mf_1_4_h4wzoywg_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 20-FEB-2020 13:55:58

Open database:

RMAN> alter database open resetlogs;
Statement processed

Review databases:

RMAN> report schema;
Report of database schema for database with db_unique_name HAWK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 800 SYSTEM YES /u02/oradata/HAWK/datafile/o1_mf_system_h4vxhroh_.dbf
3 470 SYSAUX NO /u02/oradata/HAWK/datafile/o1_mf_sysaux_h4vxh9n4_.dbf
4 70 UNDOTBS1 YES /u02/oradata/HAWK/datafile/o1_mf_undotbs1_h4vxh5k4_.dbf
7 5 USERS NO /u02/oradata/HAWK/datafile/o1_mf_users_h4vxh8lx_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u02/oradata/HAWK/datafile/o1_mf_temp_h4vxlyl7_.tmp
RMAN> exit
Recovery Manager complete.
[oracle@db-fs-1 ~]$

Verify restore to confirm table created after backup does not exists:

13:57:34 SYS @ HAWK:HAWK:>desc t;
ERROR:
ORA-04043: object t does not exist
13:57:53 SYS @ HAWK:HAWK:>

In conclusion, RMAN KEEP backup is a good alternative to rollback changes when Guarantee Restore Point is not available.

 

Categories: DBA Blogs

PART 4: Implementing Oracle Database Single Sign-on Using Kerberos, Active Directory, and Oracle CMU

Mon, 2020-02-24 08:06

This is the fourth and final article in a four-part series related to testing Oracle Database 18c Centrally Managed Users (CMU) by leveraging the Oracle Cloud Infrastructure (OCI) for Oracle DBAs to create a lab or testbed. The third article covered Implementing Oracle Database Active Directory Password Synchronization Using Oracle CMU.

The second common configuration option that Oracle customers are often interested in is a true single sign-on option for Oracle Database-based applications.

For clarity, single sign-on really means that a new credential prompt is not required to sign into an application. Simply put, a user might provide a credential to log into their desktop (i.e. a Windows Active Directory username and password). That sign-on process results in a token being issued and cached by the OS, and then that token is used to authenticate access for other resources such as network drives or applications.

External reference: https://en.wikipedia.org/wiki/Single_sign-on

For Oracle Database, Kerberos is a common authentication token-based system that can be leveraged with Centrally Managed Users (CMU) to provide a single sign-on experience.

There are a few minor catches however:

  1. The logon process must generate and cache a Kerberos ticket-granting ticket (TGT) that the Oracle software stack can use for authentication requests.
  2. The application must support and be able to pass empty string credentials. If the application throws an error if a NULL username or password is provided, then this will present a problem. For testing, we’ll use SQLPlus so this won’t be an issue for this proof of concept.
Active Directory One-time Configuration Steps

In this test environment, Active Directory is the Kerberos Authentication server. And generally, Active Directory and most Windows and Linux servers (including the Oracle Linux 7.7 images used in this testbed) should have Kerberos Version 5 already installed – if not, it needs to be added.

In Kerberos, users are known as or referred to as “principals”. The “service principal” is named in the format:

kservice/kinstance@REALM

For Oracle and CMU it is typically:

oracle/<DB Server FQDN>@<domain>

And therefore, for this example, the actual principal name will be:

oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET

For the CMU setup, we could make service principals in Active Directory for each Oracle database server (typically) or one common one if preferred.

To add the necessary principal (aka “user”) to Active Directory we could use the “Active Directory Users and Computers” GUI or, once again, just use a simple PowerShell command run from the Domain Controller DC1 such as:

New-ADUser `
   -Name "dbserv1" `
   -UserPrincipalName "dbserv1.ad1.cmuvnc.oraclevcn.com@stagecoach.net" `
   -DisplayName "dbserv1.ad1.cmuvnc.oraclevcn.com" `
   -Description "ServiceOracle Kerberos Service Principal." `
   -Path "CN=Managed Service Accounts,DC=stagecoach,DC=net" `
   -ChangePasswordAtLogon $false `
   -PasswordNeverExpires $true `
   -CannotChangePassword $true `
   -Enabled $true `
   -AccountPassword(Read-Host -AsSecureString "Initial Password:")

Again, putting the service account user in the “Managed Service Accounts” folder in Active Directory is a preference to reduce clutter – it is in no way mandatory.

And again we can do a simple PowerShell query to confirm that the user was created successfully:

dsquery user -name dbserv1

Sample output:

PS C:\Users\Administrator> dsquery user -name dbserv1
"CN=dbserv1,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET"
PS C:\Users\Administrator>

Or via “Active Directory Users and Computers” if preferable:

Before we’re done with the prerequisite AD setup, we need to extract and copy the “service key table” for the Kerberos Principal we just added.

Still running commands from the Windows Domain Controller DC1, extract the service key table for Kerberos using the Windows ktpass utility. For example:

ktpass.exe -princ oracle/<DB Server FQDN>@<domain> `
   -mapuser <DB Server FQDN>@<domain> `
   -crypto all `
   -pass * `
   -out .\$Env:COMPUTERNAME.keytab

The “*” is used to prompt for the Service Principal’s password instead of hard coding it in the command.

Sample output:

PS C:\Users\Administrator> ktpass.exe -princ oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET `
>>    -mapuser dbserv1.ad1.cmuvnc.oraclevcn.com `
>>    -crypto all `
>>    -pass * `
>>    -out .\$Env:COMPUTERNAME.keytab
Targeting domain controller: DC1.STAGECOACH.NET
Successfully mapped oracle/dbserv1.ad1.cmuvnc.oraclevcn.com to dbserv1.
Type the password for oracle/dbserv1.ad1.cmuvnc.oraclevcn.com:
Type the password again to confirm:
Password successfully set!
WARNING: pType and account type do not match. This might cause problems.
Key created.
Key created.
Key created.
Key created.
Key created.
Output keytab to .\DC1.keytab:
Keytab version: 0x502
keysize 81 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x1 (DES-CBC-CRC) keylength 8 (0x7f0ecde9d02397a2)
keysize 81 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x3 (DES-CBC-MD5) keylength 8 (0x7f0ecde9d02397a2)
keysize 89 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x17 (RC4-HMAC)
keylength 16 (0x8b2318524d2e3e2e31885afc21024cf5)
keysize 105 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x12 (AES256-SHA1) keylength 32 (0x536fa1677dd224d7510d81b86d74a602ee7fc25902a7969ed3a98ac546a88cb8)
keysize 89 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x11 (AES128-SHA1) keylength 16 (0x7d8600b56ca085d86e933888aea389ae)
PS C:\Users\Administrator>

Lastly, copy the exported keytable file to the Database Server (DBSERV1). For example, using scp from the Active Directory domain controller:

scp .\$Env:computername.keytab opc@10.0.1.101:/tmp/

Sample output:

PS C:\Users\Administrator> scp .\$Env:computername.keytab opc@10.0.1.101:/tmp/
DC1.keytab                                                                            100%  467     0.5KB/s   00:00
PS C:\Users\Administrator>

That should conclude the required prerequisite setup on the Active Directory domain controller. Unlike with password-authenticated CMU implementations, this time we did not need to extend the Active Directory schema, install any additional software, or reboot our domain controllers.

Database Home One-time Configuration Steps

Like with password-based authentication and directory synchronization, some one-time setup in the database and the database home is required (as the “oracle” OS user).

First, we need to ensure that a few relevant database parameters are not set:

. oraenv <<< XE

echo "
show parameter os_authent_prefix
show parameter remote_os_authent
" | sqlplus -s / as sysdba

Likely, at least OS_AUTHENT_PREFIX is set to a default value of “ops$”, and, hence, needs to be adjusted. Since it is not a dynamic parameter, a database restart must be included for parameter changes to come into effect:

. oraenv <<< XE

echo "
alter system set os_authent_prefix='' scope=spfile;
alter system reset remote_os_authent;
shutdown immediate
startup
" | sqlplus -s / as sysdba

Next, the SQLNET.ORA file on the database server must be adjusted to provide the relevant Kerberos settings including where to access the copied key table file.

The SQLNET.AUTHENTICATION_KERBEROS5_SERVICE parameter refers to the ‘oracle/‘ part added to the server principal name when ktpass.exe was run.

The SQLNET.KERBEROS5_CONF points to a location for a configuration file while SQLNET.KERBEROS5_KEYTAB is the key table file copied from the domain controller.

Hence, the required SQLNET.ORA file updates becomes:

. oraenv <<< XE

mkdir -p ${ORACLE_HOME}/network/admin/kerberos
cp /tmp/*.keytab ${ORACLE_HOME}/network/admin/kerberos/keytab
oklist -k -t -old ${ORACLE_HOME}/network/admin/kerberos/keytab

echo "
SQLNET.KERBEROS5_KEYTAB=${ORACLE_HOME}/network/admin/kerberos/keytab
SQLNET.KERBEROS5_CONF=${ORACLE_HOME}/network/admin/kerberos/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)
" >> ${ORACLE_HOME}/network/admin/sqlnet.ora

Other optional and related parameters (including a backup authentication method) can be included if required – refer to the Oracle CMU documentation.

The last part of the setup is to configure the aforementioned configuration file (still using the sample domain “STAGECOACH.NET” – update as required):

cat <<EOT >> ${ORACLE_HOME}/network/admin/kerberos/krb5.conf
[libdefaults]
default_realm = STAGECOACH.NET

[realms]
STAGECOACH.NET = {
  kdc = DC1.STAGECOACH.NET:88
}

[domain_realm]
.`dnsdomainname` = STAGECOACH.NET
`dnsdomainname` = STAGECOACH.NET
EOT

cat ${ORACLE_HOME}/network/admin/kerberos/krb5.conf

And when testing from the local database server, we need to ensure that SQLNET.WALLET_OVERRIDE=TRUE is not set or is commented out:

sed -i.bak '/^SQLNET.WALLET_OVERRIDE/ s/^SQLNET.WALLET_OVERRIDE/\#SQLNET.WALLET_OVERRIDE/' ${ORACLE_HOME}/network/admin/sqlnet.ora

grep SQLNET.WALLET_OVERRIDE ${ORACLE_HOME}/network/admin/sqlnet.ora
Creating Database Users to use Kerberos Authentication

Again, using the “Simon” test Active Directory user created previously, we can create an IDENTIFIED EXTERNALLY database user. For example:

echo "
alter session set container=XEPDB1;
create user \"SIMON@STAGECOACH.NET\" identified externally;
grant create session to \"SIMON@STAGECOACH.NET\";
grant select on v_\$database to \"SIMON@STAGECOACH.NET\";
" | sqlplus -s / as sysdba

To perform an initial test, from the database server DBSERV1, we need to manually obtain the TGT since we did log into the server with an Active Directory session to automatically obtain the TGT. To obtain tickets manually, we can use the okinit and oklist utilities (Oracle-specific versions of standard Kerberos utilities kinit and klist) which are provided in the Oracle Home:

okinit <Active Directory User>
oklist

Sample output:

[oracle@dbserv1 ~]$ okinit simon

Kerberos Utilities for Linux: Version 18.0.0.0.0 - Production on 26-NOV-2019 18:52:34

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

Configuration file : /opt/oracle/product/18c/dbhomeXE/network/admin/kerberos/krb5.conf.
Password for simon@STAGECOACH.NET:
[oracle@dbserv1 ~]$ oklist

Kerberos Utilities for Linux: Version 18.0.0.0.0 - Production on 26-NOV-2019 18:52:45

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

Configuration file : /opt/oracle/product/18c/dbhomeXE/network/admin/kerberos/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: simon@STAGECOACH.NET

Valid starting     Expires            Service principal
11/26/19 18:52:39  11/27/19 04:52:39  krbtgt/STAGECOACH.NET@STAGECOACH.NET
        renew until 11/27/19 18:52:34
[oracle@dbserv1 ~]$

By running those commands manually, we obtained the Kerberos TGT for the “simon@strategicdbs.com” user without logging into the OS as that user.

Of course, this is a temporary measure since we are, at this point, testing from the database server while logged in using a different OS user than the one we want to use to connect to the database. When we test from the Windows and Linux application servers, the experience should be seamless, meaning not needing to manually run these commands to obtain the required ticket.

If the okinit command fails, it may be because of a firewall or DNS resolution issue. In lieu of configuring a separate DNS environment, for testing purposes, it’s easiest to simply update /etc/hosts file as the “root” user:

echo "10.0.1.100 DC1.STAGECOACH.net DC1" >> /etc/hosts

Once the TGT is obtained, we can test the full connection and authentication. In this example we provide no username and password in the connection string – instead the Oracle client is relying on the Kerberos ticket:

echo "
set heading off
select 'DB_NAME (from v\$database) : '||name,
       'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
       'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
       'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
       'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
       'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
  from v\$database;
" | sqlplus -s /@ORCL

Sample output:

[oracle@dbserv1 ~]$ echo "
> set heading off
> select 'DB_NAME (from v\$database) : '||name,
>        'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
>        'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
>        'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
>        'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
>        'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
>        'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
>   from v\$database;
> " | sqlplus -s /@ORCL

DB_NAME (from v$database) : XE
SESSION_USER              : SIMON@STAGECOACH.NET
AUTHENTICATED_IDENTITY    : simon@STAGECOACH.NET
AUTHENTICATION_METHOD     : KERBEROS
AUTHENTICATION_TYPE       : NETWORK
LDAP_SERVER_TYPE          :
ENTERPRISE_IDENTITY       : simon@STAGECOACH.NET


[oracle@dbserv1 ~]$

From the above we can see that everything worked as expected as the authentication type was “NETWORK” and the authentication method “KERBEROS”. But this test was from the actual database server. A more realistic scenario requires testing from the Linux and Windows application servers.

Client Setup on Linux and Testing with a Domain User

Unfortunately, when testing with Kerberos authentication, additional Oracle client software setup is required:

  1. The Oracle Instant Client will no longer suffice – we must now do a proper Oracle Client installation though we can minimize the installed components.
  2. The client software’s SQLNET.ORA file must be adjusted to include Kerberos-related parameters.
  3. A Kerberos configuration file must be created.

These are all additional setup steps required on the client software end which were not required for CMU password-based authentication.

And like with the XE database software, the full Oracle Client media requires an authenticated download and, hence, cannot be done in an elegant programmatic command. Therefore, manually download the Oracle Client software and transfer to the application servers.

On the Linux, application server, first prepare the server for the Oracle software. As “root”:

# Quick shortcut: use the DB pre-install RPM as a quick method to install required dependencies:
yum install -y oracle-database-preinstall-19c

mkdir -p /u01/app/oracle/product/19.0.0/client_1
mkdir -p /u01/app/oraInventory
mkdir -p /u01/app/oracle_software
chown -R oracle:oinstall /u01
chmod -R 775 /u01
 
cat <<EOT >> /home/oracle/.bash_profile
   
#  Oracle RDBMS Settings:
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\${ORACLE_BASE}/product/19.0.0/client_1
export PATH=\${ORACLE_HOME}/bin:\${PATH}
EOT

Then as the “oracle” user install the full client using a customized response file for reusability (if required):

# Assumes that the client ZIP file has been installed into the current working directory:
unzip LINUX.X64_193000_client.zip -d /u01/app/oracle_software/

cp /u01/app/oracle_software/client/response/client_install.rsp ~/client_install.rsp

sed -i '/^oracle.install.client.installType/ s~oracle.install.client.installType=$~oracle.install.client.installType=Custom~' ~/client_install.rsp
sed -i '/^oracle.install.client.customComponents/ s~oracle.install.client.customComponents=$~oracle.install.client.customComponents="oracle.sqlplus:19.0.0.0.0","oracle.rdbms.oci:19.0.0.0.0","oracle.network.aso:19.0.0.0.0","oracle.network.client:19.0.0.0.0"~' ~/client_install.rsp
sed -i '/^UNIX_GROUP_NAME/ s~UNIX_GROUP_NAME=$~UNIX_GROUP_NAME=oinstall~' ~/client_install.rsp
sed -i '/^INVENTORY_LOCATION/ s~INVENTORY_LOCATION=$~INVENTORY_LOCATION=/u01/app/oraInventory~' ~/client_install.rsp
sed -i '/^ORACLE_HOME/ s~ORACLE_HOME=$~ORACLE_HOME='${ORACLE_HOME}'~' ~/client_install.rsp
sed -i '/^ORACLE_BASE/ s~ORACLE_BASE=$~ORACLE_BASE='${ORACLE_BASE}'~' ~/client_install.rsp

diff /u01/app/oracle_software/client/response/client_install.rsp ~/client_install.rsp

/u01/app/oracle_software/client/runInstaller -silent -waitforcompletion -responseFile ~/client_install.rsp

Once the client installation is complete, run the required root script as “root”:

/u01/app/oraInventory/orainstRoot.sh

Reverting back to the “oracle” user, again the required Kerberos parameters must be added to the SQLNET.ORA file:

mkdir -p ${ORACLE_HOME}/network/admin/kerberos

echo "
SQLNET.KERBEROS5_CONF=${ORACLE_HOME}/network/admin/kerberos/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)
" >> ${ORACLE_HOME}/network/admin/sqlnet.ora

cat ${ORACLE_HOME}/network/admin/sqlnet.ora

And of course the Kerberos configuration file must be created:

cat <<EOT >> ${ORACLE_HOME}/network/admin/kerberos/krb5.conf
[libdefaults]
default_realm = STAGECOACH.NET

[realms]
STAGECOACH.NET = {
  kdc = DC1.STAGECOACH.NET:88
}

[domain_realm]
.`dnsdomainname` = STAGECOACH.NET
`dnsdomainname` = STAGECOACH.NET
EOT

cat ${ORACLE_HOME}/network/admin/kerberos/krb5.conf

That should be all of the required Linux Client setup completed. And this point, we can test using the Active Directory “Simon” user.

From any machine, log into the Linux Application server as the test AD domain user. For example:

ssh "simon@stagecoach.net"@10.0.1.102

To truly achieve single sign-on, we don’t want to have to manually run additional commands such as okinit used earlier. Instead we want to simply log into the OS and that’s all. Consequently, it is essential to validate that we have a Kerberos ticket stored in a FILE credential cache that the Oracle Client software can use using the Linux klist command. We can easily confirm by just running the Linux klist command.

Example output:

[simon@STAGECOACH.NET@applinux1 ~]$ klist
Ticket cache: FILE:/tmp/krb5cc_747601111
Default principal: simon@STAGECOACH.NET

Valid starting       Expires              Service principal
11/27/2019 01:09:41  11/27/2019 11:09:41  krbtgt/STAGECOACH.NET@STAGECOACH.NET
        renew until 12/04/2019 01:09:41
[simon@STAGECOACH.NET@applinux1 ~]$

If the output from klist shows “KEYRING”, we need to adjust /etc/krb5.conf and restart the sssd service as per the steps described in Part 2 of this article series.

To use SQLPlus for an actual connection test, we might need to manually set our ORACLE_HOME variable and adjust our PATH variable for the test user since we never configured .bash_profile or any other environment configuration files:

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/client_1
export PATH=${ORACLE_HOME}/bin:${PATH}

Then we can perform a simple test:

echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

And the connection should succeed. For example:

[simon@STAGECOACH.NET@applinux1 ~]$ echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1
USER is "SIMON@STAGECOACH.NET"
[simon@STAGECOACH.NET@applinux1 ~]$

Going a step further with a more elaborate test showing additional connection property details:

echo "
set heading off
select 'DB_NAME (from v\$database) : '||name,
       'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
       'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
       'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
       'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
       'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
  from v\$database;
" | sqlplus -s -L /@//dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

Sample output:

[simon@STAGECOACH.NET@applinux1 ~]$ echo "
> set heading off
> select 'DB_NAME (from v\$database) : '||name,
>        'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
>        'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
>        'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
>        'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
>        'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
>        'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
>   from v\$database;
> " | sqlplus -s -L /@//dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

DB_NAME (from v$database) : XE
SESSION_USER              : SIMON@STAGECOACH.NET
AUTHENTICATED_IDENTITY    : simon@STAGECOACH.NET
AUTHENTICATION_METHOD     : KERBEROS
AUTHENTICATION_TYPE       : NETWORK
LDAP_SERVER_TYPE          :
ENTERPRISE_IDENTITY       : simon@STAGECOACH.NET


[simon@STAGECOACH.NET@applinux1 ~]$
Client Setup on Windows and Testing with a Domain User

Similarly, on the Windows application server APPWIN1, the Oracle Instant Client will no longer suffice and we must instead install the full Oracle Client. Assuming that the required media has been manually downloaded and transferred to the APPWIN1 server, it can be installed from a PowerShell window (logged in as the Administrator user) using commands such as:

# Assumes that the client ZIP file has been installed into the current working directory:
expand-archive -path '.\WINDOWS.X64_193000_client.zip' -destinationpath $Env:TEMP

cp $Env:TEMP\client\response\client_install.rsp $HOME\Desktop\client_install.txt

cat $HOME\Desktop\client_install.txt | %{$_ -replace "oracle.install.client.installType=$","oracle.install.client.installType=Custom"} > $HOME\Desktop\client_install.tmp
mv -force $HOME\Desktop\client_install.tmp $HOME\Desktop\client_install.txt

cat $HOME\Desktop\client_install.txt | %{$_ -replace "oracle.install.client.customComponents=$","oracle.install.client.customComponents=oracle.sqlplus:19.0.0.0.0,oracle.rdbms.oci:19.0.0.0.0,oracle.network.aso:19.0.0.0.0,oracle.network.client:19.0.0.0.0"} > $HOME\Desktop\client_install.tmp
mv -force $HOME\Desktop\client_install.tmp $HOME\Desktop\client_install.txt

cat $HOME\Desktop\client_install.txt | %{$_ -replace "ORACLE_HOME=$","ORACLE_HOME=C:\Oracle\product\19.0.0\client_1"} > $HOME\Desktop\client_install.tmp
mv -force $HOME\Desktop\client_install.tmp $HOME\Desktop\client_install.txt

cat $HOME\Desktop\client_install.txt | %{$_ -replace "ORACLE_BASE=$","ORACLE_BASE=C:\Oracle"} > $HOME\Desktop\client_install.tmp
mv -force $HOME\Desktop\client_install.tmp $HOME\Desktop\client_install.txt

cat $HOME\Desktop\client_install.txt | %{$_ -replace "oracle.install.IsBuiltInAccount=$","oracle.install.IsBuiltInAccount=true"} > $HOME\Desktop\client_install.tmp
mv -force $HOME\Desktop\client_install.tmp $HOME\Desktop\client_install.txt

Get-Content .\client_install.txt | out-file -encoding ASCII .\client_install.rsp
diff (cat $Env:TEMP\client\response\client_install.rsp) (cat $HOME\Desktop\client_install.rsp)

# Run installer using response file:
& "$Env:TEMP\client\setup.exe" -silent -waitforcompletion -responseFile $HOME\Desktop\client_install.rsp

After installation completes, add the necessary Kerberos parameters to the SQLNET.ORA file:

$ORACLE_HOME="C:\Oracle\product\19.0.0\client_1"
mkdir "$ORACLE_HOME\network\admin\kerberos"

echo @"
SQLNET.KERBEROS5_CC_NAME=MSLSA:
SQLNET.KERBEROS5_CONF=$ORACLE_HOME\network\admin\kerberos\krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)
"@ | Add-Content "$ORACLE_HOME\network\admin\sqlnet.ora" -Encoding ASCII

cat "$ORACLE_HOME\network\admin\sqlnet.ora"

IMPORTANT: Note that the trailing “:” after the word MSLSA is required and is sometimes missing from Oracle documentation.

Then build the required Kerberos configuration file (note the hardcoded environment-specific values in the command – adjust as required):

echo @"
[libdefaults]
default_realm = STAGECOACH.NET

[realms]
STAGECOACH.NET = {
  kdc = DC1.STAGECOACH.NET:88
}

[domain_realm]
.ad1.cmuvnc.oraclevcn.com = STAGECOACH.NET
ad1.cmuvnc.oraclevcn.com = STAGECOACH.NET
"@ | Out-File "$ORACLE_HOME\network\admin\kerberos\krb5.conf" -Encoding ASCII

cat $ORACLE_HOME\network\admin\kerberos\krb5.conf

At this point, log out and back into the APPWIN1 server as the test user. In this case, logging into the APPWIN1 server as STAGECOACH\simon.

Then on a new PowerShell window and try connecting using a simple test command:

echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

At this point, you might be presented with a common error:

PS C:\Users\Simon> echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1
ERROR:
ORA-12638: Credential retrieval failed


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
PS C:\Users\Simon>

ORA-12638 is a non-specific “catch-all” error message. For troubleshooting and tracing Kerberos connection problems see: Kerberos Troubleshooting Guide (Doc ID 185897.1)

In this case, the error is due to an Oracle bug related to the Kerberos ticket being issued with the “forwardable” flag enabled as evident by running oklist with the -f argument:

PS C:\Users\Simon> oklist -f

Kerberos Utilities for 64-bit Windows: Version 19.0.0.0.0 - Production on 26-NOV-2019 23:51:54

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : C:\Oracle\product\19.0.0\client_1\network\admin\kerberos\krb5.conf.
Ticket cache: MSLSA:
Default principal: simon@STAGECOACH.NET

Valid starting     Expires            Service principal
11/26/19 23:42:51  11/27/19 09:42:38  oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET
        renew until 12/03/19 23:42:38, Flags: FRA
11/26/19 23:42:38  11/27/19 09:42:38  LDAP/DC1.STAGECOACH.NET/STAGECOACH.NET@STAGECOACH.NET
        renew until 12/03/19 23:42:38, Flags: FRAO
PS C:\Users\Simon>

Notice: “Flags: FRA“.

This can be resolved by logging in as the domain Administrator on the Active Directory domain controller DC1. If adjusting using the GUI utility “Active Directory Users and Computers”, check the “Account is sensitive and cannot be delegated” checkbox – it is unchecked by default:

Or use a PowerShell window and the following commands to verify the setting and adjust it:

# Check setting - default value is "False":
Get-ADUser -Identity <Domain User> -Properties AccountNotDelegated

# Adjust setting:
Set-ADAccountControl -AccountNotDelegated $True -Identity <Domain User>

# Validate that the change was made:
Get-ADUser -Identity <Domain User> -Properties AccountNotDelegated

Sample output:

PS C:\Users\Administrator> Get-ADUser -Identity simon -Properties AccountNotDelegated


AccountNotDelegated : False
DistinguishedName   : CN=Simon Pane,CN=Users,DC=STAGECOACH,DC=NET
Enabled             : True
GivenName           : Simon
Name                : Simon Pane
ObjectClass         : user
ObjectGUID          : 7d925663-3d81-46be-be78-95618550f2dc
SamAccountName      : simon
SID                 : S-1-5-21-4278349699-2454951225-2679278977-1113
Surname             : Pane
UserPrincipalName   : simon@STAGECOACH.NET



PS C:\Users\Administrator> Set-ADAccountControl -AccountNotDelegated $True -Identity simon
PS C:\Users\Administrator>
PS C:\Users\Administrator> Get-ADUser -Identity simon -Properties AccountNotDelegated


AccountNotDelegated : True
DistinguishedName   : CN=Simon Pane,CN=Users,DC=STAGECOACH,DC=NET
Enabled             : True
GivenName           : Simon
Name                : Simon Pane
ObjectClass         : user
ObjectGUID          : 7d925663-3d81-46be-be78-95618550f2dc
SamAccountName      : simon
SID                 : S-1-5-21-4278349699-2454951225-2679278977-1113
Surname             : Pane
UserPrincipalName   : simon@STAGECOACH.NET



PS C:\Users\Administrator>

Credit for identifying and resolving this specific issue goes to: https://www.spotonoracle.com/?p=451 . For additional details on this option and the meaning and implications see: https://blogs.technet.microsoft.com/poshchap/2015/05/01/security-focus-analysing-account-is-sensitive-and-cannot-be-delegated-for-privileged-accounts/

After logging out and back in again (still as the STAGECOACH\simon domain user), the “FRA” flag should no longer be present:

PS C:\Users\Simon> oklist -f

Kerberos Utilities for 64-bit Windows: Version 19.0.0.0.0 - Production on 26-NOV-2019 23:54:11

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : C:\Oracle\product\19.0.0\client_1\network\admin\kerberos\krb5.conf.
Ticket cache: MSLSA:
Default principal: simon@STAGECOACH.NET

Valid starting     Expires            Service principal
11/26/19 23:54:00  11/27/19 09:54:00  LDAP/DC1.STAGECOACH.NET/STAGECOACH.NET@STAGECOACH.NET
        renew until 12/03/19 23:54:00, Flags: RAO
PS C:\Users\Simon>

Though, notice that the ticket cache is MSLSA: meaning we can connect using the Kerberos ticket cached automatically by Windows without having to manually obtain (which is exactly what we want).

At the point, the connection can be re-tested and should work:

echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

Sample output:

PS C:\Users\Simon> echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1
USER is "SIMON@STAGECOACH.NET"
PS C:\Users\Simon>

If you’re still receiving the ORA-12638 error it may be due to username case sensitivity. The authenticated identity and the Kerberos ticket must match exactly, meaning that logging into Windows as STAGECOACH\Simon and STAGECOACH\SIMON might fail while logging in a STAGECOACH\simon works.

To validate the case details of the Active Directory domain user, from the Domain Controller DC1 either validate in the “Active Directory Users and Computers”:

Or from PowerShell:

After logging in to the APPWIN1 server using the proper case username, the connection test should succeed. The TGT (and username case) can be validated using the Oracle provided oklist utility:

Notice it’s only the database that reports the user in all upper case as it was created that way.

Repeating with the more elaborate options to show additional connection properties:

echo "
set heading off
select 'DB_NAME (from v`$database) : '||name,
       'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
       'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
       'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
       'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
       'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
  from v`$database;
" | sqlplus -s /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

Sample output:

Next Steps

Now that CMU is configured for Kerberos-based authentication, more advanced mappings can be tested:

  • Authentication via Active Directory security groups and Oracle database “shared schemas”.
  • Authorization via database “global roles” mapping to AD security groups.

If you’ve enjoyed this deep dive, you may be interested in learning about the benefits of CMU to discover whether this simplified user management is right for you and your business

Categories: DBA Blogs

PART 3: Implementing Oracle Database Active Directory Password Synchronization using Oracle CMU

Wed, 2020-02-19 08:00

This is the third in a four-part article series related to testing Oracle Database 18c Centrally Managed Users (CMU) by leveraging the Oracle Cloud Infrastructure (OCI) for Oracle DBAs to create a lab or testbed. The second article covered Configuring Windows And Linux Servers For CMU Testing.

Implementing Oracle Centrally Managed Users (CMU) via “password synchronization” with Active Directory (AD) is the most practical configuration for many use cases as the authentication process from the perspective of the application remains unchanged. Applications still prompt for credentials – the key difference is that the database communicates with Active Directory for password validation.

Consequently, the database must be configured so that it can securely communicate with AD and that database users and groups are created accordingly. This article walks through how to configure and test at both the AD and database level.

Active Directory One-time Configuration Steps

The first requirement is to have an Active Directory user that the database software will use to communicate with AD. When the database receives an authorization request (i.e. a “database logon”) it needs to talk to AD to validate the user-provided credentials. Hence, a user is required for Oracle Database software to AD interaction.

As usual, this user can be added via the “Active Directory Users and Computers” GUI utility. However, for simplicity, a PowerShell equivalent command can be used.

On the Domain Controller DC1, open a PowerShell window as Administrator. Then a new user, in this case called “orasync” can be created. (IMPORTANT: update the UserPrincipalName and Path to show your actual domain vs the STAGECOACH.NET domain being used in this example):

New-ADUser `
   -Name = "orasync" `
   -UserPrincipalName = "orasync@stagecoach.net" `
   -DisplayName = "Oracle Service Directory User" `
   -Description = "Service account for Oracle Database authentication." `
   -Path = "CN=Managed Service Accounts,DC=stagecoach,DC=net" `
   -ChangePasswordAtLogon = $false `
   -PasswordNeverExpires = $true `
   -CannotChangePassword = $true `
   -Enabled = $true `
   -AccountPassword(Read-Host -AsSecureString "Initial Password:")

Putting the service account user in the “Managed Service Accounts” folder in Active Directory is a preference to reduce clutter. It’s in no way mandatory.

We can do a simple confirmation that the user was created using the PowerShell command dsquery:

dsquery user -name orasync

Sample output:

PS C:\Users\Administrator> dsquery user -name orasync
"CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET"
PS C:\Users\Administrator>

A more detailed output can be provided using the PowerShell command Get-ADUser:

Get-ADUser -Identity "orasync" -properties DistinguishedName

Sample output:

PS C:\Users\Administrator> Get-ADUser -Identity "orasync" -properties DistinguishedName


DistinguishedName : CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET
Enabled           : True
GivenName         :
Name              : orasync
ObjectClass       : user
ObjectGUID        : 0335e42e-f597-4bd8-a6dd-5f41e75177bf
SamAccountName    : orasync
SID               : S-1-5-21-4278349699-2454951225-2679278977-1108
Surname           :
UserPrincipalName : orasync@stagecoach.net


PS C:\Users\Administrator>

These same results can also be confirmed using the GUI utility “Active Directory Users and Computers”:

The permissions that this user requires on the Active Directory side are not overly clear from the official documentation. However, the following simple dsacls commands seem to provide what is required (again, change the domain name as required):

dsacls "CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET" /I:P /G "STAGECOACH\orasync:WP;lockoutTime"
dsacls "CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET" /I:P /G "STAGECOACH\orasync:RP"

The dsacls commands produce a lot of output which can be ignored.

In the previous article, an SSH public-private keypair for the Domain Controller DC1 was created. Since we will need to copy files to and from the database server, the domain controller’s public key should be copied to the database server.

On the Active Directory Domain Controller DC1, first display and copy the public key: