Zed DBA's Oracle Blog

Subscribe to  Zed DBA's Oracle Blog feed  Zed DBA's Oracle Blog
The Oracle DBA Specialist - OCM, OCP, OCE, OCS, ACE
Updated: 5 hours 42 min ago

How to restart InfiniBand Subnet Manager Exadata

Mon, 2021-01-11 08:27

You may encounter a scenario where the subnet manager is down on your InfiniBand on your Exadata. To restart is very simple:

[root@v1ex1dbadm01 ~]# ssh v1ex1sw-ibb01
You are now logged in to the root shell.
It is recommended to use ILOM shell instead of root shell.
All usage should be restricted to documented commands and documented
config files.
To view the list of documented commands, use "help" at linux prompt.
[root@v1ex1sw-ibb01 ~]# enablesm
Starting IB Subnet Manager. [ OK ]
Starting partitiond-daemon [ OK ]
[root@v1ex1sw-ibb01 ~]# ps -ef | grep opensm
root 8075 1 0 09:59 ? 00:00:00 /usr/sbin/opensm --daemon
root 8224 7909 0 09:59 pts/0 00:00:00 grep opensm
[root@v1ex1sw-ibb01 ~]#

More information can be found here:
Sun Datacenter InfiniBand Switch 36 – Enable the Subnet Manager

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

How to Startup an Oracle Exadata Machine

Fri, 2020-06-19 12:43

There maybe times when you are required to fully shutdown an Oracle Exadata Machine, for example for maintenance.

For instructions on how to shutdown an Oracle Exadata Machine, please refer to my blog post:
How to Shutdown an Oracle Exadata Machine

Once shutdown, you will need to be able to re-start which this blog post will detail.

Below is the My Oracle Support note used to carry out the startup:
Steps To Shutdown/Startup The Exadata & RDBMS Services and Cell/Compute Nodes On An Exadata Configuration (Doc ID 1093890.1)

1. Pre-requisites

Ensure you have all the compute nodes and storage cells ILOM addresses and correct passwords.  Otherwise you will not be able to remotely power back on and will require a physical power on using the power button on the front panels.

2. Power on first Compute Node

You can power on the first compute node via the ilom via ssh or WebILOM.  I prefer the ssh method shown below:

[AnwarZ@v1proxy1 ~]$ ssh root@v1ex1dbadm01-ilom
Password:

Oracle(R) Integrated Lights Out Manager

Version 4.0.4.37 r130617

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

Warning: HTTPS certificate is set to factory default.

Hostname: v1ex1dbadm01-ilom

-> show /SYSTEM

/System
Targets:
Open_Problems (0)
Processors
Memory
Power
Cooling
Storage
Networking
PCI_Devices
Firmware
BIOS
Log

Properties:
health = OK
health_details = -
open_problems_count = 0
type = Rack Mount
model = Exadata X5-2
qpart_id = XXXXXX
part_number = Exadata X5-2
serial_number = XXXXXXXXXX
component_model = ORACLE SERVER X5-2
component_part_number = XXXXXXX
component_serial_number = XXXXXXXXXX
system_identifier = Exadata Database Machine X5-2 XXXXXXXXXX
system_fw_version = 4.0.4.37
primary_operating_system = Not Available
primary_operating_system_detail = Comprehensive System monitoring is not available. Ensure the host is
running with the Hardware Management Pack. For details go to
http://www.oracle.com/goto/ilom-redirect/hmp-osa
host_primary_mac_address = xx:xx:xx:xx:xx:xx
ilom_address = x.x.x.x
ilom_mac_address = xx:xx:xx:xx:xx:xx
locator_indicator = Off
power_state = Off
actual_power_consumption = 22 watts
action = (Cannot show property)

Commands:
cd
reset
set
show
start
stop

-> start /SYSTEM
Are you sure you want to start /System (y/n)? y
Starting /System

-> show /SYSTEM

/System
Targets:
Open_Problems (0)
Processors
Memory
Power
Cooling
Storage
Networking
PCI_Devices
Firmware
BIOS
Log

Properties:
health = OK
health_details = -
open_problems_count = 0
type = Rack Mount
model = Exadata X5-2
qpart_id = XXXXXX
part_number = Exadata X5-2
serial_number = XXXXXXXXXX
component_model = ORACLE SERVER X5-2
component_part_number = XXXXXXX
component_serial_number = XXXXXXXXXX
system_identifier = Exadata Database Machine X5-2 XXXXXXXXXX
system_fw_version = 4.0.4.37
primary_operating_system = Not Available
primary_operating_system_detail = Comprehensive System monitoring is not available. Ensure the host is
running with the Hardware Management Pack. For details go to
http://www.oracle.com/goto/ilom-redirect/hmp-osa
host_primary_mac_address = xx:xx:xx:xx:xx:xx
ilom_address = x.x.x.x
ilom_mac_address = xx:xx:xx:xx:xx:xx
locator_indicator = Off
power_state = On
actual_power_consumption = 220 watts
action = (Cannot show property)

Commands:
cd
reset
set
show
start
stop

-> exit
Connection to v1ex1dbadm01-ilom closed.
[AnwarZ@v1proxy1 ~]$
3. Power on all Storage Cells

Login to the first compute node and power on all the storage cells as shown below:

login as: root
root@v1ex1dbadm01's password:
Last login: Wed Jun 10 09:21:41 IST 2020 from v1ex1dbadm01.v1.com on ssh
Last login: Wed Jun 10 17:31:31 2020 from x.x.x.x
[root@v1ex1dbadm01 ~]# uptime
17:31:37 up 1 min, 1 user, load average: 3.37, 1.22, 0.44
[root@v1ex1dbadm01 ~]# export HISTIGNORE='*'
[root@v1ex1dbadm01 ~]# for host in `cat /opt/oracle.SupportTools/onecommand/cell_group`; do
> echo ${host}: `ipmitool -I lanplus -H ${host}-ilom -U root -P XXXXXXXX chassis power on`
> done
v1ex1celadm01: Chassis Power Control: Up/On
v1ex1celadm02: Chassis Power Control: Up/On
v1ex1celadm03: Chassis Power Control: Up/On
[root@v1ex1dbadm01 ~]# export HISTIGNORE=''
[root@v1ex1dbadm01 ~]#

Please Note: the HISTIGNORE is used, so the password isn’t kept in history.

Check the storage cell services are up:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root 'hostname; uptime'
v1ex1celadm01: v1ex1celadm01.v1.com
v1ex1celadm01: 18:10:21 up 32 min, 0 users, load average: 1.86, 1.95, 2.03
v1ex1celadm02: v1ex1celadm02.v1.com
v1ex1celadm02: 18:10:21 up 32 min, 0 users, load average: 1.47, 1.82, 1.97
v1ex1celadm03: v1ex1celadm03.v1.com
v1ex1celadm03: 18:10:22 up 32 min, 0 users, load average: 1.51, 1.85, 2.01
[root@v1ex1dbadm01 ~]#
[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root "cellcli -e 'list cell detail'"
v1ex1celadm01: name: v1ex1celadm01
v1ex1celadm01: accessLevelPerm: remoteLoginEnabled
v1ex1celadm01: bbuStatus: normal
v1ex1celadm01: cellVersion: OSS_19.2.7.0.0_LINUX.X64_191012
v1ex1celadm01: cpuCount: 32/32
v1ex1celadm01: diagHistoryDays: 7
v1ex1celadm01: eighthRack: FALSE
v1ex1celadm01: fanCount: 8/8
v1ex1celadm01: fanStatus: normal
v1ex1celadm01: flashCacheMode: WriteBack
v1ex1celadm01: httpsAccess: ALL
v1ex1celadm01: id: XXXXXXXXXX
v1ex1celadm01: interconnectCount: 2
v1ex1celadm01: interconnect1: ib0
v1ex1celadm01: interconnect2: ib1
v1ex1celadm01: iormBoost: 0.0
v1ex1celadm01: ipaddress1: x.x.x.x/22
v1ex1celadm01: ipaddress2: x.x.x.x/22
v1ex1celadm01: kernelVersion: 4.1.12-124.30.1.el7uek.x86_64
v1ex1celadm01: locatorLEDStatus: off
v1ex1celadm01: makeModel: Oracle Corporation ORACLE SERVER X5-2L High Capacity
v1ex1celadm01: memoryGB: 94
v1ex1celadm01: metricHistoryDays: 7
v1ex1celadm01: notificationMethod: snmp
v1ex1celadm01: notificationPolicy: critical,warning,clear
v1ex1celadm01: offloadGroupEvents:
v1ex1celadm01: powerCount: 2/2
v1ex1celadm01: powerStatus: normal
v1ex1celadm01: ramCacheMaxSize: 0
v1ex1celadm01: ramCacheMode: Auto
v1ex1celadm01: ramCacheSize: 0
v1ex1celadm01: releaseImageStatus: success
v1ex1celadm01: releaseVersion: 19.2.7.0.0.191012
v1ex1celadm01: rpmVersion: cell-19.2.7.0.0_LINUX.X64_191012-1.x86_64
v1ex1celadm01: releaseTrackingBug: 30393131
v1ex1celadm01: rollbackVersion: 18.1.18.0.0.190709
v1ex1celadm01: snmpSubscriber: host=x.x.x.x,port=162,community=public,type=ASR,asrmPort=16161
v1ex1celadm01: host=x.x.x.x,port=161,community=V1
v1ex1celadm01: host=x.x.x.x,port=161,community=V1
v1ex1celadm01: status: online
v1ex1celadm01: temperatureReading: 22.0
v1ex1celadm01: temperatureStatus: normal
v1ex1celadm01: upTime: 0 days, 0:33
v1ex1celadm01: usbStatus: normal
v1ex1celadm01: cellsrvStatus: running
v1ex1celadm01: msStatus: running
v1ex1celadm01: rsStatus: running
v1ex1celadm02: name: v1ex1celadm02
...
v1ex1celadm02: status: online
v1ex1celadm02: temperatureReading: 22.0
v1ex1celadm02: temperatureStatus: normal
v1ex1celadm02: upTime: 0 days, 0:33
v1ex1celadm02: usbStatus: normal
v1ex1celadm02: cellsrvStatus: running
v1ex1celadm02: msStatus: running
v1ex1celadm02: rsStatus: running
v1ex1celadm03: name: v1ex1celadm03
...
v1ex1celadm03: status: online
v1ex1celadm03: temperatureReading: 22.0
v1ex1celadm03: temperatureStatus: normal
v1ex1celadm03: upTime: 0 days, 0:33
v1ex1celadm03: usbStatus: normal
v1ex1celadm03: cellsrvStatus: running
v1ex1celadm03: msStatus: running
v1ex1celadm03: rsStatus: running
[root@v1ex1dbadm01 ~]#
4. Power on remaining Compute Nodes

Power on remaining compute nodes via ipmitool:

[root@v1ex1dbadm01 ~]# export HISTIGNORE='*'
[root@v1ex1dbadm01 ~]# ipmitool -I lanplus -H v1ex1dbadm02-ilom -U root -P XXXXXXXX chassis power on
Chassis Power Control: Up/On
[root@v1ex1dbadm01 ~]# export HISTIGNORE=''
[root@v1ex1dbadm01 ~]#

If half or full rack, then the following can be used:

for host in `cat dbs_group_all_but_first`; do
echo ${host}: `ipmitool -H ${host}-ilom -U root -P XXXXXXXX chassis power on`
done

Check compute nodes are up:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l root 'hostname; uptime'
v1ex1dbadm01: v1ex1dbadm01.v1.com
v1ex1dbadm01: 18:21:55 up 12 min, 1 user, load average: 0.22, 0.89, 1.54
v1ex1dbadm02: v1ex1dbadm02.v1.com
v1ex1dbadm02: 18:21:55 up 3 min, 0 users, load average: 2.44, 1.58, 0.66
[root@v1ex1dbadm01 ~]#
5. Re-enable clusterware autostart

Re-enable clusterware autostart via dcli:

[root@v1ex1dbadm01 ~]# . oraenv
ORACLE_SID = [root] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l root /u01/app/12.1.0.2/grid/bin/crsctl enable crs
v1ex1dbadm01: CRS-4622: Oracle High Availability Services autostart is enabled.
v1ex1dbadm02: CRS-4622: Oracle High Availability Services autostart is enabled.
[root@v1ex1dbadm01 ~]#
6. Restart Grid Infrastructure on the cluster

Start clusterware on first compute node:

[root@v1ex1dbadm01 ~]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
[root@v1ex1dbadm01 ~]#

Now logon the remaining compute nodes and restart clusterware:

login as: root
root@v1ex1dbadm02's password:
Last login: Wed Jun 10 18:26:56 IST 2020 from x.x.x.x on ssh
Last login: Wed Jun 10 18:30:56 2020 from x.x.x.x
[root@v1ex1dbadm02 ~]# . oraenv
ORACLE_SID = [root] ? +ASM2
The Oracle base has been set to /u01/app/oracle
[root@v1ex1dbadm02 ~]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
[root@v1ex1dbadm02 ~]#

Wait a few minutes and check clusterware is all up as shown below:

[root@v1ex1dbadm02 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATAC1.dg
ONLINE ONLINE v1ex1dbadm01 STABLE
ONLINE ONLINE v1ex1dbadm02 STABLE
...
ONLINE ONLINE v1ex1dbadm01 STABLE
ONLINE ONLINE v1ex1dbadm02 STABLE
ora.RECOC1.dg
ONLINE ONLINE v1ex1dbadm01 STABLE
ONLINE ONLINE v1ex1dbadm02 STABLE
ora.asm
ONLINE ONLINE v1ex1dbadm01 Started,STABLE
ONLINE ONLINE v1ex1dbadm02 Started,STABLE
...
ora.net1.network
ONLINE ONLINE v1ex1dbadm01 STABLE
ONLINE ONLINE v1ex1dbadm02 STABLE
ora.ons
ONLINE ONLINE v1ex1dbadm01 STABLE
ONLINE ONLINE v1ex1dbadm02 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE v1ex1dbadm02 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE v1ex1dbadm01 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE v1ex1dbadm01 STABLE
...
1 ONLINE ONLINE v1ex1dbadm02 Open,STABLE
ora.oc4j
1 ONLINE ONLINE v1ex1dbadm01 STABLE
ora.scan1.vip
1 ONLINE ONLINE v1ex1dbadm02 STABLE
ora.scan2.vip
1 ONLINE ONLINE v1ex1dbadm01 STABLE
ora.scan3.vip
1 ONLINE ONLINE v1ex1dbadm01 STABLE
--------------------------------------------------------------------------------
[root@v1ex1dbadm02 ~]#
6. Restart OEM Agent

Optionally if you have an OEM agent (most likely), restart as follows:

[oracle@v1ex1dbadm01 ~]$ cd /u01/app/agent/agent_13.3.0.0.0/bin
[oracle@v1ex1dbadm01 bin]$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running
[oracle@v1ex1dbadm01 bin]$ ./emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Starting agent ............................ started.
[oracle@v1ex1dbadm01 bin]$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 13.3.0.0.0
OMS Version : 13.3.0.0.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/agent/agent_inst
Agent Log Directory : /u01/app/agent/agent_inst/sysman/log
Agent Binaries : /u01/app/agent/agent_13.3.0.0.0
Core JAR Location : /u01/app/agent/agent_13.3.0.0.0/jlib
Agent Process ID : 122257
Parent Process ID : 122120
Agent URL : https://v1ex1dbadm01.v1.com:3872/emd/main/
Local Agent URL in NAT : https://v1ex1dbadm01.v1.com:3872/emd/main/
Repository URL : https://v1oem.v1.com:4903/empbs/upload
Started at : 2020-06-17 15:40:59
Started by user : oracle
Operating System : Linux version 4.1.12-124.30.1.el7uek.x86_64 (amd64)
Number of Targets : 43
Last Reload : (none)
Last successful upload : 2020-06-17 15:41:23
Last attempted upload : 2020-06-17 15:41:23
Total Megabytes of XML files uploaded so far : 0.1
Number of XML files pending upload : 5
Size of XML files pending upload(MB) : 0.02
Available disk space on upload filesystem : 21.34%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2020-06-17 15:41:18
Last successful heartbeat to OMS : 2020-06-17 15:41:18
Next scheduled heartbeat to OMS : 2020-06-17 15:42:20

---------------------------------------------------------------
Agent is Running and Ready
[oracle@v1ex1dbadm01 bin]$

Now on any other compute nodes:

[oracle@v1ex1dbadm01 bin]$ ssh v1ex1dbadm02
Last login: Wed Jun 17 12:37:20 IST 2020 from x.x.x.x on ssh
Last login: Wed Jun 17 15:43:07 2020 from x.x.x.x
[oracle@v1ex1dbadm02 ~]$ cd /u01/app/agent/agent_13.3.0.0.0/bin
[oracle@v1ex1dbadm02 bin]$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running
[oracle@v1ex1dbadm02 bin]$ ./emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Starting agent .................................... started.
[oracle@v1ex1dbadm02 bin]$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 13.3.0.0.0
OMS Version : 13.3.0.0.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/agent/agent_inst
Agent Log Directory : /u01/app/agent/agent_inst/sysman/log
Agent Binaries : /u01/app/agent/agent_13.3.0.0.0
Core JAR Location : /u01/app/agent/agent_13.3.0.0.0/jlib
Agent Process ID : 189737
Parent Process ID : 189513
Agent URL : https://v1ex1dbadm02.v1.com:3872/emd/main/
Local Agent URL in NAT : https://v1ex1dbadm02.v1.com:3872/emd/main/
Repository URL : https://v1oem.v1.com:4903/empbs/upload
Started at : 2020-06-17 15:44:01
Started by user : oracle
Operating System : Linux version 4.1.12-124.30.1.el7uek.x86_64 (amd64)
Number of Targets : 37
Last Reload : (none)
Last successful upload : 2020-06-17 15:44:44
Last attempted upload : 2020-06-17 15:44:45
Total Megabytes of XML files uploaded so far : 0.17
Number of XML files pending upload : 1
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 28.51%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2020-06-17 15:44:28
Last successful heartbeat to OMS : 2020-06-17 15:44:28
Next scheduled heartbeat to OMS : 2020-06-17 15:45:28

---------------------------------------------------------------
Agent is Running and Ready
[oracle@v1ex1dbadm02 bin]$

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

How to Shutdown an Oracle Exadata Machine

Fri, 2020-06-19 10:31

There maybe times when you are required to fully shutdown an Oracle Exadata Machine, for example for maintenance.

Below is the My Oracle Support note used to carry out the shutdown:
Steps To Shutdown/Startup The Exadata & RDBMS Services and Cell/Compute Nodes On An Exadata Configuration (Doc ID 1093890.1)

1. Pre-requisites

Ensure you have all the compute nodes and storage cells ILOM addresses and correct passwords.  Otherwise you will not be able to remotely power back on and will require a physical power on using the power button on the front panels.

2. Disable clusterware autostart

First we need to stop clusterware restarting up on reboot.  So logon to your first compute node and disable via dcli (more info on dcli can be found in this blog post) using your correct crs home:

login as: root
root@x.x.x.x's password:
Last login: Wed Jun 10 08:45:30 IST 2020 from x.x.x.x on pts/0
Last login: Wed Jun 10 09:07:17 2020 from x.x.x.x
[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l root /u01/app/12.1.0.2/grid/bin/crsctl config crs
v1ex1dbadm01: CRS-4622: Oracle High Availability Services autostart is enabled.
v1ex1dbadm02: CRS-4622: Oracle High Availability Services autostart is enabled.
[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l root /u01/app/12.1.0.2/grid/bin/crsctl disable crs
v1ex1dbadm01: CRS-4621: Oracle High Availability Services autostart is disabled.
v1ex1dbadm02: CRS-4621: Oracle High Availability Services autostart is disabled.
[root@v1ex1dbadm01 ~]#
3. Stop Grid Infrastructure on the cluster

Next we stop clusterware cluster-wide gracefully:

[root@v1ex1dbadm01 ~]# . oraenv
ORACLE_SID = [root] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l root /u01/app/12.1.0.2/grid/bin/crsctl check crs
v1ex1dbadm01: CRS-4638: Oracle High Availability Services is online
v1ex1dbadm01: CRS-4537: Cluster Ready Services is online
v1ex1dbadm01: CRS-4529: Cluster Synchronization Services is online
v1ex1dbadm01: CRS-4533: Event Manager is online
v1ex1dbadm02: CRS-4638: Oracle High Availability Services is online
v1ex1dbadm02: CRS-4537: Cluster Ready Services is online
v1ex1dbadm02: CRS-4529: Cluster Synchronization Services is online
v1ex1dbadm02: CRS-4533: Event Manager is online
[root@v1ex1dbadm01 ~]#
[root@v1ex1dbadm01 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATAC1.dg
ONLINE ONLINE v1ex1dbadm01 STABLE
ONLINE ONLINE v1ex1dbadm02 STABLE
...
ora.RECOC1.dg
ONLINE ONLINE v1ex1dbadm01 STABLE
ONLINE ONLINE v1ex1dbadm02 STABLE
ora.asm
ONLINE ONLINE v1ex1dbadm01 Started,STABLE
ONLINE ONLINE v1ex1dbadm02 Started,STABLE
...
ora.net1.network
ONLINE ONLINE v1ex1dbadm01 STABLE
ONLINE ONLINE v1ex1dbadm02 STABLE
ora.ons
ONLINE ONLINE v1ex1dbadm01 STABLE
ONLINE ONLINE v1ex1dbadm02 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE v1ex1dbadm02 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE v1ex1dbadm01 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE v1ex1dbadm01 STABLE
...
1 ONLINE ONLINE v1ex1dbadm01 STABLE
ora.scan1.vip
1 ONLINE ONLINE v1ex1dbadm02 STABLE
ora.scan2.vip
1 ONLINE ONLINE v1ex1dbadm01 STABLE
ora.scan3.vip
1 ONLINE ONLINE v1ex1dbadm01 STABLE
--------------------------------------------------------------------------------
[root@v1ex1dbadm01 ~]#
[root@v1ex1dbadm01 ~]# crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'v1ex1dbadm01'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'v1ex1dbadm01'
...
CRS-2677: Stop of 'ora.ons' on 'v1ex1dbadm01' succeeded 
CRS-2673: Attempting to stop 'ora.net1.network' on 'v1ex1dbadm01' 
CRS-2677: Stop of 'ora.net1.network' on 'v1ex1dbadm01' succeeded 
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'v1ex1dbadm01' has completed 
CRS-2677: Stop of 'ora.crsd' on 'v1ex1dbadm02' succeeded 
CRS-2673: Attempting to stop 'ora.ctssd' on 'v1ex1dbadm02' 
CRS-2673: Attempting to stop 'ora.evmd' on 'v1ex1dbadm02' 
CRS-2673: Attempting to stop 'ora.storage' on 'v1ex1dbadm02' 
CRS-2677: Stop of 'ora.storage' on 'v1ex1dbadm02' succeeded 
CRS-2673: Attempting to stop 'ora.asm' on 'v1ex1dbadm02' 
CRS-2677: Stop of 'ora.crsd' on 'v1ex1dbadm01' succeeded 
CRS-2673: Attempting to stop 'ora.ctssd' on 'v1ex1dbadm01' 
CRS-2673: Attempting to stop 'ora.evmd' on 'v1ex1dbadm01' 
CRS-2673: Attempting to stop 'ora.storage' on 'v1ex1dbadm01' 
CRS-2677: Stop of 'ora.storage' on 'v1ex1dbadm01' succeeded 
CRS-2673: Attempting to stop 'ora.asm' on 'v1ex1dbadm01' 
CRS-2677: Stop of 'ora.ctssd' on 'v1ex1dbadm02' succeeded 
CRS-2677: Stop of 'ora.evmd' on 'v1ex1dbadm02' succeeded 
CRS-2677: Stop of 'ora.evmd' on 'v1ex1dbadm01' succeeded 
CRS-2677: Stop of 'ora.ctssd' on 'v1ex1dbadm01' succeeded 
CRS-2677: Stop of 'ora.asm' on 'v1ex1dbadm02' succeeded 
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'v1ex1dbadm02' 
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'v1ex1dbadm02' succeeded 
CRS-2673: Attempting to stop 'ora.cssd' on 'v1ex1dbadm02' 
CRS-2677: Stop of 'ora.cssd' on 'v1ex1dbadm02' succeeded 
CRS-2673: Attempting to stop 'ora.diskmon' on 'v1ex1dbadm02' 
CRS-2677: Stop of 'ora.diskmon' on 'v1ex1dbadm02' succeeded
CRS-2677: Stop of 'ora.asm' on 'v1ex1dbadm01' succeeded 
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'v1ex1dbadm01' 
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'v1ex1dbadm01' succeeded 
CRS-2673: Attempting to stop 'ora.cssd' on 'v1ex1dbadm01'
CRS-2677: Stop of 'ora.cssd' on 'v1ex1dbadm01' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'v1ex1dbadm01'
CRS-2677: Stop of 'ora.diskmon' on 'v1ex1dbadm01' succeeded
[root@v1ex1dbadm01 ~]#
[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l root /u01/app/12.1.0.2/grid/bin/crsctl stat res -t
v1ex1dbadm01: CRS-4535: Cannot communicate with Cluster Ready Services
v1ex1dbadm01: CRS-4000: Command Status failed, or completed with errors.
v1ex1dbadm02: CRS-4535: Cannot communicate with Cluster Ready Services
v1ex1dbadm02: CRS-4000: Command Status failed, or completed with errors.
[root@v1ex1dbadm01 ~]#
4. Power off Storage Cells

Now that clusterware is down including ASM, we can power down the storage cells by first shutting down the cell services:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root "cellcli -e alter cell shutdown services all"
v1ex1celadm01:
v1ex1celadm01: Stopping the RS, CELLSRV, and MS services...
v1ex1celadm01: The SHUTDOWN of services was successful.
v1ex1celadm02:
v1ex1celadm02: Stopping the RS, CELLSRV, and MS services...
v1ex1celadm02: The SHUTDOWN of services was successful.
v1ex1celadm03:
v1ex1celadm03: Stopping the RS, CELLSRV, and MS services...
v1ex1celadm03: The SHUTDOWN of services was successful.
[root@v1ex1dbadm01 ~]#

Now the storage cells are shutdown, we can power them off:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root poweroff

v1ex1celadm02:Connection to v1ex1celadm02 closed by remote host.

v1ex1celadm01:Connection to v1ex1celadm01 closed by remote host.

v1ex1celadm03:Connection to v1ex1celadm03 closed by remote host.

[root@v1ex1dbadm01 ~]#
5. Power off Compute Nodes

As we are on the first compute node, we can power this off as shown below:

[root@v1ex1dbadm01 ~]# poweroff

Now we power off the remaining compute node by logging on via ssh:

login as: root
root@x.x.x.x's password:
Last login: Wed Jun 10 08:45:25 IST 2020 from x.x.x.x on ssh
Last login: Wed Jun 10 09:03:41 2020 from x.x.x.x
[root@v1ex1dbadm02 ~]# poweroff

If you have a half or full rack and wish to power off all compute nodes, you can use:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/dbs_group_all_but_first -l root poweroff

Then power off the first node:

[root@v1ex1dbadm01 ~]# poweroff

Carry out your maintenance and when completed, you can restart the Oracle Exadata Machine by follow my blog post:
How to Startup an Oracle Exadata Machine

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

How to obtain the serial numbers on an Oracle Exadata Machine

Sat, 2020-05-30 18:19

You may be required to obtain serial numbers from an Oracle Exadata Machine, for example to confirm correct hardware for part replacement like disk controller battery (X4-2 and older) or disk, etc.

Below is how to obtain serials for each component.

Exadata Machine

To obtain the serial number of the Exadata Machine itself:

[root@v1ex1dbadm01 ~]# ipmitool sunoem cli "show /SP system_identifier" | grep "system_identifier ="
system_identifier = Exadata Database Machine X5-2 AK00XXXXXX
[root@v1ex1dbadm01 ~]#
Compute Nodes

To obtain the serial number of the compute nodes via dcli:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l root dmidecode -s system-serial-number
v1ex1dbadm01: 1514NMXXXX
v1ex1dbadm02: 1514NMXXXX
[root@v1ex1dbadm01 ~]#
Storage Cells

To obtain the serial number of the storage cells via dcli:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root dmidecode -s system-serial-number
v1ex1celadm01: 1515NMXXXX
v1ex1celadm02: 1515NMXXXX
v1ex1celadm03: 1515NMXXXX
[root@v1ex1dbadm01 ~]#
InfiniBands

To obtain the serial number of the InfiniBands via dcli:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/ib_group -l root showfruinfo | grep -a Sun_Serial_Number
v1ex1sw-iba01: Sun_Serial_Number : XXXXXXT+1512RRXXXX
v1ex1sw-iba01: Sun_Serial_Number : AK0029XXXX
v1ex1sw-ibb01: Sun_Serial_Number : XXXXXXT+1512RRXXXX
v1ex1sw-ibb01: Sun_Serial_Number : AK0029XXXX
[root@v1ex1dbadm01 ~]#

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

Oracle and Microsoft to Interconnect Oracle Cloud and Microsoft Azure

Tue, 2019-06-11 09:25

On the 5th of June 2019, both Oracle and Microsoft made a joint announcement on the interconnection between Oracle Cloud and Microsoft Azure:

Oracle’s Press Release
Microsoft Press Release

The key aspects of the announcement are:

  1. A direct interconnect between Oracle Cloud and Azure Cloud, starting in Oracle’s Ashburn (North America) region and Azure Washington DC (US East) region, with plans to expand additional regions in the future*.
  2. Unified identity and access management, via a unified single sign-on experience to manage resources across both Oracle Cloud and Azure.
  3. Supported deployment of custom applications and packaged Oracle applications (such as JD Edwards, PeopleSoft and E-Business Suite typically referred to as Oracle Applications Unlimited) in Azure Cloud with the Oracle databases (such as RAC, Exadata, Autonomous Database) deployed in the Oracle Cloud.
  4. A collaborative support model for customers leveraging these new capabilities.

*With only one region available in both Oracle and Azure, this will allow for failure in a Availability Domain in Oracle Cloud and/or Availability Zone in Azure Cloud but not a failure of a whole region in either.  So until more regions are added, Disaster Recover will be limited to Availability Domains/Availability Zones:

oracle-azure-connectivity

Figure 4 from Oracle’s blog “Overview of the Interconnect Between Oracle and Microsoft“.

What does this mean?

In a nutshell, for those customers who have Microsoft Azure as their Cloud platform of choice, can now migrate their application tier to Microsoft Azure, whilst migrating the database tier i.e. Oracle database (mandatory for Application Unlimited) without having to worry about the all important latency (high-throughput, low-latency as stated in Oracle’s blog post).  It is however unclear if there will be any charge for outbound/inbound traffic between the clouds, but it does seem from the documentation and blog post that both Oracle’s dedicated FastConnect and Azure’s dedicated ExpressRoute are required, which are both fix rate products.  It also helps those customers who require the favourable database licensing on the Oracle Cloud, more info can be found in my blog post here.

This is certainly a step towards the trend of multi-cloud/hybird-cloud platforms.

More info can be found regarding this announcement from our Version 1’s blog post here.

Another interesting read from SearchCloud Computing in regards to this announcement.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

RMAN Incremental Updating Backup & Demo Part 1

Thu, 2019-05-30 17:02

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Updating Backup

An incremental updating backup is a level 0 incremental backup which is recovered using level 1 incremental backups.  This avoids the overhead of taking level 0 incremental backups, for example in a backup strategy where a level 0 is taken every Sunday and all other days level 1.

An simple example would be, an incremental level 0 backup is taken once and would be recovered daily.  For example everyday an incremental level 1 backup is taken and then the incremental level 1 backup taken yesterday would be used to recover the incremental level 0 backup.  Thus the incremental level 0 backup would fluctuate between 1 to 2 days behind current, i.e. at the point when the backup script is about to run, the incremental level 1 backup would be as of approximately 48 hours ago and when ran would be as of approximately 24 hours ago.

Incremental Updating Backup Demo

We take an incremental updating backup using my script  9_incremental_updated_backup.sh:

[oracle@dc1sbxdb001 demo]$ ./9_incremental_updated_backup.sh 
-----------------------
Step 1: Set environment
-----------------------

Setting the Database Environment using oraenv...
The Oracle base remains unchanged with value /u01/app/oracle

ORACLE_SID: ZEDDBA
ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

Press Enter to continue

The environment is set to my ZEDDBA database, next we recover an incremental level 0 backup followed by taking an incremental level 1 backup, which we do three times as shown in the content of the 9_incremental_updated_backup.cmd file:

---------------------------------------
Step 2: Take Incremental Updated Backup
---------------------------------------

Clearing all backups...

Content of 9_incremental_updated_backup.cmd file:

RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
HOST 'read Press Enter';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;
HOST 'read Press Enter';
RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
HOST 'read Press Enter';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;
HOST 'read Press Enter';
RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
HOST 'read Press Enter';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;

Press Enter to continue

Next we recover an incremental level 0 backup using the “RECOVER COPY OF DATABASE” syntax with the tag of the incremental level 1 backup we want to use for recovery “WITH TAG ‘INC UPDATE’“:

Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/9_incremental_updated_backup.cmd'

Recovery Manager: Release 12.2.0.1.0 - Production on Thu May 30 19:31:09 2019

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

connected to target database: ZEDDBA (DBID=3520942925)

RMAN> RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
Starting recover at 30-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
Finished recover at 30-MAY-19
RMAN> HOST 'read Press Enter';

host command complete

As you can see RMAN states there is no copy of the datafiles found to recover.  This is because we don’t yet have an incremental level 0 backup.

Next we take an incremental level 1 backup using the “BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘INC UPDATE’ DATABASE” syntax giving the tag of the incremental level 0 backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;
Starting backup at 30-MAY-19
using channel ORA_DISK_1
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_system_gh08cy6s_.dbf tag=INC UPDATE RECID=5 STAMP=1009654288
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_sysaux_gh08d1g5_.dbf tag=INC UPDATE RECID=6 STAMP=1009654291
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_undotbs1_gh08d4lc_.dbf tag=INC UPDATE RECID=7 STAMP=1009654293
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_users_gh08d7rb_.dbf tag=INC UPDATE RECID=8 STAMP=1009654296
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-MAY-19

Starting Control File and SPFILE Autobackup at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654296_gh08d93p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-MAY-19
RMAN> HOST 'read Press Enter';

host command complete

As you can see RMAN still states there no parent backup or copy of the datafiles found, so instead takes an incremental level 0 backup (copy) for you.

Next we again recover an incremental level 0 backup using the “RECOVER COPY OF DATABASE” syntax with the tag of the incremental level 1 backup we want to use for recovery “WITH TAG ‘INC UPDATE’” to see if it behaves any different this time:

RMAN> RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
Starting recover at 30-MAY-19
using channel ORA_DISK_1
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
Finished recover at 30-MAY-19
RMAN> HOST 'read Press Enter';

host command complete

As you can see RMAN states there still no copy of the datafiles found to recover.  This is not exactly true as an incremental level 0 backup was taken above, however there is no incremental level 1 backup to use to recover hence the message.

Next we again take an incremental level 1 backup using the “BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘INC UPDATE’ DATABASE” syntax giving the tag of the incremental level 0 backup to see if it behaves any different this time:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;
Starting backup at 30-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 30-MAY-19
channel ORA_DISK_1: finished piece 1 at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08dsxd_.bkp tag=INC UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-MAY-19

Starting Control File and SPFILE Autobackup at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654317_gh08dx4z_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-MAY-19
RMAN> HOST 'read Press Enter';

host command complete

As you can see RMAN now is aware of the incremental level 0 backup and thus takes an incremental level 1 backup as requested.

Next we again recover an incremental level 0 backup using the “RECOVER COPY OF DATABASE” syntax with the tag of the incremental level 1 backup we want to use for recovery “WITH TAG ‘INC UPDATE’” to see if it behaves any different this time:

RMAN> RECOVER COPY OF DATABASE WITH TAG 'INC UPDATE';
Starting recover at 30-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_system_gh08cy6s_.dbf
recovering datafile copy file number=00002 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_sysaux_gh08d1g5_.dbf
recovering datafile copy file number=00003 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_undotbs1_gh08d4lc_.dbf
recovering datafile copy file number=00004 name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_users_gh08d7rb_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08dsxd_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08dsxd_.bkp tag=INC UPDATE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 30-MAY-19

Starting Control File and SPFILE Autobackup at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654322_gh08f35f_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-MAY-19
RMAN> HOST 'read Press Enter';

host command complete

As there is now an incremental level 1 backup that has the tag ‘INC UPDATE‘ it is used to recover the incremental level 0 backup to the point when the incremental level 1 backup was taken.

Next we again take an incremental level 1 backup using the “BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘INC UPDATE’ DATABASE” syntax giving the tag of the incremental level 0 backup to see if it behaves any different this time:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INC UPDATE' DATABASE;
Starting backup at 30-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 30-MAY-19
channel ORA_DISK_1: finished piece 1 at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_30/o1_mf_nnnd1_INC_UPDATE_gh08ffh1_.bkp tag=INC UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-MAY-19

Starting Control File and SPFILE Autobackup at 30-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_30/o1_mf_s_1009654334_gh08fgqj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-MAY-19

Recovery Manager complete.

Press Enter to continue

As you can see RMAN takes incremental level 1 backup as requested, just as before and will continue to do so till there is no incremental level 0 backup.

Finally, we update the demo log table:

-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log

1 row created.

Commit complete.

WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup
16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs
17-MAY-19 04.55.12.000000 PM Image Copy
20-MAY-19 05.13.44.000000 PM Incremental Level 0
22-MAY-19 12.09.17.000000 PM Incremental Level 1
22-MAY-19 12.54.43.000000 PM Incremental Level 1 with BCT
24-MAY-19 06.28.33.000000 PM Incremental Level 1 Cumulative
30-MAY-19 07.32.24.000000 PM Incremental Updated Backup

9 rows selected.

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$
Reference Scripts
  1. 9_incremental_updated_backup.sh
  2. 9_incremental_updated_backup.cmd

To download all 2 in one zip: 9_incremental_updated_backup.zip

The rest of the series
  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. RMAN Obsolete

Please Note: Links to the blog posts will be released daily and updated here.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

RMAN Incremental Differential vs Cumulative & Demo

Wed, 2019-05-29 17:23

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Differential Incremental Backups (default)

A differential incremental backup, backs up all the blocks that have changed after the most recent incremental backup, which can be either level 1 or 0.

RMAN determines which is the most recent level 1 backup and then backs up all the blocks changed after that backup, if no level 1 then all the blocks changed after the last level 0 backup are backed up.  If no level 0 backup exist, then a level 0 backup is taken.

The diagram below shows how the level 1 differential incremental backups are just backing up from the last level 1:

Incremental_Differential

Cumulative Incremental Backups

A cumulative incremental backup, backs up all the blocks that have changed after the most recent incremental backup at level 0 only.

This reduces the work needed when recovering after a restore as only the desired level 1 is required to be applied instead of several level 1 in sequence.  The trade off is as every level 1 backup needs to backup all the blocks changed from the last level 0, it is repeated the work done by the previous level 1, thus require more space and more time.

The diagram below shows how the level 1 cumulative incremental backup are backing up from the last level 0:

Incremental_Cumulative

Cumulative Incremental Backup Demo

We take an cumulative incremental level 1 backup using my script 8_incremental_level_1_cumulative.sh:

[oracle@dc1sbxdb001 demo]$ ./8_incremental_level_1_cumulative.sh 
-----------------------
Step 1: Set environment
-----------------------

Setting the Database Environment using oraenv...
The Oracle base remains unchanged with value /u01/app/oracle

ORACLE_SID: ZEDDBA
ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

Press Enter to continue

The environment is set to my ZEDDBA database, next we will take an differential backup twice, followed by cumulative incremental backup twice:

-------------------------------------------------
Step 2: Take Incremental Level 1 Cumlative Backup
-------------------------------------------------

Content of 8_incremental_level_1_cumulative.cmd file:

BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
HOST 'read Press Enter to LIST BACKUP';
LIST BACKUP;

Press Enter to continue

Next a differential incremental backup is taken:

Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/8_incremental_level_1_cumulative.cmd'

Recovery Manager: Release 12.2.0.1.0 - Production on Fri May 24 18:27:49 2019

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

connected to target database: ZEDDBA (DBID=3520942925)

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
Starting backup at 24-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=157 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAY-19
channel ORA_DISK_1: finished piece 1 at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp tag=INCR LEVEL 1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-MAY-19

Starting Control File and SPFILE Autobackup at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132076_ggjbdwoh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-MAY-19

Next another differential incremental backup is taken:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
Starting backup at 24-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAY-19
channel ORA_DISK_1: finished piece 1 at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp tag=INCR LEVEL 1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-MAY-19

Starting Control File and SPFILE Autobackup at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132081_ggjbf23y_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-MAY-19

Next a cumulative incremental backup is taken:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
Starting backup at 24-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAY-19
channel ORA_DISK_1: finished piece 1 at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp tag=INCR LEVEL 1 CUM comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-MAY-19

Starting Control File and SPFILE Autobackup at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132084_ggjbf516_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-MAY-19

Next another cumulative incremental backup is taken:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
Starting backup at 24-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAY-19
channel ORA_DISK_1: finished piece 1 at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp tag=INCR LEVEL 1 CUM comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-MAY-19

Starting Control File and SPFILE Autobackup at 24-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132087_ggjbf81t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-MAY-19

Next we list the backup within RMAN using ‘LIST BACKUP‘:

RMAN> HOST 'read Press Enter to LIST BACKUP';
host command complete

RMAN> LIST BACKUP;

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

...
...
...
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Incr 1 38.23M DISK 00:00:02 24-MAY-19 
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26 Full 8.31M DISK 00:00:00 24-MAY-19 
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182756
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132076_ggjbdwoh_.bkp
SPFILE Included: Modification time: 24-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 603541 Ckp time: 24-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Incr 1 64.00K DISK 00:00:03 24-MAY-19 
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
28 Full 8.31M DISK 00:00:01 24-MAY-19 
BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182801
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132081_ggjbf23y_.bkp
SPFILE Included: Modification time: 24-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 603567 Ckp time: 24-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Incr 1 38.23M DISK 00:00:01 24-MAY-19 
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 CUM
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
30 Full 8.31M DISK 00:00:01 24-MAY-19 
BP Key: 30 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182804
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132084_ggjbf516_.bkp
SPFILE Included: Modification time: 24-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 603592 Ckp time: 24-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31 Incr 1 38.23M DISK 00:00:01 24-MAY-19 
BP Key: 31 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 CUM
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp
List of Datafiles in backup set 31
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 8.31M DISK 00:00:01 24-MAY-19 
BP Key: 32 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182807
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132087_ggjbf81t_.bkp
SPFILE Included: Modification time: 24-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 603617 Ckp time: 24-MAY-19

Recovery Manager complete.

Press Enter to continue

Next we can see the first differential incremental backup backed up 39M of changed blocks, however the second differential incremental backup only backed up 72K as only backed up the blocks changed from the last level 1 backup.  Where as the cumulative incremental on both the first and second backup, backed up 39M of changed blocks:

Files size on disk:
...
...
...

/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24:
total 115M
-rw-r-----. 1 oracle oinstall 39M May 24 18:27 o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp
-rw-r-----. 1 oracle oinstall 72K May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp
-rw-r-----. 1 oracle oinstall 39M May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp
-rw-r-----. 1 oracle oinstall 39M May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp

Press Enter to continue

Finally, we update the demo log table:

-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log

1 row created.

Commit complete.

WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup
16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs
17-MAY-19 04.55.12.000000 PM Image Copy
20-MAY-19 05.13.44.000000 PM Incremental Level 0
22-MAY-19 12.09.17.000000 PM Incremental Level 1
22-MAY-19 12.54.43.000000 PM Incremental Level 1 with BCT
24-MAY-19 06.28.33.000000 PM Incremental Level 1 Cumulative

8 rows selected.

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$
Reference Scripts
  1. 8_incremental_level_1_cumulative.sh
  2. 8_incremental_level_1_cumulative.cmd

To download all 2 in one zip: 8_incremental_level_1_cumulative.zip

The rest of the series
  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. RMAN Obsolete

Please Note: Links to the blog posts will be released daily and updated here.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

RMAN Incremental & Demo Part 2 (Level 1)

Tue, 2019-05-28 11:07

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Backup

An incremental backup only backup up those data blocks that have changed since the last backup.

Types of Incremental Backups

There are 2 types of Incremental Backups:

  1. Level 0 are a base for subsequent backups.  Copies all blocks containing data similar to a full backup, with the only difference that full backups are never included in an incremental strategy.  Level 0 can be backup sets or image copies.
  2. Level 1 are subsequent backups of a level 0, backing up by default all blocks changed after the most recent level 0 or 1, known as differential incremental backup.  More on different types of level 1 backups is discuss in detail here.
Incremental Level 1 Demo

We take an incremental level 1 backup using my script 6_incremental_level_1.sh:

[oracle@dc1sbxdb001 demo]$ ./6_incremental_level_1.sh 
-----------------------
Step 1: Set environment
-----------------------

Setting the Database Environment using oraenv...
The Oracle base remains unchanged with value /u01/app/oracle

ORACLE_SID: ZEDDBA
ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

Press Enter to continue

The environment is set to my ZEDDBA database, then next the incremental backup (Level 1) is taken:

----------------------------------------
Step 2: Take Incremental Level 1 Backup
----------------------------------------

Cotent of 6_incremental_level_1.cmd file:

BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
HOST 'read Press Enter to LIST BACKUP';
LIST BACKUP;

Press Enter to continue

Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/6_incremental_level_1.cmd'

Recovery Manager: Release 12.2.0.1.0 - Production on Wed May 22 12:08:27 2019

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

connected to target database: ZEDDBA (DBID=3520942925)

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
2> HOST 'read Press Enter to LIST BACKUP';
3> LIST BACKUP;
4> 
Starting backup at 22-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 22-MAY-19
channel ORA_DISK_1: finished piece 1 at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd1_INCR_LEVEL_1_ggbcfhbp_.bkp tag=INCR LEVEL 1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-MAY-19

Starting Control File and SPFILE Autobackup at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008936514_ggbcflon_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-19

host command complete

We use host within RMAN just to wait for input before moving on for demo purposes.  Next we list the backup within RMAN using ‘LIST BACKUP‘:

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 498.93M DISK 00:00:08 16-MAY-19 
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 8.31M DISK 00:00:00 16-MAY-19 
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190516T173912
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008437952_gfv4kjko_.bkp
SPFILE Included: Modification time: 16-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 353836 Ckp time: 16-MAY-19

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3 77.83M DISK 00:00:01 16-MAY-19 
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4y7m2_.bkp

List of Archived Logs in backup set 3
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 332298 16-MAY-19 354044 16-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 498.94M DISK 00:00:06 16-MAY-19 
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174603
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_TAG20190516T174603_gfv4yco3_.bkp
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5 3.50K DISK 00:00:00 16-MAY-19 
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4ym4v_.bkp

List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 14 354044 16-MAY-19 354066 16-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 8.31M DISK 00:00:00 16-MAY-19 
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174612
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008438372_gfv4ynmv_.bkp
SPFILE Included: Modification time: 16-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 354077 Ckp time: 16-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 8.31M DISK 00:00:00 17-MAY-19 
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20190517T165453
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_17/o1_mf_s_1008521693_gfxpbfs7_.bkp
SPFILE Included: Modification time: 17-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 458035 Ckp time: 17-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Incr 0 511.64M DISK 00:00:05 20-MAY-19 
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 0
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_20/o1_mf_nnnd0_INCR_LEVEL_0_gg5njx01_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 8.31M DISK 00:00:00 20-MAY-19 
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20190520T171324
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_20/o1_mf_s_1008782004_gg5nk4cs_.bkp
SPFILE Included: Modification time: 20-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 571056 Ckp time: 20-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Incr 1 11.22M DISK 00:00:02 22-MAY-19 
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd1_INCR_LEVEL_1_ggbcfhbp_.bkp
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 575174 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 575174 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 575174 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 575174 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 Full 8.31M DISK 00:00:00 22-MAY-19 
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20190522T120834
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008936514_ggbcflon_.bkp
SPFILE Included: Modification time: 22-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 575181 Ckp time: 22-MAY-19

Recovery Manager complete.

Press Enter to continue

Next we look at the file size of the backup piece and we can see the level 1 was just 12M compared to the level 0 which was 512M:

Files size on disk:
/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16:
total 1.1G
-rw-r-----. 1 oracle oinstall 499M May 16 17:39 o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp
-rw-r-----. 1 oracle oinstall 78M May 16 17:46 o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4y7m2_.bkp
-rw-r-----. 1 oracle oinstall 499M May 16 17:46 o1_mf_nnndf_TAG20190516T174603_gfv4yco3_.bkp
-rw-r-----. 1 oracle oinstall 4.0K May 16 17:46 o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4ym4v_.bkp

/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_20:
total 512M
-rw-r-----. 1 oracle oinstall 512M May 20 17:13 o1_mf_nnnd0_INCR_LEVEL_0_gg5njx01_.bkp

/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22:
total 12M
-rw-r-----. 1 oracle oinstall 12M May 22 12:08 o1_mf_nnnd1_INCR_LEVEL_1_ggbcfhbp_.bkp

Press Enter to continue

Finally, we update the demo log table:

-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log

1 row created.

Commit complete.

WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup
16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs
17-MAY-19 04.55.12.000000 PM Image Copy
20-MAY-19 05.13.44.000000 PM Incremental Level 0
22-MAY-19 12.09.17.000000 PM Incremental Level 1

6 rows selected.

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$
Reference Scripts
  1. 6_incremental_level_1.sh
  2. 6_incremental_level_1.cmd

To download all 2 in one zip: 6_incremental_level_1.zip

The rest of the series
  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. RMAN Obsolete

Please Note: Links to the blog posts will be released daily and updated here.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

RMAN Incremental & Demo Part 1 (Level 0)

Mon, 2019-05-27 07:15

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Backup

An incremental backup only backup up those data blocks that have changed since the last backup.

Types of Incremental Backups

There are 2 types of Incremental Backups:

  1. Level 0 are a base for subsequent backups.  Copies all blocks containing data similar to a full backup, with the only difference that full backups are never included in an incremental strategy.  Level 0 can be backup sets or image copies.
  2. Level 1 are subsequent backups of a level 0, backing up by default all blocks changed after the most recent level 0 or 1, known as differential incremental backup.  More on different types of level 1 backups is discuss in detail here.
Incremental Level 0 Demo

We take an incremental level 0 backup using my script 5_incremental_level_0.sh:

[oracle@dc1sbxdb001 demo]$ ./5_incremental_level_0.sh
-----------------------
Step 1: Set environment
-----------------------

Setting the Database Environment using oraenv...
The Oracle base remains unchanged with value /u01/app/oracle

ORACLE_SID: ZEDDBA
ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

Press Enter to continue

The environment is set to my ZEDDBA database, then next the incremental backup (Level 0) is taken:

---------------------------------------
Step 2: Take Incremental Level 0 Backup
---------------------------------------

Cotent of 5_incremental_level_0.cmd file:

BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR LEVEL 0';
HOST 'read Press Enter to LIST BACKUP';
LIST BACKUP;

Press Enter to continue

Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/5_incremental_level_0.cmd'

Recovery Manager: Release 12.2.0.1.0 - Production on Mon May 20 17:13:13 2019

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

connected to target database: ZEDDBA (DBID=3520942925)

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR LEVEL 0';
2> HOST 'read Press Enter to LIST BACKUP';
3> LIST BACKUP;
4> 
Starting backup at 20-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 20-MAY-19
channel ORA_DISK_1: finished piece 1 at 20-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_20/o1_mf_nnnd0_INCR_LEVEL_0_gg5njx01_.bkp tag=INCR LEVEL 0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 20-MAY-19

Starting Control File and SPFILE Autobackup at 20-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_20/o1_mf_s_1008782004_gg5nk4cs_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-MAY-19


host command complete

We use host within RMAN just to wait for input before moving on for demo purposes.  Next we list the backup within RMAN using ‘LIST BACKUP‘:

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 498.93M DISK 00:00:08 16-MAY-19 
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 8.31M DISK 00:00:00 16-MAY-19 
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190516T173912
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008437952_gfv4kjko_.bkp
SPFILE Included: Modification time: 16-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 353836 Ckp time: 16-MAY-19

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3 77.83M DISK 00:00:01 16-MAY-19 
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4y7m2_.bkp

List of Archived Logs in backup set 3
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 332298 16-MAY-19 354044 16-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 498.94M DISK 00:00:06 16-MAY-19 
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174603
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_TAG20190516T174603_gfv4yco3_.bkp
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5 3.50K DISK 00:00:00 16-MAY-19 
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4ym4v_.bkp

List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 14 354044 16-MAY-19 354066 16-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 8.31M DISK 00:00:00 16-MAY-19 
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174612
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008438372_gfv4ynmv_.bkp
SPFILE Included: Modification time: 16-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 354077 Ckp time: 16-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 8.31M DISK 00:00:00 17-MAY-19 
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20190517T165453
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_17/o1_mf_s_1008521693_gfxpbfs7_.bkp
SPFILE Included: Modification time: 17-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 458035 Ckp time: 17-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Incr 0 511.64M DISK 00:00:05 20-MAY-19 
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 0
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_20/o1_mf_nnnd0_INCR_LEVEL_0_gg5njx01_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 0 Incr 571045 20-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 8.31M DISK 00:00:00 20-MAY-19 
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20190520T171324
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_20/o1_mf_s_1008782004_gg5nk4cs_.bkp
SPFILE Included: Modification time: 20-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 571056 Ckp time: 20-MAY-19

Recovery Manager complete.

Press Enter to continue

Finally, we update the demo log table:

-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log

1 row created.

Commit complete.

WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup
16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs
17-MAY-19 04.55.12.000000 PM Image Copy
20-MAY-19 05.13.44.000000 PM Incremental Level 0

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$
Reference Scripts
  1. 5_incremental_level_0.sh
  2. 5_incremental_level_0.cmd

To download all 2 in one zip: 5_incremental_level_0.zip

The rest of the series
  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. RMAN Obsolete

Please Note: Links to the blog posts will be released daily and updated here.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

RMAN Image Copy & Demo

Fri, 2019-05-24 09:38

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Image Copy

An Image copy backup are exact copies of the datafiles including the free space.  They are not stored in RMAN backup pieces but as actual datafiles, therefore are a bit-for-bit copy.

Image Copy Demo

We take an image copy backup using my script 4_image_copy.sh:

[oracle@dc1sbxdb001 demo]$ ./4_image_copy.sh 
-----------------------
Step 1: Set environment
-----------------------

Setting the Database Environment using oraenv...
The Oracle base remains unchanged with value /u01/app/oracle

ORACLE_SID: ZEDDBA
ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

Press Enter to continue

The environment is set to my ZEDDBA database, then next the image copy is taken:

------------------------------
Step 2: Take Image Copy Backup
------------------------------

Cotent of 4_image_copy.cmd file:

BACKUP AS COPY DATABASE TAG 'IMAGE COPY';
HOST 'read Press Enter to LIST BACKUP';
LIST COPY;

Press Enter to continue

Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/4_image_copy.cmd'

Recovery Manager: Release 12.2.0.1.0 - Production on Fri May 17 16:54:31 2019

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

connected to target database: ZEDDBA (DBID=3520942925)

RMAN> BACKUP AS COPY DATABASE TAG 'IMAGE COPY';
2> HOST 'read Press Enter to LIST BACKUP';
3> LIST COPY;
4> 
Starting backup at 17-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_system_gfxp9txc_.dbf tag=IMAGE COPY RECID=1 STAMP=1008521678
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_sysaux_gfxpb246_.dbf tag=IMAGE COPY RECID=2 STAMP=1008521685
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_undotbs1_gfxpb9b0_.dbf tag=IMAGE COPY RECID=3 STAMP=1008521691
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
output file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_users_gfxpbdgo_.dbf tag=IMAGE COPY RECID=4 STAMP=1008521693
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-MAY-19

Starting Control File and SPFILE Autobackup at 17-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_17/o1_mf_s_1008521693_gfxpbfs7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-MAY-19

host command complete

We use host within RMAN just to wait for input before moving on for demo purposes.  Next we list the backup within RMAN using ‘LIST COPY‘:

specification does not match any control file copy in the repository
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
1 1 A 17-MAY-19 458020 17-MAY-19 NO 
Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_system_gfxp9txc_.dbf
Tag: IMAGE COPY

2 2 A 17-MAY-19 458023 17-MAY-19 NO 
Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_sysaux_gfxpb246_.dbf
Tag: IMAGE COPY

3 3 A 17-MAY-19 458027 17-MAY-19 NO 
Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_undotbs1_gfxpb9b0_.dbf
Tag: IMAGE COPY

4 4 A 17-MAY-19 458029 17-MAY-19 NO 
Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/datafile/o1_mf_users_gfxpbdgo_.dbf
Tag: IMAGE COPY

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

Key Thrd Seq S Low Time 
------- ---- ------- - ---------
3 1 15 A 16-MAY-19
Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/archivelog/2019_05_17/o1_mf_1_15_gfxp940y_.arc

Recovery Manager complete.

Press Enter to continue

Finally, we update the demo log table:

-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log

1 row created.

Commit complete.

WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup
16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs
17-MAY-19 04.55.12.000000 PM Image Copy

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$
Reference Scripts
  1. 4_image_copy.sh
  2. 4_image_copy.cmd

To download all 2 in one zip: 4_image_copy.zip

The rest of the series
  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. RMAN Obsolete

Please Note: Links to the blog posts will be released daily and updated here.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

RMAN Full Backup & Demo

Thu, 2019-05-23 09:13

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Full Backup

A full backup is a backup of all the database’s data which excludes free space and stores in RMAN backup pieces.  For example, if you have a newly created database with datafiles added that initial size of 10GB, the 10GB from each datafiles is mainly just free space and hence RMAN does not backup the free space, thus space efficient.

Archive Log Mode

When DML (Data Manipulation Language) is ran against a database, this is stored in online redo logs, so Oracle can reply DML in case of crash recovery to ensure database is consistent and that all committed data is present.  Online redo logs fill up and Oracle move onto the next group till reaches the last one and then go back to the first one, but in order to maintain the redo, Oracle “archive” the online redo log to archive logs, hence the word “archive”.

Archive redo logs along with online redo logs are required to recovery a database after a restored, because if the database is open it’s a moving target and the DML will need to be replayed so the the first and last datafile that were backups are consistent i.e. at the same point (SCN) if doing incomplete recovery.  Complete recovery is when all the archive redo logs and then online redo logs are applied, taking the database to the current SCN before the restore was done i.e. no data loss.

Demos Enable Archive Log Mode

Before we can take a full backup we need to enable archive log mode using my script 1_enable_archive_log_mode.sh:

[oracle@dc1sbxdb001 demo]$ ./1_enable_archive_log_mode.sh
-----------------------
Step 1: Set environment
-----------------------

Setting the Database Environment using oraenv...
The Oracle base has been set to /u01/app/oracle

ORACLE_SID: ZEDDBA
ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

Press Enter to continue

The environment is set to my ZEDDBA database, then next a table table is created to log each demo as they run in the table:

-----------------------------
Step 2: Create demo log table
-----------------------------

Content of 1_create_demo_table.sql file:

create table demo_log (when timestamp, comments varchar2(200));
exit

Press Enter to continue

Calling 'sqlplus / as sysdba @1_create_demo_table.sql'

Table created.

Press Enter to continue

Next we enable archive log mode:

-------------------------------
Step 3: Enable Archive Log Mode
-------------------------------

Content of 1_enable_archive_log_mode.sql file:

alter system set db_recovery_file_dest_size = 15G;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
insert into demo_log values (sysdate, 'Enable Archive Log Mode');
commit;
@/media/sf_Software/scripts/demo/demo_log.sql
exit

Press Enter to continue

Calling 'sqlplus / as sysdba @1_enable_archive_log_mode.sql'

System altered.

Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 2952790016 bytes
Fixed Size 8625080 bytes
Variable Size 1677722696 bytes
Database Buffers 1258291200 bytes
Redo Buffers 8151040 bytes
Database mounted.

Database altered.

Database altered.

1 row created.

Commit complete.

WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$
Full Database Backup

We take a full backup using my script 2_full_backup.sh:

[oracle@dc1sbxdb001 demo]$ ./2_full_backup.sh 
-----------------------
Step 1: Set environment
-----------------------

Setting the Database Environment using oraenv...
The Oracle base has been set to /u01/app/oracle

ORACLE_SID: ZEDDBA
ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

Press Enter to continue

The environment is set to my ZEDDBA database, then next the full backup is taken:

------------------------
Step 2: Take Full Backup
------------------------

Cotent of 2_full_backup.cmd file:

BACKUP DATABASE TAG 'FULL BACKUP';
HOST 'read Press Enter to LIST BACKUP';
LIST BACKUP;

Press Enter to continue

Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/2_full_backup.cmd'

Recovery Manager: Release 12.2.0.1.0 - Production on Thu May 16 17:38:52 2019

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

connected to target database: ZEDDBA (DBID=3520942925)

RMAN> BACKUP DATABASE TAG 'FULL BACKUP';
2> HOST 'read Press Enter to LIST BACKUP';
3> LIST BACKUP;
4> 
Starting backup at 16-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp tag=FULL BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 16-MAY-19

Starting Control File and SPFILE Autobackup at 16-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008437952_gfv4kjko_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-MAY-19

host command complete

We use host within RMAN just to wait for input before moving on.  Next we list the backup within RMAN using ‘LIST BACKUP‘:

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 498.93M DISK 00:00:08 16-MAY-19 
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 8.31M DISK 00:00:00 16-MAY-19 
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190516T173912
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008437952_gfv4kjko_.bkp
SPFILE Included: Modification time: 16-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 353836 Ckp time: 16-MAY-19

Recovery Manager complete.

Press Enter to continue

Finally, we update the demo log table:

-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log

1 row created.

Commit complete.

WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$
Full Database Backup with Archive Logs

We take a full backup with archive logs using my script 3_full_backup_plus_archivelogs.sh:

[oracle@dc1sbxdb001 demo]$ ./3_full_backup_plus_archivelogs.sh
-----------------------
Step 1: Set environment
-----------------------

Setting the Database Environment using oraenv...
The Oracle base has been set to /u01/app/oracle

ORACLE_SID: ZEDDBA
ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

Press Enter to continue

The environment is set to my ZEDDBA database, then next the full backup with archive logs is taken:

----------------------------------------
Step 2: Take Full Backup plus archivelog
----------------------------------------

Content of 3_full_backup_plus_archivelogs.cmd file:

BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT TAG 'FULL BACKUP PLUS ARCHIVELOG';
HOST 'read Press Enter to LIST BACKUP';
LIST BACKUP;

Press Enter to continue

Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/3_full_backup_plus_archivelogs.cmd'

Recovery Manager: Release 12.2.0.1.0 - Production on Thu May 16 17:45:54 2019

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

connected to target database: ZEDDBA (DBID=3520942925)

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT TAG 'FULL BACKUP PLUS ARCHIVELOG';
2> HOST 'read Press Enter to LIST BACKUP';
3> LIST BACKUP;
4>

Starting backup at 16-MAY-19
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=158 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=1 STAMP=1008438357
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4y7m2_.bkp tag=FULL BACKUP PLUS ARCHIVELOG comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/archivelog/2019_05_16/o1_mf_1_13_gfv4y4mk_.arc RECID=1 STAMP=1008438357
Finished backup at 16-MAY-19

Starting backup at 16-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_TAG20190516T174603_gfv4yco3_.bkp tag=TAG20190516T174603 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 16-MAY-19

Starting backup at 16-MAY-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=2 STAMP=1008438370
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4ym4v_.bkp tag=FULL BACKUP PLUS ARCHIVELOG comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ZEDDBA/archivelog/2019_05_16/o1_mf_1_14_gfv4ylo5_.arc RECID=2 STAMP=1008438370
Finished backup at 16-MAY-19

Starting Control File and SPFILE Autobackup at 16-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008438372_gfv4ynmv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-MAY-19

host command complete

We use host within RMAN just to wait for input before moving on for demo purposes.  Next we list the backup within RMAN using ‘LIST BACKUP‘:

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 498.93M DISK 00:00:08 16-MAY-19 
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_FULL_BACKUP_gfv4k119_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 Full 353825 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 8.31M DISK 00:00:00 16-MAY-19 
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190516T173912
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008437952_gfv4kjko_.bkp
SPFILE Included: Modification time: 16-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 353836 Ckp time: 16-MAY-19

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3 77.83M DISK 00:00:01 16-MAY-19 
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4y7m2_.bkp

List of Archived Logs in backup set 3
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 332298 16-MAY-19 354044 16-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 498.94M DISK 00:00:06 16-MAY-19 
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174603
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_nnndf_TAG20190516T174603_gfv4yco3_.bkp
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 Full 354058 16-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5 3.50K DISK 00:00:00 16-MAY-19 
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: FULL BACKUP PLUS ARCHIVELOG
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16/o1_mf_annnn_FULL_BACKUP_PLUS_ARC_gfv4ym4v_.bkp

List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 14 354044 16-MAY-19 354066 16-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 8.31M DISK 00:00:00 16-MAY-19 
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190516T174612
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_16/o1_mf_s_1008438372_gfv4ynmv_.bkp
SPFILE Included: Modification time: 16-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 354077 Ckp time: 16-MAY-19

Recovery Manager complete.

Press Enter to continue

Finally, we update the demo log table:

-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log

1 row created.

Commit complete.

WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup
16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$
Reference Scripts
  1. 1_enable_archive_log_mode.sh
  2. 1_enable_archive_log_mode.sql
  3. 1_create_demo_table.sql
  4. 2_full_backup.sh
  5. 2_full_backup.cmd
  6. 3_full_backup_plus_archivelogs.sh
  7. 3_full_backup_plus_archivelogs.cmd

To download all 7 in one zip: 1_full_backup.zip

The rest of the series
  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. RMAN Obsolete

Please Note: Links to the blog posts will be released daily and updated here.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

How to create an sosreport on Oracle Linux

Sun, 2019-03-10 13:18

When creating a SR for an issues on Oracle Linux, for example in an Exadata environment, you are quite often enough asked to run an sosreport.

What is sosreport?

“The “sosreport” is a tool to collect troubleshooting data on an Oracle Linux system. It generates a compressed tarball of debugging information that gives an overview of the most important logs and configuration of a Linux system, to be sent to Oracle Support.

Among other things, the sosreport includes information about the installed rpm versions, syslog, network configuration, mounted filesystems, disk partition details, loaded kernel modules and status of all services

It has a plugin-based architecture that enables features to be enabled or disabled, and additional functionality added.”

How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)

Why support needs sosreport?

“The sosreport collects system information from an Oracle Linux system by capturing various log files, configuration files and command outputs that helps in diagnosing a problem faster.

Since this collects most of the commonly sort information while troubleshooting problems, collecting a sosreport helps in reducing the number of iterations of data request from the customer.

The logs, configuration files and related command outputs provides a better picture about the system environment and thus it is very helpful for cases about Root cause analysis and on going issues.

The sosreport helps the support to identify configuration errors and make proactive recommendations too.”

How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)

How to use

To use, simple run “sosreport”:

[root@v1ex1dbadm01 ~]# sosreport

sosreport (version 3.2)

This command will collect diagnostic and configuration information from
this Oracle Linux system and installed applications.

An archive containing the collected information will be generated in
/tmp/sos.9gvK0N and may be provided to a Oracle USA support
representative.

Any information provided to Oracle USA will be treated in accordance
with the published support policies at:

http://linux.oracle.com/

The generated archive may contain data considered sensitive and its
content should be reviewed by the originating organization before being
passed to any third party.

No changes will be made to system configuration.

Press ENTER to continue, or CTRL-C to quit.

Please enter your first initial and last name [v1ex1dbadm01.v1.com]: Z Anwar
Please enter the case id that you are generating this report for []: 3-XXXXXXX1234

Setting up archive ...
Setting up plugins ...
Running plugins. Please wait ...

Running 70/70: xfs...
Creating compressed archive...

Your sosreport has been generated and saved in:
/tmp/sosreport-ZAnwar.3-XXXXXXX1234-20181004103417.tar.xz

The checksum is: 04d1a2b728216ba79df6cc38f801de6d

Please send this file to your support representative.

[root@v1ex1dbadm01 ~]#

You will then have a tar file at the end, which you can upload to your SR for your support engineer to analysis.

If you don’t have sosreport installed, then install the sos package:

[root@v1ex1dbadm01 ~]# yum install sos
References
More info, can be found in the following MOS note:
  • How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)
  • SRDC – How To Collect Sosreport on Oracle Linux and Oracle VM (Doc ID 1928183.1)
Related Posts

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

Moving file location of an Oracle database whilst preserving file permissions on Windows

Mon, 2019-02-04 12:16

You may want to move an Oracle database from one location to another on a Windows Server, for example one drive to another.  But it’s important to keep all the file permissions preserved, so Oracle can access.

To do this, is simple using xcopy with certain switches as explained in this Microsoft article:

https://support.microsoft.com/en-gb/help/323007/how-to-copy-a-folder-to-another-folder-and-retain-its-permissions

Below is where I needed to move oradata from u: drive to g: drive:

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Windows\system32>xcopy u:\oradata g:\oradata /O /X /E /H /K
Does G:\oradata specify a file name
or directory name on the target
(F = file, D = directory)? d
U:\oradata\PEPM\CONTROLFILE\O1_MF_FD9ZRZBP_.CTL
U:\oradata\PEPM\DATAFILE\O1_MF_PEPM_ST_FDW1GL8P_.DBF
U:\oradata\PEPM\DATAFILE\O1_MF_PEPM_TS_FDW1GX6J_.DBF
U:\oradata\PEPM\DATAFILE\O1_MF_PERFSTAT_FDWHDK7L_.DBF
U:\oradata\PEPM\DATAFILE\O1_MF_SYSAUX_FD9ZHRHO_.DBF
U:\oradata\PEPM\DATAFILE\O1_MF_SYSTEM_FD9ZL3SK_.DBF
U:\oradata\PEPM\DATAFILE\O1_MF_TEMP_FD9ZST99_.TMP
U:\oradata\PEPM\DATAFILE\O1_MF_UNDOTBS1_FD9ZO4DD_.DBF
U:\oradata\PEPM\DATAFILE\O1_MF_USERS_FD9ZO361_.DBF
U:\oradata\PEPM\ONLINELOG\O1_MF_1_FD9ZS2RH_.LOG
U:\oradata\PEPM\ONLINELOG\O1_MF_2_FD9ZS9P3_.LOG
U:\oradata\PEPM\ONLINELOG\O1_MF_3_FD9ZSJDT_.LOG
12 File(s) copied

C:\Windows\system32>

And fast_recovery_area from v: drive to i: drive:

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Windows\system32>xcopy v:\fast_recovery_area i:\fast_recovery_area /O /X /E /H /K
Does I:\fast_recovery_area specify a file name
or directory name on the target
(F = file, D = directory)? d
V:\fast_recovery_area\PEPM\CONTROLFILE\O1_MF_FD9ZRZGM_.CTL
V:\fast_recovery_area\PEPM\ONLINELOG\O1_MF_1_FD9ZS67G_.LOG
V:\fast_recovery_area\PEPM\ONLINELOG\O1_MF_2_FD9ZSDYD_.LOG
V:\fast_recovery_area\PEPM\ONLINELOG\O1_MF_3_FD9ZSMS6_.LOG
4 File(s) copied

C:\Windows\system32>

Without using this method, you would have to manually set the permissions, which is time consuming and error prone.

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

 

Categories: DBA Blogs

Resolving Slow Performance, Skipped Checks and Timeouts on Exa Check (exachk)

Wed, 2018-10-03 05:14
Background

For more information with regards to Exa Check, please read the following post:
How to use Oracle Exadata Database Machine Exa Check (exachk)

Slow Performance, Skipped Checks and Timeouts

When running the latest exachk (at time of writing, version 18.3.0_20180808), you may notice it takes a long time to run compared to the past.  This is due to the vast amount of additional checks carried out by the tool.  Due to this, you may also notice you get timeout issues reported in the report:

Killed Processes

exachk found that below commands were killed during the run, so some checks might have failed to execute properly. Refer to the “Slow Performance, Skipped Checks, and Timeouts” section of the user guide for corrective actions.

Killed check Manage ASM Audit File Directory Growth with cron (CHECK-ID: 9DEBED7B8DAB583DE040E50A1EC01BA0) at v1ex2dbadm01 because it timed out
Killed check Manage ASM Audit File Directory Growth with cron (CHECK-ID: 9DEBED7B8DAB583DE040E50A1EC01BA0) at v1ex2dbadm02 because it timed out

 

If you refer to the documentation “Slow Performance, Skipped Checks, and Timeouts“, you’ll see there are various parameters you can set in your environment to increase the default timeouts, which I have done below:

[root@v1ex2dbadm01 exachk]# export RAT_TIMEOUT=300
[root@v1ex2dbadm01 exachk]# export RAT_ROOT_TIMEOUT=900
[root@v1ex2dbadm01 exachk]# export RAT_PASSWORDCHECK_TIMEOUT=10
[root@v1ex2dbadm01 exachk]# export RAT_PROMPT_TIMEOUT=30
[root@v1ex2dbadm01 exachk]# export RAT_PROMPT_WAIT_TIMEOUT=60
[root@v1ex2dbadm01 exachk]# export RAT_REMOTE_RUN_TIMEOUT=10800
[root@v1ex2dbadm01 exachk]#
[root@v1ex2dbadm01 exachk]# env | grep RAT
RAT_ROOT_TIMEOUT=900
RAT_PROMPT_TIMEOUT=30
RAT_TIMEOUT=300
RAT_REMOTE_RUN_TIMEOUT=10800
RAT_PASSWORDCHECK_TIMEOUT=10
RAT_PROMPT_WAIT_TIMEOUT=60
[root@v1ex2dbadm01 exachk]#

Now when you run exachk, it will wait longer before killing processes.

In addition, if you run the “-dbparallelmax” option, you will increase the number of slave processes for database checks:

[root@v1ex2dbadm01 exachk]# ./exachk -dbparallelmax

PLEASE NOTE: This will consume more resources but will run quicker, so use with caution.  Alternatively you can run with “-dbparallel” with a acceptable number of processes and increase as per your requirements.

Now you should not have any timeouts and if you still do, then you will need to review the parameters above and increase again.  Alternatively raise an Support Request with Oracle Support.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

Extending the root LVM Partition on Exadata

Tue, 2018-09-11 10:09

On an Oracle Exadata Database Machine, the ‘/’ (root) is defaulted to a size of 30Gb, which can easily fill up.  Luckily this is just a Logical Volume and there’s normally lots of space available on the Logical Volume Group which is usually untapped.

Extending ‘/’

Identify how much space is used and free on ‘/’ using df:

[root@v1ex1dbadm01 ~]# df -h /
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VGExaDb-LVDbSys1
                       30G   22G  6.2G  79% /
[root@v1ex1dbadm01 ~]#

Display the current logical volume configuration using the lvs command:

[root@v1ex1dbadm01 ~]# lvs -o lv_name,lv_path,vg_name,lv_size
  LV                 Path                            VG      LSize
  LVDbOra1           /dev/VGExaDb/LVDbOra1           VGExaDb 200.00g
  LVDbSwap1          /dev/VGExaDb/LVDbSwap1          VGExaDb  24.00g
  LVDbSys1           /dev/VGExaDb/LVDbSys1           VGExaDb  30.00g
  LVDbSys2           /dev/VGExaDb/LVDbSys2           VGExaDb  30.00g
  LVDoNotRemoveOrUse /dev/VGExaDb/LVDoNotRemoveOrUse VGExaDb   1.00g
[root@v1ex1dbadm01 ~]#

PLEASE NOTE: On Exadata there are 2 SYS volumes, of which one is active and the other inactive.  These are used when patching the compute node, as one is a backup of the current and is used for rollback purposes.

Check the online resize option is available using the tune2fs command:

[root@v1ex1dbadm01 ~]# tune2fs -l /dev/VGExaDb/LVDbSys1 | grep resize_inode
Filesystem features:      has_journal ext_attr resize_inode dir_index filetype needs_recovery extent flex_bg sparse_super large_file huge_file uninit_bg dir_nlink extra_isize
[root@v1ex1dbadm01 ~]# tune2fs -l /dev/VGExaDb/LVDbSys2 | grep resize_inode
Filesystem features:      has_journal ext_attr resize_inode dir_index filetype needs_recovery extent flex_bg sparse_super large_file huge_file uninit_bg dir_nlink extra_isize
[root@v1ex1dbadm01 ~]#

If not available then the file system needs to be unmounted before resizing.  Refer to documentation:

Extending the root LVM Partition on Systems Running Oracle Exadata System Software Earlier than Release 11.2.3.2.1

Verify there’s space available in the Logical Volume Group using vgdisplay command:

[root@v1ex1dbadm01 ~]# vgdisplay -s
  "VGExaDb" 1.63 TiB  [285.00 GiB used / 1.36 TiB free]
[root@v1ex1dbadm01 ~]#

Finally if there’s enough space, then extend the Logical Volumes using lvextend command:

[root@v1ex1dbadm01 ~]# lvextend -L +70G /dev/VGExaDb/LVDbSys1
  Size of logical volume VGExaDb/LVDbSys1 changed from 30.00 GiB (7680 extents) to 100.00 GiB (25600 extents).
  Logical volume LVDbSys1 successfully resized.
[root@v1ex1dbadm01 ~]#
[root@v1ex1dbadm01 ~]# lvextend -L +70G /dev/VGExaDb/LVDbSys2
  Size of logical volume VGExaDb/LVDbSys2 changed from 30.00 GiB (7680 extents) to 100.00 GiB (25600 extents).
  Logical volume LVDbSys2 successfully resized.
[root@v1ex1dbadm01 ~]#

Followed by a resize of the file system using resize2fs command:

[root@v1ex1dbadm01 ~]# resize2fs /dev/VGExaDb/LVDbSys1
resize2fs 1.43-WIP (20-Jun-2013)
Filesystem at /dev/VGExaDb/LVDbSys1 is mounted on /; on-line resizing required
old_desc_blocks = 2, new_desc_blocks = 7
Performing an on-line resize of /dev/VGExaDb/LVDbSys1 to 26214400 (4k) blocks.
The filesystem on /dev/VGExaDb/LVDbSys1 is now 26214400 blocks long.
[root@v1ex1dbadm01 ~]#

The inactive SYS volume may give you errors as shown below:

[root@v1ex1dbadm01 ~]# resize2fs /dev/VGExaDb/LVDbSys2
resize2fs 1.43-WIP (20-Jun-2013)
Please run 'e2fsck -f /dev/VGExaDb/LVDbSys2' first.
[root@v1ex1dbadm01 ~]#

In which case, you just need to run the command to check the file system for error that may have occurred with journal-ling after unclear shutdown:

[root@v1ex1dbadm01 ~]# e2fsck -f /dev/VGExaDb/LVDbSys2
e2fsck 1.43-WIP (20-Jun-2013)
Pass 1: Checking inodes, blocks, and sizes
Pass 2: Checking directory structure
Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information
/dev/VGExaDb/LVDbSys2: 111629/1966080 files (0.1% non-contiguous), 5031185/7864320 blocks
[root@v1ex1dbadm01 ~]#

Now re-run the resize of the file system using resize2fs command:

[root@v1ex1dbadm01 ~]# resize2fs /dev/VGExaDb/LVDbSys2
resize2fs 1.43-WIP (20-Jun-2013)
Resizing the filesystem on /dev/VGExaDb/LVDbSys2 to 26214400 (4k) blocks.
The filesystem on /dev/VGExaDb/LVDbSys2 is now 26214400 blocks long.
[root@v1ex1dbadm01 ~]#

You should now see ‘/’ with additional 70Gb less formatting:

[root@v1ex1dbadm01 ~]# df -h /
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VGExaDb-LVDbSys1
                       99G   22G   72G  24% /
[root@v1ex1dbadm01 ~]#

Also see the Logical Volumes are now 100Gb from 30Gb:

[root@v1ex1dbadm01 ~]# lvs -o lv_name,lv_path,vg_name,lv_size
  LV                 Path                            VG      LSize
  LVDbOra1           /dev/VGExaDb/LVDbOra1           VGExaDb 200.00g
  LVDbSwap1          /dev/VGExaDb/LVDbSwap1          VGExaDb  24.00g
  LVDbSys1           /dev/VGExaDb/LVDbSys1           VGExaDb 100.00g
  LVDbSys2           /dev/VGExaDb/LVDbSys2           VGExaDb 100.00g
  LVDoNotRemoveOrUse /dev/VGExaDb/LVDoNotRemoveOrUse VGExaDb   1.00g
[root@v1ex1dbadm01 ~]#

Documentation for reference:
Extending the root LVM Partition on Systems Running Oracle Exadata System Software Release 11.2.3.2.1 or Later

Related Post:
Extending a Non-root LVM Partition on Exadata

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

Index Monitoring in Oracle Database

Tue, 2018-07-17 10:34

Applies to ONLY Oracle Database 10gR1 through to 12cR1.
In Oracle Database 12cR2, this feature is replaced.  A new blog post of this feature will be posted in due course.

Since Oracle 10g, you can monitor indexes to see if they are being used or not.  Which is very useful as indexes, consume unnecessary CPU and I/O on DML activity if not used.  Therefore, it’s recommended to monitor indexes and any unused indexes can be dropped, not only freeing vital space but CPU and I/O, resulting in better throughput.

It recommended to enable index monitoring for an appropriate period that is representative of your full workload cycle.  For example if you run a report every week, month or even year, just to catch all those activities.

Please Note: It’s important that foreign key constraints have associated indexes to avoid any table level lock on DML.  These indirect uses of indexes will not be detected by index monitoring.  So be mindful when dropping indexes that are associated with foreign key constraint.

Enable Index Monitoring

To Enable Index Monitoring, you alter the index as shown below:

ALTER INDEX . MONITORING USAGE;
Checking Index Usage

To check if the index has been used, will depends on the version of Oracle:

Oracle 10g/11g
SELECT * FROM V$OBJECT_USAGE;

Database Reference 10g – V$OBJECT_USAGE
Database Reference 11g – V$OBJECT_USAGE

Please Note: Only shows the indexes belonging to the login user.

To see all indexes in the database regardless of the logged on user:

select 
do.owner,
t.name table_name, io.name index_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
ou.start_monitoring,
ou.end_monitoring 
from 
sys.obj$ io, 
sys.obj$ t, 
sys.ind$ i, 
sys.object_usage ou,
dba_objects do 
where i.obj# = ou.obj# 
and io.obj# = ou.obj# 
and t.obj# = i.bo#
and i.obj# = do.object_id
order by 1, 2, 3;

DISCLAIMER: As accessing data dictionary objects directly, this query is not guaranteed to work.

Oracle 12cR1

“The V$OBJECT_USAGE view is deprecated in Oracle Database 12c Release 1 (12.1) and maintained for backward compatibility. Support for this view may be removed in a future release. Oracle recommends that you use the USER_OBJECT_USAGE view instead of the V$OBJECT_USAGE view.”

Database Reference 12cR1 – V$OBJECT_USAGE

For the current logon user:

SELECT * FROM USER_OBJECT_USAGE;

Database Reference 12cR1 – USER_OBJECT_USAGE

For all users:

SELECT * FROM DBA_OBJECT_USAGE;

Database Reference 12cR1 – DBA_OBJECT_USAGE

Dis-enable Index Monitoring

To Dis-enable Index Monitoring, you alter the index as shown below:

ALTER INDEX . NOMONITORING USAGE;
Demo of Index Monitoring

The content of the index_monitoring_demo.sql is below:

-------------------------------------------------------------
--
-- Index Monitoring Demo
--
-- File Name: index_monitoring_demo.sql (blog.zeddba.com)
-- Created: Zahid Anwar (ZedDBA)
-- 
-- Date: 16/07/2018
-- Version: 1.0
--
-- History
--
--1.0 16/07/2018 ZA Initial Script
--
-------------------------------------------------------------
set echo on
set pages 999 lines 400

CREATE TABLE "EMP"
( "ID" NUMBER,
"FIRST_NAME" VARCHAR2(50),
"LAST_NAME" VARCHAR2(50)
);

pause Press Enter

INSERT INTO "EMP" VALUES (1, 'Zahid', 'Anwar');
INSERT INTO "EMP" VALUES (2, 'Scott', 'Tiger');
INSERT INTO "EMP" VALUES (3, 'Joe', 'Bloggs');

commit;

pause Press Enter

set autotrace on
col id format 99
col first_name format a50
col last_Name format a50

select * from emp;

pause Press Enter

create index emp_id on emp (id);

pause Press Enter

select * from emp where id = 1;

pause Press Enter

set autotrace off

col INDEX_NAME format a20
col TABLE_NAME format a20
col MONITORING format a15
col USED format a10
select * from USER_OBJECT_USAGE;

pause Press Enter

alter index emp_id monitoring usage;

pause Press Enter

select * from USER_OBJECT_USAGE;

pause Press Enter

set autotrace on

select * from emp;

pause Press Enter

set autotrace off

select * from USER_OBJECT_USAGE;

pause Press Enter

set autotrace on

select * from emp where id = 1;

pause Press Enter

set autotrace off

select * from USER_OBJECT_USAGE;

pause Press Enter

alter index emp_id nomonitoring usage;

pause Press Enter

select * from USER_OBJECT_USAGE;

pause Press Enter

DROP TABLE "EMP" PURGE;

Below is the output of the script index_monitoring_demo.sql:

SQL> @index_monitoring_demo.sql
SQL> set pages 999 lines 400
SQL>
SQL> CREATE TABLE "EMP"
2 ( "ID" NUMBER,
3 "FIRST_NAME" VARCHAR2(50),
4 "LAST_NAME" VARCHAR2(50)
5 );

Table created.

SQL>
SQL> pause Press Enter
Press Enter

Table EMP is created in my schema ZEDDBA, please ensure you have appropriate schema with correct privileges.  Next we insert 3 rows into the table EMP:

SQL> INSERT INTO "EMP" VALUES (1, 'Zahid', 'Anwar');

1 row created.

SQL> INSERT INTO "EMP" VALUES (2, 'Scott', 'Tiger');

1 row created.

SQL> INSERT INTO "EMP" VALUES (3, 'Joe', 'Bloggs');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> pause Press Enter
Press Enter

With the 3 rows inserted and committed, we select from the EMP table with auto trace on, so we can see the execution plan:

SQL> set autotrace on
SQL> col id format 99
SQL> col first_name format a50
SQL> col last_Name format a50
SQL>
SQL> select * from emp;

ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
2 Scott Tiger
3 Joe Bloggs


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| EMP | 3 | 201 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
784 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

SQL>
SQL> pause Press Enter
Press Enter

Please Note: This database is an Exadata Machine, hence the “TABLE ACCESS STORAGE FULL“, otherwise would be “TABLE ACCESS FULL“.

As we can see the Execution Plan was a Full Table Scan (FTS) as this was the only access method available.  Next, we create an index on the emp_id:

SQL> create index emp_id on emp (id);

Index created.

SQL>
SQL> pause Press Enter
Press Enter

Next, we query the table with a predicate on emp_id:

SQL> select * from emp where id = 1;

ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar


Execution Plan
----------------------------------------------------------
Plan hash value: 458854468

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 67 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> pause Press Enter
Press Enter

Now, we can see the Execution Plan used the newly created index.  Next, we check if the index was used:

SQL> set autotrace off
SQL>
SQL> col INDEX_NAME format a20
SQL> col TABLE_NAME format a20
SQL> col MONITORING format a15
SQL> col USED format a10
SQL> select * from USER_OBJECT_USAGE;

no rows selected

SQL>
SQL> pause Press Enter
Press Enter

We get no rows back as we didn’t turn on index monitoring, which isn’t on by default.  So next, we enable index monitoring:

SQL> alter index emp_id monitoring usage;

Index altered.

SQL>
SQL> pause Press Enter
Press Enter

Next, we check the index usage and we can see it monitoring but not used:

SQL> select * from USER_OBJECT_USAGE;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID     EMP        YES        NO   07/16/2018 17:53:59

SQL>
SQL> pause Press Enter
Press Enter

Next, we query the table with no predicate to do a FTS:

SQL> set autotrace on
SQL>
SQL> select * from emp;

ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar
2 Scott Tiger
3 Joe Bloggs


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 201 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| EMP | 3 | 201 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
51 recursive calls
0 db block gets
54 consistent gets
0 physical reads
132 redo size
784 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
3 rows processed

SQL>
SQL> pause Press Enter
Press Enter

Next, we check if the index monitoring reflects the usage:

SQL> set autotrace off
SQL>
SQL> select * from USER_OBJECT_USAGE;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID     EMP        YES        NO   07/16/2018 17:53:59

SQL>
SQL> pause Press Enter
Press Enter

Next, we query the table with a predicate to do Index Range Scan:

SQL> set autotrace on
SQL>
SQL> select * from emp where id = 1;

ID FIRST_NAME LAST_NAME
--- -------------------------------------------------- --------------------------------------------------
1 Zahid Anwar


Execution Plan
----------------------------------------------------------
Plan hash value: 458854468

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 67 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
13 recursive calls
3 db block gets
24 consistent gets
0 physical reads
876 redo size
695 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> pause Press Enter
Press Enter

Next, we check if the index monitoring reflects the usage:

SQL> set autotrace off
SQL>
SQL> select * from USER_OBJECT_USAGE;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID     EMP        YES        YES  07/16/2018 17:53:59

SQL>
SQL> pause Press Enter
Press Enter

Next, we now turn off index monitoring, as we know it’s been used:

SQL> alter index emp_id nomonitoring usage;

Index altered.

SQL>
SQL> pause Press Enter
Press Enter

Next, we check to see if monitoring is now disabled:

SQL> select * from USER_OBJECT_USAGE;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- -------------------- --------------- ---------- ------------------- -------------------
EMP_ID     EMP        NO         YES  07/16/2018 17:53:59 07/16/2018 17:54:09

SQL>
SQL> pause Press Enter
Press Enter

Finally, we drop the demo EMP table:

SQL> DROP TABLE "EMP" PURGE;

Table dropped.

SQL>

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

Oracle Exadata Smart Flash Logging

Tue, 2018-02-06 11:56
What is Exadata Smart Flash Logging?

In an OLTP environment, it is crucial to have fast response times to redo log writes i.e. low latency.  When multiplexing redo logs for high availability i.e. to protect against hardware failure, redo log writes are only acknowledged when redo is written to all redo log members i.e when the slowest disk completes the write.  By this nature, whenever a disk slows down even if for a moment it can have impact on redo log performance and throughput.

Flash alone can’t resolve this issue as flash can also momentarily slow down due to issues in erase cycles or wear leveling and remember the acknowledgement is only given when the redo is written to all redo log members.

Exadata Smart Flash Logging, is the feature that writes to both hard disk and flash with the acknowledgement given as soon as either completes the write, thus improving response time and throughput.  So if a write is slow to hard disk the flash will give a quicker acknowledgement but when flash is experiencing a slow down due to erase cycles or wear leveling then the hard disk will acknowledge, smoothing out response times.

The Exadata Smart Flash Cache isn’t permanent but a temporary store to provide fast response times by storing redo until it’s safely written to disk.

No changes are required to redo log configuration and is transparent to database and recovery.

How to enable Smart Flash Logging?

It’s enabled out the box or for older systems it’s enabled when applying cell patch version 11.2.2.4 and also requires Database 11.2.0.2 Bundle Patch 11 or higher.

How to disable Smart Flash Logging?

This shouldn’t be done unless instructed to do so by Oracle Support or Development.

How much flash is used by Smart Flash Logging?

By default just 512Mb is used per cell, which should be sufficient for most situations.   It’s a small investment for huge performance benefit.  Statistics record the number of successful write and unsuccessful writes due to the temporary space filled.  In which case the size may need to be increased.  Also I/O Resource Manager (IORM) can be used to disable Smart Flash Logging for none critical databases.

Do standby redo logs use Smart Flash Logging?

Yes, standby redo logs benefit from Smart Flash Logging just as redo logs as long as cell patch 11.2.2.4 or higher is applied and Database 11.2.0.2 Bundle Patch 11 or higher is applied.

How to check that Smart Flash Logging is configured?

Using CellCLI run “LIST FLASHLOG DETAIL” and if output is returned as shown below with the details, then this means that Smart Flash Logging is configured:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e "list flashlog detail"
 v1ex1celadm01: name: v1ex1celadm01_FLASHLOG
 v1ex1celadm01: cellDisk: FD_00_v1ex1celadm01,FD_01_v1ex1celadm01
 v1ex1celadm01: creationTime: 2015-06-28T17:52:43+01:00
 v1ex1celadm01: degradedCelldisks:
 v1ex1celadm01: effectiveSize: 512M
 v1ex1celadm01: efficiency: 100.0
 v1ex1celadm01: id: 366421ec-bf77-499e-870f-f0cf5390343e
 v1ex1celadm01: size: 512M
 v1ex1celadm01: status: normal
 v1ex1celadm02: name: v1ex1celadm02_FLASHLOG
 v1ex1celadm02: cellDisk: FD_01_v1ex1celadm02,FD_00_v1ex1celadm02
 v1ex1celadm02: creationTime: 2015-06-28T17:52:44+01:00
 v1ex1celadm02: degradedCelldisks:
 v1ex1celadm02: effectiveSize: 512M
 v1ex1celadm02: efficiency: 100.0
 v1ex1celadm02: id: 9f670843-c9cc-4156-a32e-8d23fa79cdb8
 v1ex1celadm02: size: 512M
 v1ex1celadm02: status: normal
 v1ex1celadm03: name: v1ex1celadm03_FLASHLOG
 v1ex1celadm03: cellDisk: FD_01_v1ex1celadm03,FD_00_v1ex1celadm03
 v1ex1celadm03: creationTime: 2015-06-28T17:52:33+01:00
 v1ex1celadm03: degradedCelldisks:
 v1ex1celadm03: effectiveSize: 512M
 v1ex1celadm03: efficiency: 100.0
 v1ex1celadm03: id: 749bada6-8ae2-4c51-8410-97622f9a9532
 v1ex1celadm03: size: 512M
 v1ex1celadm03: status: normal
[root@v1ex1dbadm01 ~]#

For more info:
Exadata Smart Flash Logging FAQ (Doc ID 1372894.1)
Oracle Exadata Whitepaper:  Exadata Smart Flash Cache Features and the Oracle Exadata Database Machine

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

How to check if Exadata Write-Back Flash Cache is Enabled

Mon, 2018-01-22 06:14
What is Exadata Write-Back Flash Cache?

Exadata Write-Back Flash Cache provides the ability to cache not only read I/Os but write I/O to the Exadata’s PCI flash on the storage cells.  Exadata storage software 11.2.3.2.1 or higher and Grid Infrastructure and Database software 11.2.0.3.9 or higher is required to use Exadata Write-Back Flash Cache, which is persistent across storage cell restarts.

The default since April 2017 for the Oracle Exadata Deployment Assistant (OEDA) is Write-Back Flash Cache when DATA diskgroup is HIGH redundancy and Grid Infrastructure and Database software are:

  • 11.2.0.4.1 or higher
  • 12.1.0.2 or higher
  • 12.2.0.2 or higher

PLEASE NOTE: This option is only applicable to High Capacity as Extreme Flash doesn’t have Hard Disks and therefore Write-Back Flash Cache is explicitly enabled and can’t be disabled.

What are the Performance Benefit of Exadata Write-Back Flash Cache?

Write-Back Flash Cache can significantly improve write intensive operations because writing to Flash Cache is significantly faster than writing to Hard Disks.  Depending on the workload, write performance (IOPS) can be improved by 10x on older generations of Exadata Machines V2 and X2 and 20x on newer generations X3 onwards (correct at time of writing).

If you are experiencing high write I/O times on storage cells from AWR Reports or Storage Cell metrics, then you should consider enabling Write-Back Flash Cache to alleviate write operations on Hard Disks and move to Flash Cache.

See the following My Oracle Support (MOS) Note for more info:
Exadata Write-Back Flash Cache – FAQ (Doc ID 1500257.1)

How to check if Exadata Write-Back Flash Cache is Enabled?

To check if Exadata Write-Back Flash Cache is enabled, run “list cell attributes flashcachemode” on the storage cell using CellCLI as shown below:

[root@v1ex2celadm01 ~]# cellcli
CellCLI: Release 12.1.2.3.5 - Production on Wed Jan 17 10:09:51 GMT 2018

Copyright (c) 2007, 2016, Oracle. All rights reserved.

CellCLI> list cell attributes flashcachemode
 WriteThrough

CellCLI> exit
quitting

[root@v1ex2celadm01 ~]#

If “WriteThrough” then Write-Back Flash Cache is disabled (writes go straight to hard disk and then can be placed in flash for caching reads if required), otherwise if “WriteBack” then Write-Back Flash Cache is enabled as the name suggests (writes go straight to flash and then can be moved to hard disk if aged or not required for read caching).

You can also run “list cell detail” using CellCLI as shown below:

[root@v1ex2celadm01 ~]# cellcli
CellCLI: Release 12.1.2.3.5 - Production on Wed Jan 17 10:10:22 GMT 2018

Copyright (c) 2007, 2016, Oracle. All rights reserved.

CellCLI> list cell detail
 name: v1ex2celadm01
 accessLevelPerm: remoteLoginEnabled
 bbuStatus: normal
 cellVersion: OSS_12.1.2.3.5_LINUX.X64_170418
 cpuCount: 16/32
 diagHistoryDays: 7
 eighthRack: TRUE
 fanCount: 8/8
 fanStatus: normal
 flashCacheMode: WriteThrough
 id: xxxxxxxxxx
 interconnectCount: 2
 interconnect1: ib0
 interconnect2: ib1
 iormBoost: 6.4
 ipaddress1: 10.1.11.14/22
 ipaddress2: 10.1.11.15/22
 kernelVersion: 2.6.39-400.294.4.el6uek.x86_64
 locatorLEDStatus: off
 makeModel: Oracle Corporation ORACLE SERVER X5-2L High Capacity
 memoryGB: 95
 metricHistoryDays: 7
 notificationMethod: snmp
 notificationPolicy: critical,warning,clear
 offloadGroupEvents:
 powerCount: 2/2
 powerStatus: normal
 releaseImageStatus: success
 releaseVersion: 12.1.2.3.5.170418
 rpmVersion: cell-12.1.2.3.5_LINUX.X64_170418-1.x86_64
 releaseTrackingBug: 25509078
 rollbackVersion: 12.1.2.3.4.170111
 securityCert: PrivateKey OK
 Certificate: Subject CN=v1ex2celadm01.v1.com,OU=Oracle Exadata,O=Oracle Corporation,L=Redwood City,ST=California,C=US
 Issuer CN=v1ex2celadm01.v1.com,OU=Oracle Exadata,O=Oracle Corporation,L=Redwood City,ST=California,C=US
 snmpSubscriber: host=v1ex2dbadm02.v1.com,port=1830,community=public
 host=v1ex2dbadm01.v1.com,port=1830,community=public
 host=v1ex2dbadm01.v1.com,port=3872,community=public
 host=v1ex2dbadm02.v1.com,port=3872,community=public
 status: online
 temperatureReading: 24.0
 temperatureStatus: normal
 upTime: 105 days, 7:35
 usbStatus: normal
 cellsrvStatus: running
 msStatus: running
 rsStatus: running

CellCLI> exit
quitting

[root@v1ex2celadm01 ~]#

However, the simpler way to check is via dcli, especially when you have lots of storage cells as shown below:

[root@v1ex2dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e "list cell attributes flashcachemode"
v1ex2celadm01: WriteThrough
v1ex2celadm02: WriteThrough
v1ex2celadm03: WriteThrough

Related Posts:
How to Enable Exadata Write-Back Flash Cache

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs

How to use the Oracle Exadata Diagnostics Collection Tool (sundiag.sh)

Wed, 2018-01-17 09:16
What is the Oracle Exadata Diagnostics Collection Tool sundiag.sh

Very often when creating a Support Request (SR) for an issue on an Oracle Exadata Database Machine, you’ll need to run the script “sundiag.sh“.  Which is the “Oracle Exadata Database Machine – Diagnostics Collection Tool“.

The tool collects a lot of diagnostics information that assist the support analyst in diagnosing your problem, such as failed hardware like a failed disk, etc.

More information can be found on My Oracle Support (MOS) Note:
SRDC – EEST Sundiag (Doc ID 1683842.1)
Oracle Exadata Diagnostic Information required for Disk Failures and some other Hardware issues (Doc ID 761868.1)

How to run the Diagnostics Collection Tool

To run “sundiag.sh“, is very simple as shown below:

[root@v1ex1dbadm01 ~]# /opt/oracle.SupportTools/sundiag.sh
Oracle Exadata Database Machine - Diagnostics Collection Tool
Gathering Linux information
Skipping collection of OSWatcher/ExaWatcher logs, Cell Metrics and Traces
Skipping ILOM collection. Use the ilom or snapshot options, or login to ILOM
over the network and run Snapshot separately if necessary.
/var/log/exadatatmp/sundiag_v1ex1dbadm01_xxxxxxxxxx_2018_01_17_13_49
Gathering dbms information
Generating diagnostics tarball and removing temp directory
==============================================================================
Done. The report files are bzip2 compressed in /var/log/exadatatmp/sundiag_v1ex1dbadm01_xxxxxxxxxx_2018_01_17_13_49.tar.bz2
==============================================================================
[root@v1ex1dbadm01 ~]#

For more advanced collections, use the option switches to override default behaviour as shown in the help:

[root@v1ex1dbadm01 ~]# /opt/oracle.SupportTools/sundiag.sh -h

Oracle Exadata Database Machine - Diagnostics Collection Tool

Version: 12.1.2.3.3.161109

By default sundiag will collect OSWatcher/ExaWatcher, Cell Metrics and traces,
if there was an alert in the last 7 days. If there is more than one alert, latest
alert is chosen to set the time range for data collection.
Time range is 8hrs prior to and 1hr after the latest alert, for the total of 9 hrs
e.g: latest alert timestamp = 2014-03-29T01:20:04-05:00
 echo Time range = 2014-03-28_16:00:00 and 2014-03-29_01:00:00
User can also specify time ranges (as explained in usage below), which takes
precedence over default behavior of checking for alerts

Usage: /opt/oracle.SupportTools/sundiag.sh [ilom | snapshot] [osw <time ranges>]
 osw - This argument when used expects value of one or more comma separated
 time ranges. OSWatcher/ExaWatcher, cell metrics and traces will be gathered
 in those time ranges.
 The format for time range(s) is <from>-<to>,<from>-<to> and so on without spaces
 where <from> and <to> format is <date>_<time>
 <date> and <time> format should be any valid format that can be recognized by
 'date' command. The command 'date -d <date>' or 'date -d <time>' should be valid
 e.g: /opt/oracle.SupportTools/sundiag.sh osw 2014/03/31_15:00:00-2014/03/31_18:00:00
 Note: Total time range should not exceed 9 hrs. Only the time ranges that
 fall within this limit are considered for the collection of above data
 ilom - User level ILOM data gathering option via ipmitool, in place of
 separately using root login to get ILOM snapshot over the network.
 snapshot - Collects node ILOM snapshot- requires host root password for ILOM
 to send snapshot data over the network.
[root@v1ex1dbadm01 ~]#

Then just upload the bzip2 file to your SR on MOS.

I tend to run this as part of my SR creation and upload to save time.

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Categories: DBA Blogs