Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 1 week 5 days ago

That’s How We’ve Always Done It Before

Sat, 2017-11-11 05:13

Thanksgiving is coming up and to this day, I still remembered anecdote from former manager.

Daughter is helping mother prepare for Thanksgiving dinner which they do together for many years.

This year, daughter decided to ask, “Why do we cut off the turkey legs and wings before putting it the oven?”

Mom responded, “That’s how we’ve always done it before. Let’s call grandma and find out.”

On the phone with grandma and grandma’s response was, “I can’t believe you’re still doing that! I cut off the legs and wings because my oven was too small.”

Happy Thanksgiving.

 

 


GoldenGate Naming Convention P03

Sun, 2017-11-05 10:44

GoldenGate Naming Convention P01
GoldenGate Naming Convention P02

Here I provide an example for how I would implement 3-way replication.
I used capitalization for some clarity in trail – not tested yet.

N-way Replication calculations:
Primary Extract for each silo: 1
Pump Extract for each silo: N-1 
Replicats for each silo: N-1 
Total proceses for each silo: 2N-1
Sequence start value: (1001-100N), increment by 100

++++++++++

3-way Replication:
Primary Extract for each silo: 1
Pump Extract for each silo: 3-1=2
Replicats for each silo: 3-1=2
Total proceses for each silo: 2*3-1=5
Sequence start value: (1001,1002,1003) increment by 100

++++++++++

(Silo 1 - NYPRD) E_NY(aa) | pump (aB):(aC) | replicate (bA):(cA) | 1001+100 (sequence)
(Silo 2 - DCPRD) E_DC(bb) | pump (bA):(bC) | replicate (aB):(cB) | 1002+100 (sequence)
(Silo 3 - STDBY) E_SB(cc) | pump (cA):(cB) | replicAte (aC)|(bC) | 1003+100 (sequence)

++++++++++

E_NY1 (aa)

-- Pump include all other silos except current silo
P_NY2 (aB)
P_NY3 (aC)  

-- ReplicAt include all other silos except current silo
R_DC2 (bA)
R_SB3 (cA)

++++++++++

E_DC2 (bb)

P_DC1 (bA)
P_DC3 (bC)

R_NY1 (aB)
R_SB3 (cB)

++++++++++

E_SB3 (cc)

P_SB1 (cA)
P_SB2 (cB)

R_DC2 (bC)
R_NY1 (aC)

++++++++++

Stop replication from NYPRD:
(Silo 1) stop *NY* (stops E_NY1/P_NY2/P_NY3)

-- This may be optional depending on requirements.
-- If nothing is extracted, then nothing is replicated.
(Silo 2) stop *NY* (stops R_NY1) 
(Silo 3) stop *NY* (stops R_NY1)

Relocate Services Back To Instance Before Patching

Sun, 2017-11-05 07:16

This will only work for 2-nodes RAC!

Prerequisite:
Patching starts at instance1, services failover to instance2.
Patching completed at instance1, restart instance1.
Patching starts at instance2, services failover to instance1.
Patching completed at instance2, restart instance2.
All services are now running at instance1.
Relocate services from instance2 back to where it belongs.

Save existing service configuration before patching.
[oracle@racnode-dc1-2 rac_relocate]$ ./save_service.sh

 

+ srvctl status database -d orclcdb -v
+ srvctl status database -d orclcdb -v
+ awk '-F ' '{print $2}'
+ cat /tmp/services.conf
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2 with online services testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
+ cat /tmp/instance.conf
orclcdb1
orclcdb2
++ tail -1 /tmp/services.conf
++ awk '-F ' '{print $11}'
++ awk '{$0=substr($0,1,length($0)-1); print $0}'
+ svc=testsvc26,testsvc27,testsvc28,testsvc29
+ exit
[oracle@racnode-dc1-2 rac_relocate]$

 

Patching completed at instance1 and starting at instance2.
All services are running on instance1 after failover of instance2.

 

[oracle@racnode-dc1-2 rac_relocate]$ srvctl stop instance -db orclcdb -instance orclcdb2 -failover
[oracle@racnode-dc1-2 rac_relocate]$ srvctl status database -d orclcdb -v
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15,testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
Instance orclcdb2 is not running on node racnode-dc1-2
[oracle@racnode-dc1-2 rac_relocate]$

 

Patching completed at instance2, start instance2, all services running from instance1.

[oracle@racnode-dc1-2 rac_relocate]$ srvctl start instance -db orclcdb -instance orclcdb2
[oracle@racnode-dc1-2 rac_relocate]$ srvctl status database -d orclcdb -v
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15,testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2. Instance status: Open.
[oracle@racnode-dc1-2 rac_relocate]$

Verify relocate services will work as intended by testing first – print but not execute commands.

[oracle@racnode-dc1-2 rac_relocate]$ ./test_relocate.sh
================================================================================
++++++ Saved Configuration
-rw-r--r-- 1 oracle oinstall  18 Nov  5 13:01 /tmp/instance.conf
-rw-r--r-- 1 oracle oinstall 291 Nov  5 13:01 /tmp/services.conf
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2 with online services testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
orclcdb1
orclcdb2
================================================================================
++++++ Relocate Configuration
newinst=orclcdb2
oldinst=orclcdb1
svc=testsvc26,testsvc27,testsvc28,testsvc29
================================================================================
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15,testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2. Instance status: Open.
srvctl relocate service -db orclcdb -service testsvc26 -oldinst orclcdb1 -newinst orclcdb2
srvctl relocate service -db orclcdb -service testsvc27 -oldinst orclcdb1 -newinst orclcdb2
srvctl relocate service -db orclcdb -service testsvc28 -oldinst orclcdb1 -newinst orclcdb2
srvctl relocate service -db orclcdb -service testsvc29 -oldinst orclcdb1 -newinst orclcdb2
[oracle@racnode-dc1-2 rac_relocate]$

Relocate services to orginal saved configuration.

[oracle@racnode-dc1-2 rac_relocate]$ ./relocate_service.sh
================================================================================
++++++ Saved Configuration
-rw-r--r-- 1 oracle oinstall  18 Nov  5 13:01 /tmp/instance.conf
-rw-r--r-- 1 oracle oinstall 291 Nov  5 13:01 /tmp/services.conf
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2 with online services testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
orclcdb1
orclcdb2
================================================================================
++++++ Relocate Configuration
newinst=orclcdb2
oldinst=orclcdb1
svc=testsvc26,testsvc27,testsvc28,testsvc29
================================================================================
+ srvctl status database -d orclcdb -v
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15,testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2. Instance status: Open.
+ IFS=,
+ for s in '${svc}'
+ srvctl relocate service -db orclcdb -service testsvc26 -oldinst orclcdb1 -newinst orclcdb2
+ for s in '${svc}'
+ srvctl relocate service -db orclcdb -service testsvc27 -oldinst orclcdb1 -newinst orclcdb2
+ for s in '${svc}'
+ srvctl relocate service -db orclcdb -service testsvc28 -oldinst orclcdb1 -newinst orclcdb2
+ for s in '${svc}'
+ srvctl relocate service -db orclcdb -service testsvc29 -oldinst orclcdb1 -newinst orclcdb2
+ srvctl status database -d orclcdb -v
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2 with online services testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
+ exit
[oracle@racnode-dc1-2 rac_relocate]$

I have rant about hardcoding before.
YES! I hardcoded conf file location to provide a permanent and consistent location for all environments.

I don’t like to have to dig through code for find such information.
ex:
SCRIPT_DIR=/u01/app/oracle/scripts
LOG_DIR=$SCRIPT_DIR/log

save_service.sh


#!/bin/sh -x
srvctl status database -d ${db} -v > /tmp/services.conf
srvctl status database -d ${db} -v|awk -F" " '{print $2}' > /tmp/instance.conf
cat /tmp/services.conf
cat /tmp/instance.conf
svc=`tail -1 /tmp/services.conf | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
exit
IFS=","
srvctl status database -d ${db} -v
for s in ${svc}
do
srvctl relocate service -db ${db} -service ${s} -oldinst ${oldinst} -newinst ${newinst}
done
srvctl status database -d ${db} -v
exit

 

test_relocate.sh


#!/bin/sh
echo "================================================================================"
echo "++++++ Saved Configuration"
ls -l /tmp/*.conf
cat /tmp/services.conf
cat /tmp/instance.conf
echo "================================================================================"
echo "++++++ Relocate Configuration"
export svc=`tail -1 /tmp/services.conf | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
export oldinst=`head -1 /tmp/instance.conf`
export newinst=`tail -1 /tmp/instance.conf`
env|egrep 'svc|inst'|sort
echo "================================================================================"
srvctl status database -d ${db} -v
IFS=","
for s in ${svc}
do
echo "srvctl relocate service -db ${db} -service ${s} -oldinst ${oldinst} -newinst ${newinst}"
done
exit

 

relocate_service.sh


#!/bin/sh
echo "================================================================================"
echo "++++++ Saved Configuration"
ls -l /tmp/*.conf
cat /tmp/services.conf
cat /tmp/instance.conf
echo "================================================================================"
echo "++++++ Relocate Configuration"
export svc=`tail -1 /tmp/services.conf | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
export oldinst=`head -1 /tmp/instance.conf`
export newinst=`tail -1 /tmp/instance.conf`
env|egrep 'svc|inst'|sort
echo "================================================================================"
set -x
srvctl status database -d ${db} -v
IFS=","
for s in ${svc}
do
srvctl relocate service -db ${db} -service ${s} -oldinst ${oldinst} -newinst ${newinst}
done
srvctl status database -d ${db} -v
exit

 


12.1 Improved Service Failover

Sun, 2017-11-05 06:51

11gR2 Database Services and Instance Shutdown

The thought of having to manually relocated dozens of services was not very appealing.

As it turns out, there is no need to manually relocate services.

srvctl stop instance -db orclcdb -instance orclcdb1 -failover will do the trick.

Comparing the 2 commands, 12c is a lot clearer / cleaner.

12c:
srvctl add service -db orclcdb -service DBA_TEST -preferred orclcdb1 -available orclcdb2 -failovertype SELECT -tafpolicy BASIC

11g:
srvctl add service -d orclcdb -s DBA_TEST -P BASIC -e SELECT -r orclcdb1 -a orclcdb2

DEMO:

$ srvctl config service -d orclcdb -s DBA_TEST|egrep -i ‘Service name|Preferred instances|Available instances|failover’

Service name: DBA_TEST
Failover type: SELECT
Failover method:
TAF failover retries:
TAF failover delay:
Preferred instances: orclcdb1
Available instances: orclcdb2

$ srvctl status database -d orclcdb

Instance orclcdb1 is running on node racnode-dc1-1
Instance orclcdb2 is running on node racnode-dc1-2

$ sqlplus mdinh/mdinh@dbatest @t.sql

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 5 04:17:56 2017

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

Last Successful login time: Sun Nov 05 2017 04:15:29 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options


   INST_ID STARTUP_TIME
---------- -----------------------------
         1 05-NOV-2017 04:12:55
         2 05-NOV-2017 04:14:49


   INST_ID FAILOVER_TYPE FAILOVER_M FAI
---------- ------------- ---------- ---
         1 NONE          NONE       NO
         1 SELECT        BASIC      NO
         2 NONE          NONE       NO

04:17:57 MDINH @ dbatest:>host
[oracle@racnode-dc1-1 ~]$ srvctl stop instance -db orclcdb -instance orclcdb1 -failover;date
Sun Nov 5 04:18:34 CET 2017
[oracle@racnode-dc1-1 ~]$ exit
exit

04:18:37 MDINH @ dbatest:>@t.sql

   INST_ID STARTUP_TIME
---------- -----------------------------
         2 05-NOV-2017 04:14:49


   INST_ID FAILOVER_TYPE FAILOVER_M FAI
---------- ------------- ---------- ---
         2 SELECT        BASIC      YES

04:18:40 MDINH @ dbatest:>

GoldenGate Naming Convention P02

Sat, 2017-11-04 08:05

GoldenGate Naming Convention P01

Bidirectional replication:

E_10G (write to aa) | P_10G (read from aa, write to ab) | R_10G (read from ab, write to 12C DB)
E_12C (write to bb) | P_12C (read from bb, write to ba) | R_12C (read from ba, write to 10G DB)

Source (10G DB)     | Target (12C DB)
-------------------------------------
E_10G [>aa]         | E_12C [>bb]
P_10G [>ab]         | P_12C [>ba]
R_12C [<ba]         | R_10G [<ab]

Create same process name for extract, pump, replicat.

Using the example above:
stop 10G replication, stop *10G at source and target.
stop 12C replication, stop *12C at source and target.

Another method.

Source (10G DB)     | Target (12C DB)
-------------------------------------
E_10G [>aa]         | E_12C [>bb]
P_10G [>ab]         | P_12C [>ba]
R_10G [<ba]         | R_12C [<ab]

Process name was created based on DB versions.

Using the example above:
stop 10G replication, stop E_10G,P_10G at source and stop R_12C at target.
stop 12C replication, stop E_12C,P_12C at source and stop R_10G at target.

Of the 2 methods, which do you prefer?

Splitting extracts:

Source      | Target
-------------------------------------
E_USR [>aa] | R_JOE [<az]
P_JOE [>az] | R_SUE [<ay]
P_SUE [>ay] | R_AMY [<ax]
P_AMY [>ax] |

How does one know where source is from? You don’t unless you comment the parameters.

Example: RAC environment where VIP is used for PUMP

EXTRACT e_hawk
-- CHECKPARAMS
-- ADD EXTRACT e_hawk, INTEGRATED TRANLOG, BEGIN NOW
-- ADD EXTTRAIL ./dirdat/aa EXTRACT e_hawk, MEGABYTES 500

EXTRACT p_hawk
-- CHECKPARAMS
-- Target: host03/04
-- ADD EXTRACT p_hawk, EXTTRAILSOURCE ./dirdat/aa
-- ADD RMTTRAIL ./dirdat/ab, EXTRACT p_hawk, MEGABYTES 500
RMTHOST OGG_VIP MGRPORT 7801, TCPBUFSIZE 1048576, TCPFLUSHBYTES 1048576 

REPLICAT r_hawk
-- CHECKPARAMS
-- Source: host01/02
-- REGISTER REPLICAT r_hawk DATABASE
-- ADD REPLICAT r_hawk, INTEGRATED, EXTTRAIL ./dirdat/ab

In conclusion, there is really no best practice, but some thought and planning do help.


Goldengate XAG HAS

Fri, 2017-11-03 19:53

If you install GI for SI DB, then you might as well install XAG for OGG.

Imagine if there is a Vagrant to put all of this together?

[oracle@db-asm-1 xag]$ mkdir -p /u01/app/oracle/xag

[oracle@db-asm-1 xag]$ ./xagsetup.sh --install --directory /u01/app/oracle/xag
Installing Oracle Grid Infrastructure Agents on: db-asm-1
Done.

[oracle@db-asm-1 ~]$ cd /u01/app/oracle/xag/bin/

[oracle@db-asm-1 bin]$ ./agctl query releaseversion
The Oracle Grid Infrastructure Agents release version is 8.1.0

[oracle@db-asm-1 bin]$ ./agctl query deployment
The Oracle Grid Infrastructure Agents deployment is standalone

[oracle@db-asm-1 bin]$ ./agctl add goldengate --help
Adds Goldengate instance to Oracle Clusterware.

[oracle@db-asm-1 bin]$ ./agctl add goldengate ogg_amer \
> --instance_type dual --databases ora.amer.db \
> --gg_home /u01/app/oracle/amer/ogg/12.3.0_ora12c \
>  --oracle_home /u01/app/oracle/12.2.0.1/db1

[oracle@db-asm-1 bin]$ ./agctl status goldengate ogg_amer
Goldengate  instance 'ogg_amer' is not running

[oracle@db-asm-1 bin]$ ./agctl start goldengate ogg_amer

[oracle@db-asm-1 bin]$ ./agctl status goldengate ogg_amer
Goldengate  instance 'ogg_amer' is running on db-asm-1

[oracle@db-asm-1 bin]$ ./agctl config goldengate
XAG-212: Instance '' is not yet registered.

[oracle@db-asm-1 bin]$ ./agctl config goldengate ogg_amer
GoldenGate location is: /u01/app/oracle/amer/ogg/12.3.0_ora12c
GoldenGate instance type is: dual
ORACLE_HOME location is: /u01/app/oracle/12.2.0.1/db1
Databases needed: ora.amer.db
EXTRACT groups to monitor:
REPLICAT groups to monitor:
Critical EXTRACT groups:
Critical REPLICAT groups:
Autostart on DataGuard role transition to PRIMARY: no
Autostart JAgent: no
[oracle@db-asm-1 bin]$

oracle@db-asm-1:+ASM:/home/oracle
$ ./crs_stat.sh
The Oracle base remains unchanged with value /u01/app/oracle
NAME                                          TARGET     STATE           SERVER       STATE_DETAILS
-------------------------                     ---------- ----------      ------------ ------------------
                                              Name       Target          State        Server State
ora.CRS.dg                                    ONLINE     ONLINE          db-asm-1     STABLE
ora.DATA.dg                                   ONLINE     ONLINE          db-asm-1     STABLE
ora.FRA.dg                                    ONLINE     ONLINE          db-asm-1     STABLE
ora.LISTENER.lsnr                             ONLINE     ONLINE          db-asm-1     STABLE
ora.asm                                       ONLINE     ONLINE          db-asm-1     Started,STABLE
ora.ons                                       OFFLINE    OFFLINE         db-asm-1     STABLE
ora.amer.db                                   ONLINE     ONLINE          db-asm-1     Open,HOME=/u01/app/o
ora.cssd                                      ONLINE     ONLINE          db-asm-1     STABLE
ora.diskmon                                   OFFLINE    OFFLINE         STABLE
ora.euro.db                                   ONLINE     ONLINE          db-asm-1     Open,HOME=/u01/app/o
ora.evmd                                      ONLINE     ONLINE          db-asm-1     STABLE
xag.ogg_amer.goldengate                       ONLINE     ONLINE          db-asm-1     STABLE

oracle@db-asm-1:+ASM:/home/oracle
$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.CRS.dg' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.euro.db' on 'db-asm-1'
CRS-2673: Attempting to stop 'xag.ogg_amer.goldengate' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'db-asm-1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.euro.db' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.CRS.dg' on 'db-asm-1' succeeded
CRS-2677: Stop of 'xag.ogg_amer.goldengate' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.amer.db' on 'db-asm-1'
CRS-2677: Stop of 'ora.amer.db' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'db-asm-1'
CRS-2677: Stop of 'ora.DATA.dg' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'db-asm-1'
CRS-2677: Stop of 'ora.FRA.dg' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'db-asm-1'
CRS-2677: Stop of 'ora.evmd' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.asm' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'db-asm-1'
CRS-2677: Stop of 'ora.cssd' on 'db-asm-1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'db-asm-1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

GoldenGate Naming Convention P01

Fri, 2017-11-03 17:33

I had a nice discussion with teammates about standards.

It’s wrong if there are no standards or naming conventions; otherwise, let your imagination run wild.

Hence, before you embark, think about it as it will make life much easier.

For prompt: I like to know what ORACLE_SID for environment.

oracle@db-asm-1:amer:/home/oracle
oracle@db-asm-1:euro:/home/oracle

For Goldengate: I did it this way because there are 2 DBs / 2 GGs for the same host.

Why ora12c? There are 2 options when installing Goldengate: ORA11g|ORA12c

/u01/app/oracle/amer/ogg/12.3.0_ora12c
/u01/app/oracle/euro/ogg/12.3.0_ora12c

There was discussion ogg/gg/ggs – doesn’t really matter.

Where should it reside? I had planned for /u02 and /u03 but Vagrant was not being nice to me.

Why different mount? There are GG directories and trails which will fill up.

I like to KISS and avoid soft links.

One thing that does annoy is using $GGHOME.

Why? ORACLE_BASE, ORACLE_HOME, ORACLE_SID, GRID_HOME, etc…

Make life simple, use aliases.

oracle@db-asm-1:+ASM:/home/oracle
$ cat .bash_profile

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
export PATH=$PATH:$HOME/.local/bin:$HOME/bin
. ~/.alias

oracle@db-asm-1:+ASM:/home/oracle
$ cat .alias

alias amer='source ~/.amer'
alias euro='source ~/.euro'
alias ggs='cd $GG_HOME'

oracle@db-asm-1:+ASM:/home/oracle
$ cat .amer

export LD_LIBRARY_PATH=/lib:/usr/lib
export ORACLE_SID=amer
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK
export GG_HOME=/u01/app/oracle/amer/ogg/12.3.0_ora12c
export LD_LIBRARY_PATH=$GG_HOME:$LD_LIBRARY_PATH
export PS1="\u@\h:\${ORACLE_SID}:\${PWD}\n$ "

oracle@db-asm-1:+ASM:/home/oracle
$ amer

The Oracle base remains unchanged with value /u01/app/oracle
oracle@db-asm-1:amer:/home/oracle
$ env|egrep 'ORACLE|HOME'
ORACLE_SID=amer
ORACLE_BASE=/u01/app/oracle
GG_HOME=/u01/app/oracle/amer/ogg/12.3.0_ora12c
PS1=\u@\h:${ORACLE_SID}:${PWD}\n$
HOME=/home/oracle
ORACLE_HOME=/u01/app/oracle/12.2.0.1/db1

oracle@db-asm-1:amer:/home/oracle
$ cat .euro

export LD_LIBRARY_PATH=/lib:/usr/lib
export ORACLE_SID=euro
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK
export GG_HOME=/u01/app/oracle/euro/ogg/12.3.0_ora12c
export LD_LIBRARY_PATH=$GG_HOME:$LD_LIBRARY_PATH
export PS1="\u@\h:\${ORACLE_SID}:\${PWD}\n$ "

oracle@db-asm-1:amer:/home/oracle
$ euro

The Oracle base remains unchanged with value /u01/app/oracle
oracle@db-asm-1:euro:/home/oracle
$ env|egrep 'ORACLE|HOME'
ORACLE_SID=euro
ORACLE_BASE=/u01/app/oracle
GG_HOME=/u01/app/oracle/euro/ogg/12.3.0_ora12c
PS1=\u@\h:${ORACLE_SID}:${PWD}\n$
HOME=/home/oracle
ORACLE_HOME=/u01/app/oracle/12.2.0.1/db1

oracle@db-asm-1:euro:/u01/app/oracle/euro/ogg/12.3.0_ora12c
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (db-asm-1) 1> exit

$ grep ORA oggcore.rsp

# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
#         ORA11g for installing Oracle GoldenGate for Oracle Database 11g

Monitoring Standby – SQLPlus or DGMGRL

Wed, 2017-11-01 22:00

Here is an example using dgmgrl


DGMGRL> show database roverdb SendQEntries
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs) 
           roverstby     ARCHIVED        936921167                1                13019  10/31/2017 10:47:04  10/31/2017 10:48:31     746413367424     746413483999          1819004 
           roverstby     ARCHIVED        936921167                1                13023  10/31/2017 10:51:40  10/31/2017 10:52:19     746413767648     746413883688          1809094 
           roverstby     ARCHIVED        936921167                1                13031  10/31/2017 10:57:02  10/31/2017 10:57:44     746414728981     746414851377          1924909 
           roverstby     ARCHIVED        936921167                1                13032  10/31/2017 10:57:44  10/31/2017 10:58:23     746414851377     746414967877          1815042 
           roverstby     ARCHIVED        936921167                1                13033  10/31/2017 10:58:23  10/31/2017 10:59:02     746414967877     746415089206          1798857 
           roverstby     ARCHIVED        936921167                1                13034  10/31/2017 10:59:02  10/31/2017 10:59:41     746415089206     746415217514          1818919 
                          CURRENT        936921167                1                13036  10/31/2017 11:29:41                          746415239037                               628 
                          CURRENT        936921167                2                12359  10/31/2017 11:29:05                          746415238854                               864 

DGMGRL> show database roverstby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs) 
         NOT_APPLIED        936921167                1                13020  10/31/2017 10:48:31  10/31/2017 10:48:49     746413483999     746413509640           385949 
         NOT_APPLIED        936921167                1                13021  10/31/2017 10:48:49  10/31/2017 10:50:19     746413509640     746413636246          1885417 
         NOT_APPLIED        936921167                1                13022  10/31/2017 10:50:19  10/31/2017 10:51:40     746413636246     746413767648          1944637 
         NOT_APPLIED        936921167                1                13024  10/31/2017 10:52:19  10/31/2017 10:52:58     746413883688     746413999759          1819116 
         NOT_APPLIED        936921167                1                13025  10/31/2017 10:52:58  10/31/2017 10:53:40     746413999759     746414124264          1868420 
         NOT_APPLIED        936921167                1                13026  10/31/2017 10:53:40  10/31/2017 10:54:22     746414124264     746414244619          1890478 
         NOT_APPLIED        936921167                1                13027  10/31/2017 10:54:22  10/31/2017 10:55:02     746414244619     746414363387          1843514 
         NOT_APPLIED        936921167                1                13028  10/31/2017 10:55:02  10/31/2017 10:55:41     746414363387     746414484244          1818826 
         NOT_APPLIED        936921167                1                13029  10/31/2017 10:55:41  10/31/2017 10:56:20     746414484244     746414605367          1813344 
         NOT_APPLIED        936921167                1                13030  10/31/2017 10:56:20  10/31/2017 10:57:02     746414605367     746414728981          1904385 
         NOT_APPLIED        936921167                1                13035  10/31/2017 10:59:41  10/31/2017 11:29:41     746415217514     746415239037            79395 
   PARTIALLY_APPLIED        936921167                2                12352  10/31/2017 10:40:04  10/31/2017 10:47:07     746413130730     746413371576             1980 
         NOT_APPLIED        936921167                2                12353  10/31/2017 10:47:07  10/31/2017 10:50:22     746413371576     746413640990             1658 
         NOT_APPLIED        936921167                2                12354  10/31/2017 10:50:22  10/31/2017 10:53:01     746413640990     746414010894             1774 
         NOT_APPLIED        936921167                2                12355  10/31/2017 10:53:01  10/31/2017 10:55:04     746414010894     746414371654             1541 
         NOT_APPLIED        936921167                2                12356  10/31/2017 10:55:04  10/31/2017 10:57:04     746414371654     746414736501             1532 
         NOT_APPLIED        936921167                2                12357  10/31/2017 10:57:04  10/31/2017 10:59:04     746414736501     746415097318             1485 
         NOT_APPLIED        936921167                2                12358  10/31/2017 10:59:04  10/31/2017 11:29:05     746415097318     746415238854             6101 

Use ORACLE_UNQNAME for DataGuard Environment

Sat, 2017-10-28 09:25

If you are running only 1 database on the host, then it may not be useful.

However, if you run multiple databases, then it makes it easier to automate provided there are consistencies and/or conventions.

DB configuration

HOST01:(SYS@qa):PHYSICAL STANDBY> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      qa
db_unique_name                       string      qadr

OS configuration

$ env|grep ORACLE
ORACLE_BASE=/u01/app/oracle
ORACLE_SID=qa
ORACLE_UNQNAME=qadr
ORACLE_HOME=/u01/app/oracle/db/11g
$ ps -ef|grep pmon
  oracle  9896050        1   0 16:11:12      -  0:03 asm_pmon_+ASM
  oracle 10354862        1   0 20:06:31      -  0:02 ora_pmon_qa

Check DB status using srvctl

srvctl status database -d $ORACLE_UNQNAME -v
Database qadr is running with online services qarosvc
#!/bin/sh -e
. /opt/oracle/oracle_qa_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
exit
$ ./d.sh
DGMGRL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration

Configuration - dgqa

  Protection Mode: MaxPerformance
  Databases:
    qa   - Primary database
    qadr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database qa

Database - qa

  Enterprise Manager Name: qa_cluster
  Role:                    PRIMARY
  Intended State:          TRANSPORT-ON
  Instance(s):
    qa_1
    qa_2

Database Status:
SUCCESS

DGMGRL> show database qadr

Database - qadr

  Enterprise Manager Name: qa1
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds (computed 0 seconds ago)
  Apply Lag:               0 seconds (computed 1 second ago)
  Apply Rate:              937.00 KByte/s
  Real Time Query:         ON
  Instance(s):
    qa

Database Status:
SUCCESS

DGMGRL> exit

crsctl stat res -w “STATE = ONLINE”|egrep “db$|TYPE=ora.database.type”

NAME=ora.qadr.db
TYPE=ora.database.type
NAME=ora.qa2dr.db
TYPE=ora.database.type
NAME=ora.stageqadr.db
TYPE=ora.database.type
NAME=ora.testdr.db
TYPE=ora.database.type
dg_show.sh
#!/bin/sh -e
. /opt/oracle/oracle_qa_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
. /opt/oracle/oracle_qa2_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
. /opt/oracle/oracle_stageqa_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
. /opt/oracle/oracle_test_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
exit
Improved dg_show.sh using function.
#!/bin/sh -e
check_dg()
{
  dgmgrl -echo << END
  connect /
  show configuration
  show database ${ORACLE_SID}
  show database ${ORACLE_UNQNAME}
  exit
  END
}
. /opt/oracle/oracle_qa_env
check_dg
. /opt/oracle/oracle_qa2_env
check_dg
. /opt/oracle/oracle_stageqa_env
check_dg
. /opt/oracle/oracle_test_env
check_dg
exit

KUP-04095: preprocessor command qopiprep.bat

Fri, 2017-10-27 07:46

WARNING: Please open SR to confirm this is applicable to your environment as there are too many errors which look similar.

Applying Patch 26635880 – Database Proactive Bundle Patch 12.1.0.2.171017

# $GRID_HOME/OPatch/opatchauto apply $PATCHDIR/26635880

Starting database service on home /u01/oracle/db12c
Database service successfully started on home /u01/oracle/db12c

Preparing home /u01/oracle/db12c after database service restarted
No step execution required.........
Prepared home /u01/oracle/db12c successfully after database service restarted

Trying to apply SQL patch on home /u01/oracle/db12c
"/bin/sh -c 'ORACLE_HOME=/u01/oracle/db12c ORACLE_SID=db01
/u01/oracle/db12c/OPatch/datapatch'" command failed with errors. 
Please refer to logs for more details. SQL changes, if any, can be applied by manually retrying the same command.

SQL patch applied successfully on home /u01/oracle/db12c

Verifying patches applied on home /u01/oracle/gi12c
Patch verification completed with warning on home /u01/oracle/gi12c

Verifying patches applied on home /u01/oracle/db12c
Patch verification completed with warning on home /u01/oracle/db12c

OPatchAuto successful.

--------------------------------Summary--------------------------------
Output omitted for brevity.

This is the only results – no other errors.
SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------
ORA-20001: Latest xml inventory is not loaded into table

SQL> select * from OPATCH_XML_INV;

ERROR:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04095: preprocessor command /u01/oracle/db12c/QOpatch/qopiprep.bat
encountered error "Argument(s) Error... Cannot use file
"/u01/oracle/db12c/QOpatch/xml_file.xml" to generate XML output.
Specify path/filename and make sure filename doesn't"



no rows selected

SQL> host ls -l /u01/oracle/db12c/QOpatch/*
-rw-r--r--    1 oracle   dba          128227 Oct 24 16:24 /u01/oracle/db12c/QOpatch/qopatch_log.log
-r-xr-xr--    1 oracle   dba            1372 Apr 07 2013  /u01/oracle/db12c/QOpatch/qopiprep.bat
-rw-r--r--    1 oracle   dba            2349 Oct 24 16:24 /u01/oracle/db12c/QOpatch/stout.txt
-rw-r--r--    1 oracle   dba         2066473 Oct 24 16:24 /u01/oracle/db12c/QOpatch/xml_file.xml

SQL>

Oracle support provided solution as shown below:
Patch 20244108: QOPIPREP.BAT MODIFIES XML INVENTORY WHILE READING IT
Last Updated 03-Apr-2015 04:11 (2+ years ago)

What’s really FUBAR – patch is from 2+ years ago, so why wouldn’t it be included in BP?


DB 12.2 NF for Goldengate

Tue, 2017-10-24 07:30

Logical Replication

Error Handling Improvements for Oracle GoldenGate Integrated Replicat
Database views can manage and display details about Oracle GoldenGate conflict resolution methods, 
DBA_APPLY_REPERROR_HANDLERS 
DBA_APPLY_HANDLECOLLISIONS
DBA_APPLY_DML_CONF_HANDLERS
DBA_APPLY_DML_CONF_COLUMNS

A new capture parameter, ANNOTATE_DDL_SIZE, 
is available for Oracle GoldenGate integrated capture to specify the size of the buffer that describes the DDL more fully.

Finding EM Configuration

Sun, 2017-10-22 22:45

DIRECTORY STRUCTURE AND LOCATIONS OF IMPORTANT TRACE AND LOG FILES OF ENTERPRISE MANAGER CLOUD CONTROL 13C

MW_HOME=/u01/middleware
OMS_HOME=$MW_HOME/13.2.0
EM_INST_HOME=$OMS_HOME/gc_inst

ps -ef|grep agent_inst|tail -2
ps -ef|grep gc_inst|tail -2

OMS_HOME/install/setupinfo.txt
OMS_HOME/install/portlist.ini

 


DB Starts with SQLPlus not SRVCTL

Mon, 2017-10-16 20:25

Reason why DB was able to be started using SQL*Plus and not srvctl because DB was configured incorrectly with srvctl.

$ srvctl start database -d DB01
PRCR-1079 : Failed to start resource ora.db01.db
CRS-5017: The resource action "ora.db01.db start" encountered the following error:
ORA-01078: failure in processing system parameters. 
For details refer to "(:CLSN00107:)" in "/u01/app/oracle/product/11.2.0/grid_2/log/host01/agent/ohasd/oraagent_oracle//oraagent_log".

CRS-2674: Start of 'ora.db01.db' on 'host01' failed

--- Spfile pointing to non-existing pfile.
$ srvctl config database -d DB01
Database unique name: DB01
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_2
Oracle user: oracle
Spfile: /oracle/product/11.2.0/dbhome_2/dbs/initDB01.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: DB01
Disk Groups: DATA,FRA
Services:

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/db01/spfiledb01.ora

cat: /oracle/product/11.2.0/dbhome_2/dbs/initDB01.ora: No such file or directory

$ srvctl modify database -d DB01 -p +DATA/db01/spfiledb01.ora
$ srvctl config database -d DB01
Database unique name: DB01
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/db01/spfiledb01.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: DB01
Disk Groups: DATA,FRA
Services:

Cloning Goldengate Integrated Capture and DB

Tue, 2017-10-10 17:10

Using DBMS_STREAMS_ADM To Cleanup GoldenGate

Let’s say you want to clone DB and Goldengate implementation from PROD to DEV, then you need to drop the capture that was registered with PROD database.

This is what happens when dependencies are introduced / created.

select capture_name from dba_capture;
exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('&capture');

No Guarantees with opatch -report or CheckConflict

Mon, 2017-10-09 15:13

I have performed the following checks.

# $GRID_HOME/OPatch/opatch auto /media/swrepo/JUL2017PSU/26030799 -report -ocmrf /tmp/ocm.rsp
$ $GRID_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/swrepo/JUL2017PSU/26030799

Actual patching failed.

# $GRID_HOME/OPatch/opatch auto /media/swrepo/JUL2017PSU/26030799 -ocmrf /tmp/ocm.rsp
Executing /u01/app/oracle/product/11.2.0/grid/perl/bin/perl 
/u01/app/oracle/product/11.2.0/grid/OPatch/crs/patch11203.pl 
-patchdir /media/swrepo/JUL2017PSU -patchn 26030799 
-ocmrf /tmp/ocm.rsp -paramfile /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatchauto2017-10-09_10-35-34.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatchauto2017-10-09_10-35-34.report.log

2017-10-09 10:35:34: Starting Oracle Restart Patch Setup
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params

Stopping RAC /u01/app/oracle/product/11.2.0/dbhome_1 ...
Stopped RAC /u01/app/oracle/product/11.2.0/dbhome_1 successfully

patch /media/swrepo/JUL2017PSU/26030799/25869727  apply successful for home  /u01/app/oracle/product/11.2.0/dbhome_1
patch /media/swrepo/JUL2017PSU/26030799/25920335/custom/server/25920335  apply successful for home  /u01/app/oracle/product/11.2.0/dbhome_1

Stopping CRS...

Stopped CRS successfully

Error : The opatch Applicable check failed.  The patch /media/swrepo/JUL2017PSU/26030799/25920335 is not applicable to /u01/app/oracle/product/11.2.0/grid
Error:Patch Applicable check failed for /u01/app/oracle/product/11.2.0/grid

Starting CRS...

ERROR: Prereq checkApplicable failed. Refer log file for more details.


opatch auto failed.
#
Really useful info – ERROR: Prereq checkApplicable failed. Refer log file for more details.

I digress.

After some digging – search for ZOP-46 from /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch

$ grep -n "ZOP-46" opatch2017-10-09*.log
opatch2017-10-09_10-41-58AM_1.log:13:
[Oct 9, 2017 10:42:00 AM]    ZOP-46: 
The patch(es) are not applicable on the Oracle Home because some patch actions are not applicable. 
All required components, however, are installed.


$ head -25 opatch2017-10-09_10-41-58AM_1.log
[Oct 9, 2017 10:41:59 AM]    PREREQ session

[Oct 9, 2017 10:41:59 AM]    
OPatch invoked as follows: 'prereq CheckApplicable 
-ph /media/swrepo/JUL2017PSU/26030799/25920335 
-oh /u01/app/oracle/product/11.2.0/grid 
-invPtrLoc /u01/app/oracle/product/11.2.0/grid/oraInst.loc '

[Oct 9, 2017 10:41:59 AM]    OUI-67077:
                             Oracle Home       : /u01/app/oracle/product/11.2.0/grid
                             Central Inventory : /u01/app/oracle/oraInventory
                                from           : /u01/app/oracle/product/11.2.0/grid/oraInst.loc
                             OPatch version    : 11.2.0.3.6
                             OUI version       : 11.2.0.4.0
                             OUI location      : /u01/app/oracle/product/11.2.0/grid/oui
                             Log file location : /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch/opatch2017-10-09_10-41-58AM_1.log
[Oct 9, 2017 10:41:59 AM]    Patch history file: /u01/app/oracle/product/11.2.0/grid/cfgtoollogs/opatch/opatch_history.txt
[Oct 9, 2017 10:41:59 AM]    Invoking prereq "checkapplicable"

[Oct 9, 2017 10:42:00 AM]    
ZOP-46: The patch(es) are not applicable on the Oracle Home because some patch actions are not applicable. 
All required components, however, are installed.

[Oct 9, 2017 10:42:00 AM]    Patch 25920335:
                             Copy Action: Source File "/media/swrepo/JUL2017PSU/26030799/25920335/files/bin/appvipcfg.pl" does not exists or is not readable
                             'oracle.crs, 11.2.0.4.0': Cannot copy file from 'appvipcfg.pl' to '/u01/app/oracle/product/11.2.0/grid/bin/appvipcfg.pl'
                             Copy Action: Source File "/media/swrepo/JUL2017PSU/26030799/25920335/files/bin/oclumon.bin" does not exists or is not readable
                             'oracle.crs, 11.2.0.4.0': Cannot copy file from 'oclumon.bin' to '/u01/app/oracle/product/11.2.0/grid/bin/oclumon.bin'
                             Copy Action: Source File "/media/swrepo/JUL2017PSU/26030799/25920335/files/bin/ologgerd" does not exists or is not readable
                             'oracle.crs, 11.2.0.4.0': Cannot copy file from 'ologgerd' to '/u01/app/oracle/product/11.2.0/grid/bin/ologgerd'
                             Copy Action: Source File "/media/swrepo/JUL2017PSU/26030799/25920335/files/bin/osysmond.bin" does not exists or is not readable
                             'oracle.crs, 11.2.0.4.0': Cannot copy file from 'osysmond.bin' to '/u01/app/oracle/product/11.2.0/grid/bin/osysmond.bin'
                             Copy Action: Source File "/media/swrepo/JUL2017PSU/26030799/25920335/files/crs/demo/coldfailover/act_db.pl" does not exists or is not readable
                             'oracle.crs, 11.2.0.4.0': Cannot copy file from 'act_db.pl' to '/u01/app/oracle/product/11.2.0/grid/crs/demo/coldfailover/act_db.pl'
                             Copy Action: Source File "/media/swrepo/JUL2017PSU/26030799/25920335/files/crs/demo/coldfailover/act_listener.pl" does not exists or is not readable
$ ls -l /media/swrepo/JUL2017PSU/26030799/25920335/files/bin/appvipcfg.pl
-rwxr-x--- 1 root root 9051 Jun 27 07:40 /media/swrepo/JUL2017PSU/26030799/25920335/files/bin/appvipcfg.pl

Please don’t ask me why.

Solution.

# cd /media/
# chmod -R 777 swrepo/
# chown -R oracle:dba patches/

opatch report “ERROR: Prereq checkApplicable failed.” when Applying Grid Infrastructure patch (Doc ID 1417268.1)

	A. Expected behaviour if GRID_HOME has not been unlocked
 	B. Bug 13575478
 	C. The patch is stored in a shared NFS location and there is a permission issue accessing the patch
 	D. The patch is not unzipped as grid user, often it is unzipped as root user
 	E. The patch is unzipped inside GRID_HOME

In summary, trust but verify!


Upgrade 12.2 Journey – Fixing MEMORY_TARGET

Mon, 2017-10-09 05:08

As it turns out, root cause for MEMORY_TARGET is due to configuration.

Setting SQL> alter system set memory_target=0 scope=spfile; was the initial work around, the better solution is to fix root cause.

From alert_+ASM.log

2017-10-09T11:50:19.746027+02:00
WARNING: You are trying to use the MEMORY_TARGET feature. 
This feature requires the /dev/shm file system to be mounted for at least 1073741824 bytes. 
/dev/shm is either not mounted or is mounted with available space less than this size. 
Please fix this so that MEMORY_TARGET can work as expected. 
Current available is 915083264 and used is 0 bytes. 
Ensure that the mount point is /dev/shm for this directory.

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

[oracle@db-asm-1 ~]$ df /dev/shm/
Filesystem     1K-blocks  Used Available Use% Mounted on
tmpfs             893636     0    893636   0% /dev/shm
[oracle@db-asm-1 ~]$

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

[oracle@db-asm-1 dinh]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 9 11:48:44 2017

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


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

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 10M
sga_target                           big integer 0
SQL> show parameter size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 24M
large_pool_size                      big integer 12M
max_dump_file_size                   string      unlimited
parallel_execution_message_size      integer     16384
sga_max_size                         big integer 216M
shared_pool_reserved_size            big integer 8808038
shared_pool_size                     big integer 168M
sort_area_size                       integer     65536
unified_audit_sga_queue_size         integer     1048576
workarea_size_policy                 string      AUTO

SQL> alter system set memory_max_target=1G scope=spfile;

System altered.

SQL> shu immediate;
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup;
ORA-00845: MEMORY_TARGET not supported on this system
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@db-asm-1 dinh]$

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

[root@db-asm-1 ~]# df -h /dev/shm/
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           873M     0  873M   0% /dev/shm
[root@db-asm-1 ~]# mount -o remount,size=2G /dev/shm
[root@db-asm-1 ~]# df -h /dev/shm/
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           2.0G     0  2.0G   0% /dev/shm
[root@db-asm-1 ~]# vi /etc/fstab
[root@db-asm-1 ~]# cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Mon Apr 24 10:50:55 2017
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
UUID=b33f8d74-4869-460a-9835-abcbf5e7fc19 /                       ext4    defaults        1 1
UUID=f6263370-a504-46e9-bb85-26e2400eafae swap                    swap    defaults        0 0
/root/swapfile                            swap                    swap    defaults        0 0
tmpfs                   /dev/shm          tmpfs defaults,size=2G        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/vgora/lvora /u01 ext4 defaults 1 2
[root@db-asm-1 ~]# mount -a
[root@db-asm-1 ~]#

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

[oracle@db-asm-1 dinh]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 9 11:54:43 2017

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

Connected to an idle instance.

SQL> startup;
ASM instance started

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size            1039947064 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 1G
memory_target                        big integer 0
pga_aggregate_target                 big integer 10M
sga_target                           big integer 0
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@db-asm-1 dinh]$

Upgrade 12.2 Journey – Troubleshooting FAILED GRID Upgrade from 11.2.0.4 to 12.2.0.1

Sun, 2017-10-08 20:41

First, I am very thankful to all the bloggers out there.
It would have been nearly impossible to find resolution on one’s own.

ASM not starting with ORA-00845 – how to fix ASM parameter file

One issue after GI upgrade

Solution

[oracle@db-asm-1 ~]$ . oraenv <<< +ASM 
ORACLE_SID = [+ASM] ? The Oracle base remains unchanged with value /u01/app/oracle 

[oracle@db-asm-1 ~]$ sqlplus / as sysasm 
SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 9 01:47:54 2017 
Copyright (c) 1982, 2016, Oracle. All rights reserved. 
Connected to an idle instance. 

SQL> startup;
ORA-00845: MEMORY_TARGET not supported on this system
SQL> exit
Disconnected
[oracle@db-asm-1 ~]$

--- Find spfile.
[oracle@db-asm-1 ~]$ crsctl stat res ora.asm -p | egrep "ASM_DISKSTRING|SPFILE"
ASM_DISKSTRING=/dev/oracle/*
SPFILE=+CRS/asm/asmparameterfile/registry.253.956854045

--- Create pfile to start +ASM with MEMORY_TARGET=0.
[oracle@db-asm-1 ~]$ cat /tmp/initASM.ora
SPFILE=+CRS/asm/asmparameterfile/registry.253.956854045
MEMORY_TARGET=0
_asm_compatibility='11.2.0.4.0'

--- Mount CRS DG in restricted mode to modify 'compatible.asm'='11.2.0.4.0'.
[oracle@db-asm-1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 9 02:47:09 2017

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

Connected to an idle instance.

SQL> startup pfile=/tmp/initASM.ora
ASM instance started

Total System Global Area  226492416 bytes
Fixed Size                  8619064 bytes
Variable Size             192707528 bytes
ASM Cache                  25165824 bytes
ORA-15032: not all alterations performed
ORA-59303: The attribute compatible.asm (11.2.0.0.0) of the diskgroup being
mounted should be 11.2.0.2.0 or higher.

SQL> alter diskgroup CRS mount restricted;

Diskgroup altered.

SQL> alter diskgroup CRS set attribute 'compatible.asm'='11.2.0.4.0';

Diskgroup altered.

SQL> alter diskgroup CRS dismount;
alter diskgroup CRS dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "CRS" precludes its dismount

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@db-asm-1 ~]$ asmcmd lsdg
State       Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
RESTRICTED  EXTERN  N         512             512   4096  4194304     10236    10148                0           10148              0             N  CRS/
MOUNTED     EXTERN  N         512             512   4096  4194304     20472    18612                0           18612              0             N  DATA/
MOUNTED     EXTERN  N         512             512   4096  4194304     30708    30264                0           30264              0             N  FRA/

--- Restart +ASM since DG was not able to dismount.
[oracle@db-asm-1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 9 02:43:59 2017

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


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

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +CRS/asm/asmparameterfile/regi
                                                 stry.253.956854045
SQL> shut abort;
ASM instance shutdown

SQL> startup pfile=/tmp/initASM.ora
ASM instance started

Total System Global Area  226492416 bytes
Fixed Size                  8619064 bytes
Variable Size             192707528 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@db-asm-1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     10236    10148                0           10148              0             N  CRS/
MOUNTED  EXTERN  N         512             512   4096  4194304     20472    18612                0           18612              0             N  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304     30708    30264                0           30264              0             N  FRA/

--- Set memory_target=0 for spfile and restart ASM.
[oracle@db-asm-1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 9 02:47:09 2017

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


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

SQL> alter system set memory_target=0 scope=spfile;

System altered.

SQL> shu abort;
ASM instance shutdown
SQL> startup;
ASM instance started

Total System Global Area  226492416 bytes
Fixed Size                  8619064 bytes
Variable Size             192707528 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 10M
sga_target                           big integer 0
SQL> show parameter disk

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string      DATA, FRA, CRS
asm_diskstring                       string      /dev/oracle/*
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@db-asm-1 ~]$ asmcmd lsattr -G CRS -l
Name                     Value
access_control.enabled   FALSE
access_control.umask     066
au_size                  4194304
cell.smart_scan_capable  FALSE
compatible.asm           11.2.0.4.0
compatible.rdbms         10.1.0.0.0
content.type             data
disk_repair_time         3.6h
idp.boundary             auto
idp.type                 dynamic
sector_size              512
[oracle@db-asm-1 ~]$

[oracle@db-asm-1 ~]$ . oraenv <<< orclcdb
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@db-asm-1 ~]$ srvctl status database -d orclcdb
Database is running.
[oracle@db-asm-1 ~]$

[oracle@db-asm-1 ~]$ . oraenv <<< +ASM
ORACLE_SID = [+ASM] ? The Oracle base remains unchanged with value /u01/app/oracle
[oracle@db-asm-1 ~]$ echo $ORACLE_HOME
/u01/app/12.2.0.1/grid
[oracle@db-asm-1 ~]$ export GRID_HOME=$ORACLE_HOME
[oracle@db-asm-1 ~]$ $GRID_HOME/OPatch/opatch lspatches
There are no Interim patches installed in this Oracle Home "/u01/app/12.2.0.1/grid".

OPatch succeeded.

[oracle@db-asm-1 ~]$ $GRID_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/12.2.0.1/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/12.2.0.1/grid/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/12.2.0.1/grid/cfgtoollogs/opatch/opatch2017-10-09_03-36-17AM_1.log

Lsinventory Output file location : /u01/app/12.2.0.1/grid/cfgtoollogs/opatch/lsinv/lsinventory2017-10-09_03-36-17AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: db-asm-1.internal.lab
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Grid Infrastructure 12c                                       12.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.
[oracle@db-asm-1 ~]$

[oracle@db-asm-1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       db-asm-1                 STABLE
ora.DATA.dg
               ONLINE  ONLINE       db-asm-1                 STABLE
ora.FRA.dg
               ONLINE  ONLINE       db-asm-1                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       db-asm-1                 STABLE
ora.asm
               ONLINE  ONLINE       db-asm-1                 STABLE
ora.ons
               OFFLINE OFFLINE      db-asm-1                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       db-asm-1                 STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       db-asm-1                 STABLE
ora.orclcdb.db
      1        ONLINE  ONLINE       db-asm-1                 Open,HOME=/u01/app/o
                                                             racle/11.2.0.4/db1,S
                                                             TABLE
--------------------------------------------------------------------------------
[oracle@db-asm-1 ~]$

Q.E.D.

Upgrade 12.2 Journey – Failed GRID Upgrade from 11.2.0.4 to 12.2.0.1

Sun, 2017-10-08 19:05

Oracle Restart Upgrade Failed simply because CRS diskgroup is OFFLINE.

ora.CRS.dg
               OFFLINE OFFLINE      db-asm-1                 STABLE

Resolution coming up.

Upgrade Log
[oracle@db-asm-1 ~]$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.4.0]

[oracle@db-asm-1 ~]$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [11.2.0.4.0]
[oracle@db-asm-1 ~]$

00:58:42 SYS @ +ASM:>show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +CRS/asm/asmparameterfile/regi
                                                 stry.253.956854045
00:58:47 SYS @ +ASM:>show parameter disk

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string      DATA, FRA
asm_diskstring                       string      /dev/oracle/*
00:58:52 SYS @ +ASM:>

[oracle@db-asm-1 ~]$ srvctl config asm
ASM home: /u01/app/oracle/11.2.0.4/grid
ASM listener: LISTENER
Spfile: +CRS/asm/asmparameterfile/registry.253.956854045
ASM diskgroup discovery string: /dev/oracle/*

[oracle@db-asm-1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304     10236    10148                0           10148              0             N  CRS/
MOUNTED  EXTERN  N         512   4096  4194304     20472    18612                0           18612              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304     30708    30264                0           30264              0             N  FRA/
[oracle@db-asm-1 ~]$

[oracle@db-asm-1 ~]$ env|egrep -i 'oracle|home'
USER=oracle
LD_LIBRARY_PATH=/u01/app/oracle/11.2.0.4/grid/lib
ORACLE_SID=+ASM
ORACLE_BASE=/u01/app/oracle
MAIL=/var/spool/mail/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/11.2.0.4/grid/bin
PWD=/home/oracle
HOME=/home/oracle
GRID_HOME=/u01/app/oracle/11.2.0.4/grid
LOGNAME=oracle
LOG=/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCLCDB_APPLY_2017Oct08_21_41_02.log
ORACLE_HOME=/u01/app/oracle/11.2.0.4/grid
[oracle@db-asm-1 ~]$ unset ORACLE_SID ORACLE_BASE GRID_HOME ORACLE_HOME
[oracle@db-asm-1 ~]$ env|egrep -i 'oracle|home'
USER=oracle
LD_LIBRARY_PATH=/u01/app/oracle/11.2.0.4/grid/lib
MAIL=/var/spool/mail/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/11.2.0.4/grid/bin
PWD=/home/oracle
HOME=/home/oracle
LOGNAME=oracle
LOG=/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCLCDB_APPLY_2017Oct08_21_41_02.log
[oracle@db-asm-1 ~]$

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@db-asm-1 ~]$ cd /u01/app/12.2.0.1/grid/
[oracle@db-asm-1 grid]$ ./gridSetup.sh -silent -waitforcompletion -skipPrereqs -responseFile /u01/app/12.2.0.1/grid/install/response/grid_upgrade.rsp
Launching Oracle Grid Infrastructure Setup Wizard...

[WARNING] [INS-40436] Installer has detected an earlier version of Oracle Automatic Storage Management (ASM) Instance configured. Before proceeding with the upgrade you must shutdown all Oracle Databases that are using Oracle ASM for storage.

You may ignore this message and continue with the upgrade, if the database(s) are already in shutdown state.
[WARNING] [INS-41808] Possible invalid choice for OSASM Group.
   CAUSE: The name of the group you selected for the OSASM group is commonly used to grant other system privileges (For example: asmdba, asmoper, dba, oper).
   ACTION: Oracle recommends that you designate asmadmin as the OSASM group.
[WARNING] [INS-41809] Possible invalid choice for OSDBA Group.
   CAUSE: The group name you selected as the OSDBA for ASM group is commonly used for Oracle Database administrator privileges.
   ACTION: Oracle recommends that you designate asmdba as the OSDBA for ASM group, and that the group should not be the same group as an Oracle Database OSDBA group.
[WARNING] [INS-41810] Possible invalid choice for OSOPER Group.
   CAUSE: The group name you selected as the OSOPER for ASM group is commonly used for Oracle Database administrator privileges.
   ACTION: Oracle recommends that you designate asmoper as the OSOPER for ASM group, and that the group should not be the same group as an Oracle Database OSOPER group.
[WARNING] [INS-41812] OSDBA and OSASM are the same OS group.
   CAUSE: The chosen values for OSDBA group and the chosen value for OSASM group are the same.
   ACTION: Select an OS group that is unique for ASM administrators. The OSASM group should not be the same as the OS groups that grant privileges for Oracle ASM access, or for database administration.
[WARNING] [INS-32018] The selected Oracle home is outside of Oracle base.
   ACTION: Oracle recommends installing Oracle software within the Oracle base directory. Adjust the Oracle home or Oracle base accordingly.
You can find the log of this install session at:
 /u01/app/oraInventory/logs/GridSetupActions2017-10-09_01-03-02AM/gridSetupActions2017-10-09_01-03-02AM.log

As a root user, execute the following script(s):
        1. /u01/app/12.2.0.1/grid/rootupgrade.sh

Execute /u01/app/12.2.0.1/grid/rootupgrade.sh on the following nodes:
[db-asm-1]

Successfully Setup Software.
As install user, execute the following command to complete the configuration.
        /u01/app/12.2.0.1/grid/gridSetup.sh -executeConfigTools -responseFile /u01/app/12.2.0.1/grid/install/response/grid_upgrade.rsp [-silent]

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++        

[oracle@db-asm-1 grid]$ ps -ef|grep pmon
oracle    3333 26998  0 01:19 pts/2    00:00:00 grep pmon
oracle   14084     1  0 Oct08 ?        00:00:02 asm_pmon_+ASM
oracle   14346     1  0 Oct08 ?        00:00:02 ora_pmon_orclcdb
[oracle@db-asm-1 grid]$ asmcmd lsdg
Connected to an idle instance.
ASMCMD-8102: no connection to Oracle ASM; command requires Oracle ASM to run

[oracle@db-asm-1 grid]$ srvctl status asm
ASM is running on db-asm-1
[oracle@db-asm-1 grid]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       db-asm-1
ora.DATA.dg
               ONLINE  ONLINE       db-asm-1
ora.FRA.dg
               ONLINE  ONLINE       db-asm-1
ora.LISTENER.lsnr
               ONLINE  ONLINE       db-asm-1
ora.asm
               ONLINE  ONLINE       db-asm-1                 Started
ora.ons
               OFFLINE OFFLINE      db-asm-1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       db-asm-1
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       db-asm-1
ora.orclcdb.db
      1        ONLINE  ONLINE       db-asm-1                 Open

[oracle@db-asm-1 grid]$ env|egrep -i 'oracle|home'
OLDPWD=/home/oracle
USER=oracle
LD_LIBRARY_PATH=/u01/app/oracle/11.2.0.4/grid/lib
MAIL=/var/spool/mail/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/11.2.0.4/grid/bin
HOME=/home/oracle
LOGNAME=oracle
LOG=/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCLCDB_APPLY_2017Oct08_21_41_02.log
[oracle@db-asm-1 grid]$

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@db-asm-1 ~]$ su -
Password:
[root@db-asm-1 ~]# /u01/app/12.2.0.1/grid/rootupgrade.sh
Check /u01/app/12.2.0.1/grid/install/root_db-asm-1_2017-10-09_01-22-49-043808622.log for the output of root script

[root@db-asm-1 ~]# ps -ef|grep pmon
root      8648  3386  0 01:32 pts/1    00:00:00 grep pmon
[root@db-asm-1 ~]#

[oracle@db-asm-1 ~]$ cat /u01/app/12.2.0.1/grid/install/root_db-asm-1_2017-10-09_01-22-49-043808622.log
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/12.2.0.1/grid
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/12.2.0.1/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/oracle/crsdata/db-asm-1/crsconfig/roothas_2017-10-09_01-22-50AM.log
2017/10/09 01:23:00 CLSRSC-363: User ignored prerequisites during installation

ASM has been upgraded and started successfully.

Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node db-asm-1 successfully pinned.
2017/10/09 01:27:19 CLSRSC-329: Replacing Clusterware entries in file 'oracle-ohasd.conf'
CRS-4123: Oracle High Availability Services has been started.

2017/10/09 01:29:31 CLSRSC-482: Running command: 'srvctl upgrade model -s 11.2.0.4.0 -d 12.2.0.1.0 -p first'
2017/10/09 01:29:52 CLSRSC-482: Running command: 'srvctl upgrade model -s 11.2.0.4.0 -d 12.2.0.1.0 -p last'

db-asm-1     2017/10/09 01:29:57     /u01/app/12.2.0.1/grid/cdata/db-asm-1/backup_20171009_012957.olr     0

db-asm-1     2017/10/08 16:45:27     /u01/app/oracle/11.2.0.4/grid/cdata/db-asm-1/backup_20171008_164527.olr     -
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.evmd' on 'db-asm-1'
CRS-2677: Stop of 'ora.evmd' on 'db-asm-1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'db-asm-1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2017/10/09 01:31:14 CLSRSC-327: Successfully configured Oracle Restart for a standalone server
[oracle@db-asm-1 ~]$

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@db-asm-1 grid]$ ps -ef|grep pmon
oracle    8652 26998  0 01:33 pts/2    00:00:00 grep pmon

[oracle@db-asm-1 grid]$ /u01/app/12.2.0.1/grid/gridSetup.sh -executeConfigTools -responseFile /u01/app/12.2.0.1/grid/install/response/grid_upgrade.rsp -silent
Launching Oracle Grid Infrastructure Setup Wizard...

You can find the logs of this session at:
/u01/app/oraInventory/logs/GridSetupActions2017-10-09_01-33-40AM

Successfully Configured Software.

[oracle@db-asm-1 grid]$ cd /u01/app/oraInventory/logs/GridSetupActions2017-10-09_01-33-40AM
[oracle@db-asm-1 GridSetupActions2017-10-09_01-33-40AM]$ ls -lrt
total 80
-rw-r-----. 1 oracle oinstall   129 Oct  9 01:33 installerPatchActions_2017-10-09_01-33-40AM.log
-rw-r-----. 1 oracle oinstall     0 Oct  9 01:33 gridSetupActions2017-10-09_01-33-40AM.err
-rw-r-----. 1 oracle oinstall   196 Oct  9 01:35 gridSetupActions2017-10-09_01-33-40AM.out
-rw-r-----. 1 oracle oinstall  2176 Oct  9 01:35 time2017-10-09_01-33-40AM.log
-rw-r-----. 1 oracle oinstall 67881 Oct  9 01:35 gridSetupActions2017-10-09_01-33-40AM.log

[oracle@db-asm-1 GridSetupActions2017-10-09_01-33-40AM]$ ps -ef|grep pmon
oracle    9335 26998  0 01:37 pts/2    00:00:00 grep pmon

[oracle@db-asm-1 GridSetupActions2017-10-09_01-33-40AM]$ tail /etc/oratab
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orclcdb:/u01/app/oracle/11.2.0.4/db1:N          # line added by Agent
+ASM:/u01/app/12.2.0.1/grid:N           # line added by Agent
[oracle@db-asm-1 GridSetupActions2017-10-

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@db-asm-1 ~]$ . oraenv <<< +ASM
ORACLE_SID = [oracle] ? The Oracle base has been set to /u01/app/oracle
[oracle@db-asm-1 ~]$ echo $ORACLE_HOME
/u01/app/12.2.0.1/grid
[oracle@db-asm-1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               OFFLINE OFFLINE      db-asm-1                 STABLE
ora.DATA.dg
               ONLINE  OFFLINE      db-asm-1                 STABLE
ora.FRA.dg
               ONLINE  OFFLINE      db-asm-1                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       db-asm-1                 STABLE
ora.asm
               ONLINE  OFFLINE      db-asm-1                 STABLE
ora.ons
               OFFLINE OFFLINE      db-asm-1                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       db-asm-1                 STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       db-asm-1                 STABLE
ora.orclcdb.db
      1        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
--------------------------------------------------------------------------------
[oracle@db-asm-1 ~]$ ps -ef|grep pmon
oracle    9360  4055  0 01:39 pts/3    00:00:00 grep pmon
[oracle@db-asm-1 ~]$ asmcmd lsdg
Connected to an idle instance.
ASMCMD-8102: no connection to Oracle ASM; command requires Oracle ASM to run

[oracle@db-asm-1 ~]$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [12.2.0.1.0]

[oracle@db-asm-1 ~]$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [12.2.0.1.0]
[oracle@db-asm-1 ~]$


Upgrade 12.2 Journey – ORAchk Failed To Detect compatible.asm=11.2.0.0.0 Issue

Sun, 2017-10-08 18:04

Ran orachk and it did not detect compatibility issues.

./orachk -u -o pre

WARNING OS Check Package cvuqdisk-1.0.9-1-x86_64 is recommended but NOT installed All Database Servers
WARNING OS Check Package unixODBC-devel-2.2.14-11.el6-i686 is recommended but NOT installed All Database Servers
WARNING OS Check Package unixODBC-2.2.14-11.el6-i686 is recommended but NOT installed All Database Servers
WARNING OS Check ORACLE_HOME environment variable is set for database software owner All Database Servers


Upgrade 12.2 Journey – Preparation for 12.2 Upgrade

Sun, 2017-10-08 17:20

This is exactly what I did last time and upgrade will fail due to compatible.asm=11.2.0.0.0

Goal now is to now to detect and prevent the failure.

Let’s see if ORAchk – Health Checks for the Oracle Stack (Doc ID 1268927.2) will detect this.

[oracle@db-asm-1 ~]$ asmcmd lsattr -G CRS -l
Name                     Value
access_control.enabled   FALSE
access_control.umask     066
au_size                  4194304
cell.smart_scan_capable  FALSE
compatible.asm           11.2.0.0.0
compatible.rdbms         10.1.0.0.0
disk_repair_time         3.6h
sector_size              512
[oracle@db-asm-1 ~]$
Install Grid 12.2, runcluvfy.sh, resolve swap issue, create grid_upgrade.rsp
[oracle@db-asm-1 ~]$ mkdir -p /u01/app/12.2.0.1/grid
[oracle@db-asm-1 ~]$ ls -l /sf_OracleSoftware/12.2.0.1_Linux_64/
total 6297251
-rwxrwxrwx. 1 vagrant vagrant 3453696911 Apr 12 15:44 linuxx64_12201_database.zip
-rwxrwxrwx. 1 vagrant vagrant 2994687209 Apr 12 15:43 linuxx64_12201_grid_home.zip
[oracle@db-asm-1 ~]$ unzip -d /u01/app/12.2.0.1/grid -qo /sf_OracleSoftware/12.2.0.1_Linux_64/linuxx64_12201_grid_home.zip; echo $?
0
[oracle@db-asm-1 grid]$ ./runcluvfy.sh stage -pre hacfg
Pre-check for Oracle Restart configuration was unsuccessful.
Failures were encountered during execution of CVU verification request "stage -pre hacfg".

Verifying Physical Memory ...FAILED
db-asm-1: PRVF-7530 : Sufficient physical memory is not available on node
          "db-asm-1" [Required physical memory = 8GB (8388608.0KB)]

Verifying Swap Size ...FAILED
db-asm-1: PRVF-7573 : Sufficient swap size is not available on node "db-asm-1"
          [Required = 1.5GB (1572864.0KB) ; Found = 1023.9961MB (1048572.0KB)]

CVU operation performed:      stage -pre hacfg
Date:                         Oct 8, 2017 11:36:24 PM
CVU home:                     /u01/app/12.2.0.1/grid/
User:                         oracle

[root@db-asm-1 ~]# swapon -s
Filename                                Type            Size    Used    Priority
/dev/sda1                               partition       1048572 20860   -1

[root@db-asm-1 ~]# cat /proc/swaps
Filename                                Type            Size    Used    Priority
/dev/sda1                               partition       1048572 20860   -1

[root@db-asm-1 ~]# cat /etc/fstab|grep swap
UUID=f6263370-a504-46e9-bb85-26e2400eafae swap                    swap    defaults        0 0

[root@db-asm-1 ~]# df -h /root/
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        47G  2.4G   43G   6% /

[root@db-asm-1 ~]# dd if=/dev/zero of=/root/swapfile count=1024 bs=2097152
1024+0 records in
1024+0 records out
2147483648 bytes (2.1 GB) copied, 4.2507 s, 505 MB/s

[root@db-asm-1 ~]# mkswap -c /root/swapfile
mkswap: /root/swapfile: warning: don't erase bootbits sectors
        on whole disk. Use -f to force.
Setting up swapspace version 1, size = 2097148 KiB
no label, UUID=c0aa5207-4882-4ce5-8943-1b245e1615dc

[root@db-asm-1 ~]# swapon /root/swapfile
[root@db-asm-1 ~]# swapon -s
Filename                                Type            Size    Used    Priority
/dev/sda1                               partition       1048572 20860   -1
/root/swapfile                          file            2097148 0       -2

[root@db-asm-1 ~]# cat /etc/fstab|grep swap
UUID=f6263370-a504-46e9-bb85-26e2400eafae swap                    swap    defaults        0 0

[root@db-asm-1 ~]# vi /etc/fstab
[root@db-asm-1 ~]# cat /etc/fstab|grep swap
UUID=f6263370-a504-46e9-bb85-26e2400eafae swap                    swap    defaults        0 0
/root/swapfile                            swap                    swap    defaults        0 0
[root@db-asm-1 ~]#

[oracle@db-asm-1 grid]$ ./runcluvfy.sh stage -pre hacfg
Pre-check for Oracle Restart configuration was unsuccessful.
Failures were encountered during execution of CVU verification request "stage -pre hacfg".

Verifying Physical Memory ...FAILED
db-asm-1: PRVF-7530 : Sufficient physical memory is not available on node
          "db-asm-1" [Required physical memory = 8GB (8388608.0KB)]

CVU operation performed:      stage -pre hacfg
Date:                         Oct 8, 2017 11:49:01 PM
CVU home:                     /u01/app/12.2.0.1/grid/
User:                         oracle
[oracle@db-asm-1 grid]$

[oracle@db-asm-1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/11.2.0.4/db1

[oracle@db-asm-1 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with
              rdbms/src/server/osds/config.c, specifically regarding the
              number of elements in the ss_dba_grp array.
 */

#define SS_DBA_GRP "oinstall"
#define SS_OPER_GRP "oinstall"
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};
[oracle@db-asm-1 ~]$ 

[oracle@db-asm-1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/11.2.0.4/grid

[oracle@db-asm-1 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with
              rdbms/src/server/osds/config.c, specifically regarding the
              number of elements in the ss_dba_grp array.
 */

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"
#define SS_ASM_GRP "dba"

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};
[oracle@db-asm-1 ~]$

[oracle@db-asm-1 ~]$ cd /u01/app/12.2.0.1/grid/install/response/
[oracle@db-asm-1 response]$ ls -l
total 76
-rw-r--r--. 1 oracle oinstall 34357 Jan 26  2017 grid_2017-01-26_04-10-28PM.rsp
-rw-r-----. 1 oracle oinstall 35087 Jan 26  2017 gridsetup.rsp
-rw-r-----. 1 oracle oinstall  1541 May 20  2016 sample.ccf

[oracle@db-asm-1 response]$ cp -v gridsetup.rsp grid_upgrade.rsp
`gridsetup.rsp' -> `grid_upgrade.rsp'

[oracle@db-asm-1 response]$ vi grid_upgrade.rsp
[oracle@db-asm-1 response]$ diff -iwyB --suppress-common-lines -W 150 gridsetup.rsp grid_upgrade.rsp; echo
oracle.install.option=                                                    |     oracle.install.option=UPGRADE
ORACLE_BASE=                                                              |     ORACLE_BASE=/u01/app/oracle
oracle.install.asm.OSDBA=                                                 |     oracle.install.asm.OSDBA=dba
oracle.install.asm.OSOPER=                                                |     oracle.install.asm.OSOPER=oper
oracle.install.asm.OSASM=                                                 |     oracle.install.asm.OSASM=dba

[oracle@db-asm-1 response]$
Incorrect group will results in failure.
[oracle@db-asm-1 ~]$ groups
oinstall asmdba dba backupdba oper dgdba kmdba

[oracle@db-asm-1 ~]$ id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54318(asmdba),54322(dba),54323(backupdba),54324(oper),54325(dgdba),54326(kmdba)
[oracle@db-asm-1 ~]$

oracle@db-asm-1::/u01/app/12.2.0.1/grid/install/response
> diff -iwyB --suppress-common-lines -W 150 gridsetup.rsp grid_upgrade.rsp; echo
oracle.install.option=                                                    |     oracle.install.option=UPGRADE
ORACLE_BASE=                                                              |     ORACLE_BASE=/u01/app/oracle
oracle.install.asm.OSDBA=                                                 |     oracle.install.asm.OSDBA=asmdba
oracle.install.asm.OSOPER=                                                |     oracle.install.asm.OSOPER=asmdba
oracle.install.asm.OSASM=                                                 |     oracle.install.asm.OSASM=asmdba

oracle@db-asm-1::/u01/app/12.2.0.1/grid
> ./gridSetup.sh -silent -waitforcompletion -skipPrereqs -responseFile /u01/app/12.2.0.1/grid/install/response/grid_upgrade.rsp

Launching Oracle Grid Infrastructure Setup Wizard...

[WARNING] [INS-40436] Installer has detected an earlier version of Oracle Automatic Storage Management (ASM) Instance configured. Before proceeding with the upgrade you must shutdown all Oracle Databases that are using Oracle ASM for storage.

You may ignore this message and continue with the upgrade, if the database(s) are already in shutdown state.
[WARNING] [INS-41808] Possible invalid choice for OSASM Group.
   CAUSE: The name of the group you selected for the OSASM group is commonly used to grant other system privileges (For example: asmdba, asmoper, dba, oper).
   ACTION: Oracle recommends that you designate asmadmin as the OSASM group.
[WARNING] [INS-41813] OSDBA for ASM, OSOPER for ASM, and OSASM are the same OS group.
   CAUSE: The group you selected for granting the OSDBA for ASM group for database access, and the OSOPER for ASM group for startup and shutdown of Oracle ASM, is the same group as the OSASM group, whose members have SYSASM privileges on Oracle ASM.
   ACTION: Choose different groups as the OSASM, OSDBA for ASM, and OSOPER for ASM groups.

[FATAL] [INS-41881] Installer has detected that the group specified for OSDBA is not same as the group 'dba' retrieved from the current configuration of grid software. 
The upgrade operation will not be successful if the same group name is not selected.
   ACTION: Select the same group and proceed further.

[FATAL] [INS-41881] Installer has detected that the group specified for OSOPER is not same as the group 'oper' retrieved from the current configuration of grid software. 
The upgrade operation will not be successful if the same group name is not selected.
   ACTION: Select the same group and proceed further.

[FATAL] [INS-41881] Installer has detected that the group specified for OSASM is not same as the group 'dba' retrieved from the current configuration of grid software. 
The upgrade operation will not be successful if the same group name is not selected.
   ACTION: Select the same group and proceed further.

Pages