Exadata Grid Infrastructure One-off Patch Application

There are times when you encounter issues related to Database/ASM/Clusterware and Oracle recommends you to apply one-patches to fix the issue. These one-off patches can be applicable to Grid and Oracle Home or in some cases just specific to a particular home. In my cases we were adding Exadata X7-2 Compute nodes and Storage Cells to an Existing Exadata X6-2 Rack, and the prerequisite to do that was to apply the one-off (27965497) to GI home before upgrading the Exadata Cluster.

In this article we will demonstrate how to apply a one-off patch to Grid Infrastructure home on Exadata Database Machine. Here my GI version is 11.2.0.4.180116

Steps to apply the one-off patch to GI home

Note: Read the readme.txt or readme.html file carefully as the steps may change for your environment.

• Get the Database Status and role

dm01db01-orcldb1 {/home/oracle}: sqlplus / as sysdba

SQL> select name, open_mode,database_role from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ORCLDB   MOUNTED              PHYSICAL STANDBY
ORCLDB   MOUNTED              PHYSICAL STANDBY
ORCLDB   MOUNTED              PHYSICAL STANDBY

• Set Grid Infrastructure environmental variable

dm01db01-orcldb1 {/home/oracle}: export ORACLE_SID=+ASM1

dm01db01-orcldb1 {/home/oracle}: export ORACLE_HOME=/u01/app/11.2.0.4/grid

dm01db01-+ASM1 {/home/oracle}:echo $ORACLE_HOME
/u01/app/11.2.0.4/grid

dm01db01-+ASM1 {/home/oracle}: export PATH=$PATH:/u01/app/11.2.0.4/grid/OPatch

• List the current GI patches

dm01db01-+ASM1 {/home/oracle}:opatch lspatches
26925255;DATABASE PATCH FOR EXADATA (Jan 2018 - 11.2.0.4.180116) : (26925255)
26609929;OCW Patch Set Update : 11.2.0.4.170814 (26609929)
23727132;
22502505;ACFS Patch Set Update : 11.2.0.4.160419 (22502505)

OPatch succeeded.

• Download, copy and unzip the one-off patch to a staging directory on Exadata Compute node 1.

[root@dm01db01 GI]# ls -ltr
total 442668
-rw-rw-r-- 1 oracle oinstall       267 May  8 02:21 bundle.xml
-rw-rw-r-- 1 oracle oinstall     91939 May 14 15:15 README.html
-rw-rw-r-- 1 oracle oinstall     50971 May 14 15:15 README.txt
-rw-r--r-- 1 oracle oinstall 453130872 May 15 12:02 p27965497_11204160419forACFS_Linux-x86-64.zip
drwxr-xr-x 5 oracle oinstall      4096 May 17 09:40 27965497

• Login as root user and Navigate to one-off patch directory

[root@dm01db01 ~]# cd /u01/GI/27965497

• Set the PATH to include opatch utility from GI home

 [root@dm01db01 27965497]# export PATH=$PATH:/u01/app/11.2.0.4/grid/OPatch

• Verify opatch utility location

[root@dm01db01 27965497]# which opatch
/u01/app/11.2.0.4/grid/OPatch/opatch

• Verify opatch version. Here the minimum verion required is 11.2.0.3.6

[root@dm01db01 27965497]# opatch version
OPatch Version: 11.2.0.3.18

OPatch succeeded.

• Make sure that the ASM and Database is up and running

[root@dm01db01 27965497]# ps -ef|grep smon
root     277568 254212  0 09:39 pts/1    00:00:00 grep smon
root     322798      1  3 May02 ?        13:08:10 /u01/app/11.2.0.4/grid/bin/osysmond.bin
oracle   326697      1  0 May02 ?        00:00:23 asm_smon_+ASM1
oracle   328622      1  0 May02 ?        00:00:15 ora_smon_orcldb1

• Create the OCM file as shown below

[root@dm01db01 27965497]# /u01/app/11.2.0.4/grid/OPatch/ocm/bin/emocmrsp
OCM Installation Response Generator 10.3.7.0.0 - Production
Copyright (c) 2005, 2012, Oracle and/or its affiliates.  All rights reserved.

Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
The OCM configuration response file (ocm.rsp) was successfully created.
[root@dm01db01 27965497]#

[root@dm01db01 27965497]# ls -ltr
total 16
drwxr-xr-x 4 oracle oinstall 4096 May  8 02:18 etc
drwxr-xr-x 3 oracle oinstall 4096 May  8 02:18 custom
drwxr-xr-x 6 oracle oinstall 4096 May  8 02:18 files
-rw-r--r-- 1 root   root      621 May 17 09:40 ocm.rsp

• Apply the one-off patch to GI home as show below

[root@dm01db01 cfgtoollogs]# /u01/app/11.2.0.4/grid/OPatch/opatch auto /u01/patches/GI -oh /u01/app/11.2.0.4/grid -ocmrf /u01/patches/GI/27965497/ocm.rsp
Executing /u01/app/11.2.0.4/grid/perl/bin/perl /u01/app/11.2.0.4/grid/OPatch/crs/patch11203.pl -patchdir /u01/patches -patchn GI -oh /u01/app/11.2.0.4/grid -ocmrf /u01/patches/GI/27965497/ocm.rsp -paramfile /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/11.2.0.4/grid/cfgtoollogs/opatchauto2018-05-17_10-28-13.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/11.2.0.4/grid/cfgtoollogs/opatchauto2018-05-17_10-28-13.report.log

2018-05-17 10:28:13: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

Stopping CRS...
Stopped CRS successfully

patch /u01/patches/GI/27965497  apply successful for home  /u01/app/11.2.0.4/grid

Starting CRS...
Installing Trace File Analyzer
CRS-4123: Oracle High Availability Services has been started.

opatch auto succeeded.

• Login as GI software owner and verify that the one-off patch is applied successfully

[root@dm01db01 cfgtoollogs]# su - oracle

dm01db01-+ASM1 {/home/oracle}:export PATH=$PATH:/u01/app/11.2.0.4/grid/OPatch

dm01db01-+ASM1 {/home/oracle}:opatch lspatches
27965497;ACFS Interim patch for 27965497
26925255;DATABASE PATCH FOR EXADATA (Jan 2018 - 11.2.0.4.180116) : (26925255)
26609929;OCW Patch Set Update : 11.2.0.4.170814 (26609929)
23727132;

OPatch succeeded.

• Verify GI and Database status

dm01db01-+ASM1 {/home/oracle}:ps -ef|grep pmon
oracle   171755      1  0 10:40 ?        00:00:00 asm_pmon_+ASM1
oracle   173484      1  0 10:41 ?        00:00:00 ora_pmon_orcldb1
oracle   178926 176700  0 10:43 pts/0    00:00:00 grep pmon

dm01db01-orcldb1 {/home/oracle}:/u01/app/11.2.0.4/grid/bin/crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
dm01db01-orcldb1 {/home/oracle}:

SQL> select name, open_mode,database_role from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ORCLDB   MOUNTED              PHYSICAL STANDBY
ORCLDB   MOUNTED              PHYSICAL STANDBY
ORCLDB   MOUNTED              PHYSICAL STANDBY

***Repeat the above steps on all other Compute nodes in the Cluster***

Start the Media Recovery process if it is not started automatically.

SQL> alter database recover managed standby database using currently logfile disconnect;

Conclusion

In this article we have learned how to apply a one-off patch to Grid Infrastructure home on Exadata Database Machine.

Oracle Exadata Database Machine Health Check - Exachk 18c

Oracle has released Exachk utility 18c on May 18th, 2018. Let's quickly check if there are differences in Exachk 18c or it is similar to Exachk 12c.

Download latest Exachk 18c utility from MOS note:
Oracle Exadata Database Machine exachk or HealthCheck (Doc ID 1070954.1)

Changes in Exachk 18.2 can be found at:
https://docs.oracle.com/cd/E96145_01/OEXUG/changes-in-this-release-18-2-0.htm#OEXUG-GUID-88FCFBC6-C647-47D3-898C-F4C712117B8B

Steps to Execute Exachk 18c on Exadata Database Machine


Download the latest Exachk from MOS note. Here I am downloading Exachk 18c.

Download Completed

Using WinSCP copy the exachk.zip file to Exadata Compute node

Copy completed. List the Exachk file on Compute node

Unzip the Exachk zip file

Verify Exachk version

Execute Exachk Health by running the following command

Exachk execution completed

Review the Exachk report and take necessary action

Conclusion
In this article we have learned how to execute Oracle Exadata Database Machine health Check using Exachk 18c. Using Exachk 18c is NO different than it's previous releases.

Exadata Setup SSH Equivalence

You want to execute Operating System or Exadata commands on multiple Exadata Compute nodes and Storage Cell in parallel. To accomplish this you must setup passwordless SSH across compute nodes and storage cells.

If SSH equivalence is NOT setup and you execute the dcli command you will see the follow messages. This mean the SSH equivalence is not configured.

[root@dm01db01 ~]# dcli -g dbs_group -l root 'uptime'
The authenticity of host 'dm01db03 (10.10.10.195)' can't be established.
RSA key fingerprint is 40:81:3c:6d:ef:e7:1f:d7:a0:df:eb:f5:ea:92:a5:db.
Are you sure you want to continue connecting (yes/no)? The authenticity of host 'dm01db05 (10.10.10.197)' can't be established.
RSA key fingerprint is 1b:95:47:0b:92:b4:13:9f:55:b7:a3:2a:56:27:9f:1c.
Are you sure you want to continue connecting (yes/no)? The authenticity of host 'dm01db02 (10.10.10.194)' can't be established.
RSA key fingerprint is e1:0d:90:46:16:88:74:01:02:5a:11:90:63:b1:6b:1c.
Are you sure you want to continue connecting (yes/no)? The authenticity of host 'dm01db01 (10.10.10.193)' can't be established.
RSA key fingerprint is 2b:6f:43:4b:86:29:bb:ed:a6:03:c5:34:75:cf:45:34.
Are you sure you want to continue connecting (yes/no)? The authenticity of host 'dm01db04 (10.10.10.196)' can't be established.
RSA key fingerprint is 44:a7:ad:65:c3:1c:fb:0b:0b:28:2c:b6:a5:f3:59:99.
Are you sure you want to continue connecting (yes/no)? The authenticity of host 'dm01db07 (10.10.10.199)' can't be established.
RSA key fingerprint is 25:5f:9a:e6:a4:7a:13:ba:e2:e7:7d:2e:79:53:49:2b.
Are you sure you want to continue connecting (yes/no)? root@dm01db06's password: root@dm01db08's password:

In this article we will demonstrate how to setup SSH equivalence on Exadata Database Machine.


Steps to Setup SSH Equivalence

1. Create the following files if doesn't exist

[root@dm01db08 ~]# cat dbs_group
dm01db01
dm01db02
dm01db03
dm01db04
dm01db05
dm01db06
dm01db07
dm01db08

[root@dm01db08 ~]# cat cell_group
dm01cel01
dm01cel02
dm01cel03
dm01cel04
dm01cel05
dm01cel06
dm01cel07

[root@dm01db08 ~]# cat all_group
dm01db01
dm01db02
dm01db03
dm01db04
dm01db05
dm01db06
dm01db07
dm01db08
dm01cel01
dm01cel02
dm01cel03
dm01cel04
dm01cel05
dm01cel06
dm01cel07
dm01sw-iba01
dm01sw-ibb01

2. Navigate to Support directory on Compute node 1 as shown below

[root@dm01db01 ~]# cd /opt/oracle.SupportTools/

3. Oracle has provided a script *setup_ssh_eq.sh* to configure SSH equivalence across Exadata components. Execute the script as shown below. Here we are setting the SSH equivalence for root user

[root@dm01db01 oracle.SupportTools]# ./setup_ssh_eq.sh ~/all_group root welcome1

/root/.ssh/id_dsa already exists.

Overwrite (y/n)?

/root/.ssh/id_rsa already exists.

Overwrite (y/n)?

spawn dcli -c dm01db01 -l root -k

dm01db01: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01db02 -l root -k

dm01db02: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01db03 -l root -k

dm01db03: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01db04 -l root -k

dm01db04: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01db05 -l root -k

dm01db05: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01db06 -l root -k

dm01db06: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01db07 -l root -k

dm01db07: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01db08 -l root -k

dm01db08: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01cel01 -l root -k

dm01cel01: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01cel02 -l root -k

dm01cel02: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01cel03 -l root -k

dm01cel03: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01cel04 -l root -k

dm01cel04: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01cel05 -l root -k

dm01cel05: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01cel06 -l root -k

dm01cel06: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01cel07 -l root -k

dm01cel07: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01sw-iba01 -l root -k

dm01sw-iba01: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

spawn dcli -c dm01sw-ibb01 -l root -k

dm01sw-ibb01: ssh key already exists

expect: spawn id exp4 not open

    while executing

"expect "*?assword:*""

4. Verify SSH equivalence is working fine

[root@dm01db08 ~]# dcli -g ~/all_group -l root 'uptime'

dm01db01: 09:16:41 up 21 days, 15:47,  1 user,  load average: 1.80, 3.02, 3.35

dm01db02: 09:16:41 up 21 days, 15:38,  0 users,  load average: 2.93, 2.44, 2.37

dm01db03: 09:16:41 up 21 days, 15:19,  0 users,  load average: 2.16, 2.27, 2.77

dm01db04: 09:16:41 up 21 days, 15:12,  0 users,  load average: 4.07, 4.33, 4.14

dm01db05: 09:16:41 up 21 days, 15:09,  0 users,  load average: 2.45, 2.82, 2.75

dm01db06: 09:16:41 up 21 days, 15:06,  0 users,  load average: 1.70, 2.04, 2.60

dm01db07: 09:16:41 up 21 days, 15:02,  0 users,  load average: 6.39, 4.46, 4.20

dm01db08: 09:16:41 up 21 days, 14:59,  1 user,  load average: 1.66, 1.81, 1.97

dm01cel01: 09:16:41 up 203 days, 19:00,  0 users,  load average: 1.40, 1.97, 2.21

dm01cel02: 09:16:41 up 203 days, 18:59,  0 users,  load average: 1.52, 2.08, 2.38

dm01cel03: 09:16:41 up 203 days, 18:59,  0 users,  load average: 1.00, 1.71, 2.02

dm01cel04: 09:16:41 up 203 days, 18:59,  0 users,  load average: 1.08, 1.59, 1.92

dm01cel05: 09:16:41 up 203 days, 18:59,  0 users,  load average: 1.24, 1.53, 1.82

dm01cel06: 09:16:41 up 203 days, 18:59,  0 users,  load average: 1.09, 1.60, 1.96

dm01cel07: 09:16:41 up 203 days, 19:00,  0 users,  load average: 1.01, 1.37, 1.60

dm01sw-iba01: 09:16:42 up 539 days,  6:21,  0 users,  load average: 0.79, 0.99, 1.07

dm01sw-ibb01: 14:49:54 up 539 days,  9:43,  0 users,  load average: 1.26, 1.44, 1.41

[root@dm01db08 ~]# dcli -g dbs_group -l root 'imageinfo | grep "Image version"'

dm01db01: Image version: 12.1.2.3.6.170713

dm01db02: Image version: 12.1.2.3.6.170713

dm01db03: Image version: 12.1.2.3.6.170713

dm01db04: Image version: 12.1.2.3.6.170713

dm01db05: Image version: 12.1.2.3.6.170713

dm01db06: Image version: 12.1.2.3.6.170713

dm01db07: Image version: 12.1.2.3.6.170713

dm01db08: Image version: 12.1.2.3.6.170713

[root@dm01db08 ~]# dcli -g cell_group -l root 'imageinfo | grep "Active image version"'

dm01cel01: Active image version: 12.1.2.3.6.170713

dm01cel02: Active image version: 12.1.2.3.6.170713

dm01cel03: Active image version: 12.1.2.3.6.170713

dm01cel04: Active image version: 12.1.2.3.6.170713

dm01cel05: Active image version: 12.1.2.3.6.170713

dm01cel06: Active image version: 12.1.2.3.6.170713

dm01cel07: Active image version: 12.1.2.3.6.170713

[root@dm01db08 ~]# ssh dm01sw-iba01 version

SUN DCS 36p version: 2.1.8-1

Build time: Sep 18 2015 10:26:47

SP board info:

Manufacturing Date: 2015.05.13

Serial Number: "NCDKO0980"

Hardware Revision: 0x0200

Firmware Revision: 0x0000

BIOS version: SUN0R100

BIOS date: 06/22/2010

Conclusion

In this article we have learned how to configure SSH equivalence on Exadata Database Machine. Using the setup_ssh_eq.sh script is very easy setup SSH equivalence.

Oracle GoldenGate Pocket Reference Guide

Oracle GoldenGate Pocket Reference

Oracle GoldenGate Pocket Reference

Oracle Data Guard Resolve UNNAMED File Issue

In a Data Guard configuration it is very common to these the 'UNNAMED File Issue/Error' on Standby Database when you add new datafile on Primary Database and there is no space available on the standby database server or improper parameter settings related to standby file management. This will result in the following Oracle error messages ORA-01111, ORA-01110, ORA-01157 and cause MRP process to crash resulting in Standby database 'out of sync' with primary. 

Also note that, If STANDBY_FILE_MANAGEMENT parameter is not configured as 'AUTO' in your Data Guard environment and a datafile is created on Primary database,  then the file created on standby server will have unnamed file name and it is created under $ORACLE_HOME/dbs directory. 

Environment Details:

Primary DB HostName / DB Instance : PRD101/ ORCLPRD
Standby DB HostName / DB Instance : STD101/ ORCLSTDY
Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Alert log on Standby DB:

Sun May 21 15:05:13 2017
Errors in file /u01/app/oracle/diag/rdbms/orclstdy/orclstdy/trace/orclstdy_dbw0_61177.trc:
ORA-01186: file 11 failed verification tests
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00011'
File 11 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/orclstdy/orclstdy/trace/orclstdy_pr00_11317.trc:
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00011'
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00011'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (orclstdy)

Troubleshooting Steps:

Let us see how we can fix this error and get Standby In-synch again with Primary.

• Run following at Standby side. 

SQL> set lines 300
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ORCLSTDY  READ ONLY            PHYSICAL STANDBY

SQL> select PROCESS,THREAD#,SEQUENCE#,STATUS from v$managed_standby where process='MRP0';

no rows selected  --> MRP is not Running

SQL> set sqlblanklines on

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                  13817                 13708        109

• Stop MRP if it's not already stopped.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL> show parameter standby_file_management

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
standby_file_management              string                           AUTO


SQL> col NAME for a70
SQL>  select * from v$recover_file where error like '%UNNAMED%';

     FILE# NAME
---------- ----------------------------------------------------------------------
        11 /u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00011

Here the datafile #11 is renamed to UNNAMED00011 on standby database 

• Identify the datafile name on primary database

SQL>  select file#,name from v$datafile where file#=11;

FILE#   NAME
—— ———————————————————————-
11  /u02/oradata/datafile/orclprd/users01.dbf

• Temporary change setting for STANDBY_FILE_MANAGEMENT to MANUAL, we can revert this setting once we finish the activity. 

SQL> alter system set standby_file_management=MANUAL scope=both;

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
standby_file_management              string 

• Create a new datafile with same name as of Primary ( Name obtained above)

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00011' as '/u02/oradata/datafile/orclstdy/users01.dbf';

Database altered.

SQL> select file#,name from v$datafile where file#=11;

     FILE# NAME
---------- ----------------------------------------------------------------------
        11 /u02/oradata/datafile/orclstdy/users01.dbf

• Revert STANDBY_FILE_MANAGEMENT to AUTO 

  SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

• Check if MRP is up 

SQL> select PROCESS,THREAD#,SEQUENCE#,STATUS from v$managed_standby where process='MRP0';

no rows selected

• Start MRP process and monitor lag gap. MRP will start applying logs if archives are available at Standby side.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

• On Primary database switch log or archive log and check the archive logs are being shipped to Standby database and MRP is applying them

SQL> alter system switch logfile;

• Verify standby database 

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                  13818                 13713        105


SQL> /

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  13820                 13820          0

You can observe, Standby Database is in synch with Primary and MRP is working perfect. 

Conclusion

In this article we have learned how to fix 'UNNAMED File Issue/Error' on Standby Database when you add new datafile on Primary Database and there is no space available on the standby database server or improper parameter settings related to standby file management. To avoid these kind of errors, also ensure that you have correct Data Guard setup as per best practices and there is sufficient file system space on both Primary and Standby servers

Create a Local Yum Repository Using an ISO Image on Exadata Database Machine

You want to install some packages on the Exadata Compute nodes running Oracle Enterprise Linux and you don't have internet access. In those cases you can create a local YUM repository using DVD Image. You simply download the DVD Image on the Desktop/Laptop where you have the internet access, copy it to the server and mount it.

In this article we will demonstrate how create a Local YUM Repository using ISO Image on Exadata Database Machines.

Prerequisites

1. System with Internet
2. Sufficient space on the server for Oracle Linux DVD ISO

Steps to create a Local YUM Repository using ISO Image on Exadata Database Machines

Step 1: Download Oracle Linux DVD Image from the Oracle Software Delivery Cloud at http://edelivery.oracle.com/linux

Here I am downloading:
V860937-01.iso Oracle Linux 6 Update 9 for x86 64 bit, 3.8 GB

Step 2: Copy the DVD image to the server to a staging directory using Winscp

Create a staging directory on the server

[root@dm01db01 ~]# mkdir -p /u01/app/oracle/software/ISO

Use Winscp to copy the ISO Image to the server

[root@dm01db01 ~]# ls -l /u01/app/oracle/software/ISO
total 3953672
-rw-r--r-- 1 root root 4048551936 Mar 20 04:18 V860937-01.iso

Step 3:  Create a mount point, for example /mnt/OEL6.9, and mount the DVD image on it. 

[root@dm01db01 ~]# mkdir -p /mnt/OEL6.9

[root@dm01db01 ~]# ls -l /mnt/OEL6.9
total 0

[root@dm01db01 ~]# mount -o loop,ro /u01/app/oracle/software/ISO/V860937-01.iso /mnt/OEL6.9

[root@dm01db01 ~]# df -h /mnt/OEL6.9
Filesystem            Size  Used Avail Use% Mounted on
/u01/app/oracle/software/ISO/V860937-01.iso
                      3.8G  3.8G     0 100% /mnt/OEL6.9

[root@dm01db01 ~]# cd /mnt/OEL6.9

[root@dm01db01 OEL6.9]# ls -l
total 1686
drwxr-xr-x 3 root root   2048 Mar 24  2017 EFI
-rw-r--r-- 1 root root   8529 Mar 24  2017 EULA
-rw-r--r-- 1 root root   8529 Mar 24  2017 eula.en_US
-rw-r--r-- 1 root root   3334 Mar 24  2017 eula.py
-rw-r--r-- 1 root root  18390 Mar 24  2017 GPL
drwxr-xr-x 3 root root   2048 Mar 24  2017 HighAvailability
drwxr-xr-x 3 root root   2048 Mar 24  2017 images
drwxr-xr-x 2 root root   2048 Mar 24  2017 isolinux
drwxr-xr-x 3 root root   2048 Mar 24  2017 LoadBalancer
-rw-r--r-- 1 root root     98 Mar 24  2017 media.repo
drwxr-xr-x 2 root root 716800 Mar 24  2017 Packages
-rw-r--r-- 1 root root   7193 Mar 24  2017 README-en
-rw-r--r-- 1 root root   6016 Mar 24  2017 README-en.html
-rw-r--r-- 1 root root  81004 Mar 24  2017 RELEASE-NOTES-en
-rw-r--r-- 1 root root 227548 Mar 24  2017 RELEASE-NOTES-en.html
-rw-r--r-- 1 root root  81004 Mar 24  2017 RELEASE-NOTES-x86_64-en
-rw-r--r-- 1 root root 227548 Mar 24  2017 RELEASE-NOTES-x86_64-en.html
-rw-r--r-- 1 root root  81004 Mar 24  2017 RELEASE-NOTES-x86-en
-rw-r--r-- 1 root root 227548 Mar 24  2017 RELEASE-NOTES-x86-en.html
lrwxrwxrwx 1 root root     15 Mar 24  2017 repodata -> Server/repodata
drwxr-xr-x 3 root root   2048 Mar 24  2017 ResilientStorage
-rw-r--r-- 1 root root   1011 Mar 24  2017 RPM-GPG-KEY
-rw-r--r-- 1 root root   1011 Mar 24  2017 RPM-GPG-KEY-oracle
drwxr-xr-x 3 root root   2048 Mar 24  2017 ScalableFileSystem
drwxr-xr-x 4 root root   2048 Mar 24  2017 Server
-rw-r--r-- 1 root root    108 Mar 24  2017 supportinfo
-r--r--r-- 1 root root   6517 Mar 24  2017 TRANS.TBL
drwxr-xr-x 3 root root   2048 Mar 24  2017 UEK4

Step 4:  Add an entry to the /etc/fstab file, this will to auto mount the DVD image after a reboot. 

/u01/app/oracle/software/ISO/V860937-01.iso /mnt/OEL6.9 iso9660 loop,ro 0 0

[root@dm01db01 ~]# vi /etc/fstab

[root@dm01db01 ~]# cat /etc/fstab
LABEL=DBSYS             /                       ext4    defaults        1 1
LABEL=BOOT              /boot                   ext4    defaults,nodev        1 1
LABEL=DBORA             /u01                    ext4    defaults,nodev  1 1
tmpfs                   /dev/shm                tmpfs   defaults,size=258341m 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP              swap                    swap    defaults        0 0
/u01/app/oracle/software/ISO/V860937-01.iso /mnt/OEL6.9 iso9660 loop,ro 0 0

Step 5:  Navigate to /etc/yum.repos.d directory, you can edit the existing repository files public-yum-ol6.repo or create a new repo file such as ULN-base.repo, and disable all entries by setting enabled=0. 

[root@dm01db01 ~]# cd /etc/yum.repos.d

[root@dm01db01 yum.repos.d]# ls -l
total 20
-rw-r----- 1 root root  291 Mar 31 22:43 Exadata-computenode.repo
-r--r----- 1 root root  896 Jan 26 06:19 Exadata-computenode.repo.sample
-rw-r--r-- 1 root root 7299 Mar 20 05:57 public-yum-ol6.repo

Here I am going to create the OL69.repo repository file and add the entries 

[root@dm01db01 yum.repos.d]# vi OL69.repo

[root@dm01db01 yum.repos.d]# cat OL69.repo
[OL69]
name=Oracle Linux 6.9 x86_64
baseurl=file:///mnt/OEL6.9
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY
gpgcheck=1
enabled=1

Step 7:  Perform YUM Cache cleanup as below

[root@dm01db01 yum.repos.d]# yum clean all
Cleaning repos: OL69
Cleaning up Everything

Step 8:  Perform a test to ensure you can access yum repository

[root@dm01db01 yum.repos.d]# yum repolist
OL69                          | 3.7 kB      00:00 ...
OL69/primary_db                | 3.1 MB      00:00 ...
repo id                        repo name    status    
OL69                          Oracle Linux 6.9 x86_64 3,860
repolist: 3,860


Conclusion

In this article we have learned how to create local YUM repository using DVD ISO Image on Exadata Database Machine. Configuring a local YUM repository comes handy when you want to install packages on the server and you don't have internet access from the server.