Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 12 hours 27 min ago

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.

Upgrade 12.2 Journey – Apply July 2017 PSU

Sun, 2017-10-08 16:08

Patches to apply before upgrading Oracle GI and DB to 12.2.0.1 (Doc ID 2180188.1)

GI PSU 22646198
11.2.0.4.160419 (Apr 2016) Grid Infrastructure Patch Set Update (GI PSU)
Recommended to install latest* 11.2.0.4 GI PSU

Patch Set Update and Critical Patch Update July 2017 Availability Document (Doc ID 2261562.1)

    OPatch utility version 11.2.0.3.6 or later

    --------------------------------------------------------------------------------
    Patch 26030799 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.170718 (Jul2017) 
    (Includes Database PSU 11.2.0.4.170718)
    --------------------------------------------------------------------------------
    25869727
    DB PSU 11.2.0.4.170718 (INCLUDES CPUJUL2017)
    Both DB Homes and Grid Home

    25920335
    OCW PSU 11.2.0.4.170718
    Both DB Homes and Grid Home

    22502505
    ACFS PSU 11.2.0.4.160419
    Only Grid Home

    --------------------------------------------------------------------------------
    Patch 26027154 - Oracle JavaVM Component 11.2.0.4.170718 Database PSU
    --------------------------------------------------------------------------------
    Oracle JavaVM Component 11.2.0.4.170718 Database PSU

====================================================================================================
Oracle Recommended Patches -- "Oracle JavaVM Component Database PSU and RU" (OJVM PSU and OJVM RU) Patches (Doc ID 1929745.1)	
====================================================================================================
    Jul 2016 - present (12.1.0.1, 11.2.0.4)

    Database Home - OJVM PSU (Jul 2016 - present)
    Grid Home - JDBC Patch (Jul 2016)

    Oracle JavaVM Component 11.2.0.4.160719 Database PSU - Generic JDBC Patch 23727132

GRID PSU

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

[oracle@db-asm-1 dinh]$ export GRID_HOME=$ORACLE_HOME
[oracle@db-asm-1 dinh]$ $GRID_HOME/OPatch/opatch lspatches
23727132;
22502505;ACFS Patch Set Update : 11.2.0.4.160419 (22502505)
25920335;OCW Patch Set Update : 11.2.0.4.170718 (25920335)
25869727;Database Patch Set Update : 11.2.0.4.170718 (25869727)

OPatch succeeded.

[oracle@db-asm-1 dinh]$ crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.

[oracle@db-asm-1 dinh]$ crsctl check has
CRS-4638: Oracle High Availability Services is online

[oracle@db-asm-1 dinh]$ 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 dinh]$ 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/

DB PSU

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

[oracle@db-asm-1 dinh]$ $ORACLE_HOME/OPatch/opatch lspatches
26027154;OJVM PATCH SET UPDATE 11.2.0.4.170718
25920335;OCW Patch Set Update : 11.2.0.4.170718 (25920335)
25869727;Database Patch Set Update : 11.2.0.4.170718 (25869727)

OPatch succeeded.

[oracle@db-asm-1 dinh]$ srvctl config database
orclcdb

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

[oracle@db-asm-1 dinh]$ sqlplus / as sysdba @ dba_registry.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 8 22:22:49 2017

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

 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

 

Session altered.

 

NAME COMP_ID COMP_NAME VERSION SCHEMA STATUS
---------- ------------ ---------------------------------------- ------------------------------ --------------- ------------
ORCLCDB OWB OWB 11.2.0.4.0 OWBSYS VALID
ORCLCDB APEX Oracle Application Express 3.2.1.00.12 APEX_030200 VALID
ORCLCDB EM Oracle Enterprise Manager 11.2.0.4.0 SYSMAN VALID
ORCLCDB SDO Spatial 11.2.0.4.0 MDSYS VALID
ORCLCDB AMD OLAP Catalog 11.2.0.4.0 OLAPSYS VALID
ORCLCDB XOQ Oracle OLAP API 11.2.0.4.0 SYS VALID
ORCLCDB APS OLAP Analytic Workspace 11.2.0.4.0 SYS VALID
ORCLCDB ORDIM Oracle Multimedia 11.2.0.4.0 ORDSYS VALID
ORCLCDB RUL Oracle Rules Manager 11.2.0.4.0 EXFSYS VALID
ORCLCDB XDB Oracle XML Database 11.2.0.4.0 XDB VALID
ORCLCDB CONTEXT Oracle Text 11.2.0.4.0 CTXSYS VALID
ORCLCDB EXF Oracle Expression Filter 11.2.0.4.0 EXFSYS VALID
ORCLCDB CATJAVA Oracle Database Java Packages 11.2.0.4.0 SYS VALID
ORCLCDB XML Oracle XDK 11.2.0.4.0 SYS VALID
ORCLCDB JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 SYS VALID
ORCLCDB OWM Oracle Workspace Manager 11.2.0.4.0 WMSYS VALID
ORCLCDB CATPROC Oracle Database Packages and Types 11.2.0.4.0 SYS VALID
ORCLCDB CATALOG Oracle Database Catalog Views 11.2.0.4.0 SYS VALID

18 rows selected.

 

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SER
------------------------------ --------------- --------------- ------------------------------ ---------- ------------------------------ ----------
08-OCT-17 09.57.45.178375 PM APPLY 26027154 Patch 26027154 applied
08-OCT-17 09.57.45.172250 PM APPLY SERVER 11.2.0.4.170718OJVMPSU 0 OJVM PSU post-install
08-OCT-17 09.57.45.095287 PM jvmpsu.sql SERVER 11.2.0.4.170718OJVMPSU 0 RAN jvmpsu.sql
08-OCT-17 09.42.07.465250 PM APPLY SERVER 11.2.0.4 170718 PSU 11.2.0.4.170718 PSU
08-OCT-17 05.16.54.900094 PM APPLY SERVER 11.2.0.4 0 Patchset 11.2.0.2.0 PSU
24-AUG-13 12.03.45.119862 PM APPLY SERVER 11.2.0.4 0 Patchset 11.2.0.2.0 PSU

6 rows selected.

22:22:49 SYS @ orclcdb:>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@db-asm-1 dinh]$

 


Upgrade 12.2 Journey – Why Upgrade Failed

Sun, 2017-10-08 13:43

I already know upgrade will fail and will reproduce to show how to resolve the issue.

Curious how did it happened.

Without the benefit of knowing what will happened, is there any way this could have been detected.

RTFM – but did not find anything in the matter.

From Vagant:

TASK [oraasm-createdg : ASMCA | List ASM diskgroups]
compatible.asm=11.2.0.4

From alert_+ASM.log:

SQL> CREATE DISKGROUP crs EXTERNAL REDUNDANCY  DISK '/dev/oracle/crs01' ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='4M' /* ASMCA */

Details:

TASK [oraasm-createdg : ASMCA | Create script to build asm-diskgroups] *********
changed: [db-asm-1] => (item={u'attributes': [{u'name': u'compatible.rdbms', u'value': u'11.2.0.4.0'}, {u'name': u'compatible.asm', u'value': u'11.2.0.4'}], u'disk': [{u'device': u'/dev/sdd', u'asmlabel': u'data01'}, {u'device': u'/dev/sde', u'asmlabel': u'data02'}], u'diskgroup': u'data', u'properties': [{u'redundancy': u'external', u'ausize': 4}]})
changed: [db-asm-1] => (item={u'attributes': [{u'name': u'compatible.rdbms', u'value': u'11.2.0.4.0'}, {u'name': u'compatible.asm', u'value': u'11.2.0.4'}], u'disk': [{u'device': u'/dev/sdf', u'asmlabel': u'fra01'}, {u'device': u'/dev/sdg', u'asmlabel': u'fra02'}, {u'device': u'/dev/sdh', u'asmlabel': u'fra03'}], u'diskgroup': u'fra', u'properties': [{u'redundancy': u'external', u'ausize': 4}]})

TASK [oraasm-createdg : ASMCA | Create ASM diskgroups] *************************
changed: [db-asm-1] => (item={u'attributes': [{u'name': u'compatible.rdbms', u'value': u'11.2.0.4.0'}, {u'name': u'compatible.asm', u'value': u'11.2.0.4'}], u'disk': [{u'device': u'/dev/sdd', u'asmlabel': u'data01'}, {u'device': u'/dev/sde', u'asmlabel': u'data02'}], u'diskgroup': u'data', u'properties': [{u'redundancy': u'external', u'ausize': 4}]})
changed: [db-asm-1] => (item={u'attributes': [{u'name': u'compatible.rdbms', u'value': u'11.2.0.4.0'}, {u'name': u'compatible.asm', u'value': u'11.2.0.4'}], u'disk': [{u'device': u'/dev/sdf', u'asmlabel': u'fra01'}, {u'device': u'/dev/sdg', u'asmlabel': u'fra02'}, {u'device': u'/dev/sdh', u'asmlabel': u'fra03'}], u'diskgroup': u'fra', u'properties': [{u'redundancy': u'external', u'ausize': 4}]})

TASK [oraasm-createdg : Print Results] *****************************************
fatal: [db-asm-1]: FAILED! => {"failed": true, "msg": "template error while templating string: no filter named 'map'. String: {{ asmca.results|map(attribute='stdout_lines')|list }}"}
...ignoring

TASK [oraasm-createdg : ASMCA | List ASM diskgroups] ***************************
changed: [db-asm-1] => (item={u'attributes': 
[{u'name': u'compatible.rdbms', u'value': u'11.2.0.4.0'}, 
{u'name': u'compatible.asm', u'value': u'11.2.0.4'}], 
u'disk': [{u'device': u'/dev/sdc', u'asmlabel': u'crs01'}], 
u'diskgroup': u'crs', u'properties': [{u'redundancy': u'external', u'ausize': 4}]})

changed: [db-asm-1] => (item={u'attributes': [{u'name': u'compatible.rdbms', u'value': u'11.2.0.4.0'}, {u'name': u'compatible.asm', u'value': u'11.2.0.4'}], u'disk': [{u'device': u'/dev/sdd', u'asmlabel': u'data01'}, {u'device': u'/dev/sde', u'asmlabel': u'data02'}], u'diskgroup': u'data', u'properties': [{u'redundancy': u'external', u'ausize': 4}]})
changed: [db-asm-1] => (item={u'attributes': [{u'name': u'compatible.rdbms', u'value': u'11.2.0.4.0'}, {u'name': u'compatible.asm', u'value': u'11.2.0.4'}], u'disk': [{u'device': u'/dev/sdf', u'asmlabel': u'fra01'}, {u'device': u'/dev/sdg', u'asmlabel': u'fra02'}, {u'device': u'/dev/sdh', u'asmlabel': u'fra03'}], u'diskgroup': u'fra', u'properties': [{u'redundancy': u'external', u'ausize': 4}]})

TASK [oraasm-createdg : list diskgroups] ***************************************
fatal: [db-asm-1]: FAILED! => {"failed": true, "msg": "template error while templating string: no filter named 'map'. String: {{ srvctlasm.results|map(attribute='stdout_lines')|list }}"}
...ignoring


[oracle@db-asm-1 trace]$ 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    18652                0           18652              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304     30708    30264                0           30264              0             N  FRA/
[oracle@db-asm-1 trace]$ 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 trace]$ asmcmd lsattr -G DATA -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         11.2.0.4.0
content.type             data
disk_repair_time         3.6h
idp.boundary             auto
idp.type                 dynamic
sector_size              512
[oracle@db-asm-1 trace]$ asmcmd lsattr -G FRA -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         11.2.0.4.0
content.type             data
disk_repair_time         3.6h
idp.boundary             auto
idp.type                 dynamic
sector_size              512
[oracle@db-asm-1 trace]$

[oracle@db-asm-1 trace]$ head alert_+ASM.log
Sun Oct 08 16:46:58 2017

MEMORY_TARGET defaulting to 1128267776.
* instance_number obtained from CSS = 1, checking for the existence of node 0...
* node 0 does not exist. instance_number = 1
Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature. 
This feature requires the /dev/shm file system to be mounted for at least 1140850688 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 914882560 and used is 200704 bytes. 
Ensure that the mount point is /dev/shm for this directory.

LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 1
Shared memory segment for instance monitoring created
[oracle@db-asm-1 trace]$

[oracle@db-asm-1 trace]$ grep compatible.asm alert_+ASM.log
SQL> CREATE DISKGROUP crs EXTERNAL REDUNDANCY  DISK '/dev/oracle/crs01' ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='4M' /* ASMCA */
NOTE: Instance updated compatible.asm to 11.2.0.0.0 for grp 1
SUCCESS: CREATE DISKGROUP crs EXTERNAL REDUNDANCY  DISK '/dev/oracle/crs01' ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='4M' /* ASMCA */
NOTE: Instance updated compatible.asm to 11.2.0.0.0 for grp 1
'/dev/oracle/data02' ATTRIBUTE 'compatible.asm'='11.2.0.4','compatible.rdbms'='11.2.0.4.0','au_size'='4M' /* ASMCA */
NOTE: Instance updated compatible.asm to 11.2.0.4.0 for grp 2
'/dev/oracle/data02' ATTRIBUTE 'compatible.asm'='11.2.0.4','compatible.rdbms'='11.2.0.4.0','au_size'='4M' /* ASMCA */
'/dev/oracle/fra03' ATTRIBUTE 'compatible.asm'='11.2.0.4','compatible.rdbms'='11.2.0.4.0','au_size'='4M' /* ASMCA */
NOTE: Instance updated compatible.asm to 11.2.0.4.0 for grp 3
'/dev/oracle/fra03' ATTRIBUTE 'compatible.asm'='11.2.0.4','compatible.rdbms'='11.2.0.4.0','au_size'='4M' /* ASMCA */
[oracle@db-asm-1 trace]$

Upgrade 12.2 Journey – Update Opatch

Sun, 2017-10-08 11:14
[oracle@db-asm-1 ~]$ ps -ef|grep pmon
oracle    1413     1  0 17:18 ?        00:00:00 ora_pmon_orclcdb
oracle   10752 10731  0 17:52 pts/1    00:00:00 grep pmon
oracle   24615     1  0 16:47 ?        00:00:00 asm_pmon_+ASM

[oracle@db-asm-1 ~]$ . oraenv <<< orclcdb 
ORACLE_SID = [oracle] ? The Oracle base has been set to /u01/app/oracle 

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

[oracle@db-asm-1 ~]$ cd $ORACLE_HOME 
[oracle@db-asm-1 db1]$ mv -v OPatch OPatch.old 
'OPatch' -> 'OPatch.old'

[oracle@db-asm-1 db1]$ mkdir OPatch
[oracle@db-asm-1 db1]$ ls -ld OP*
drwxr-xr-x. 2 oracle oinstall 4096 Oct  8 17:53 OPatch
drwxr-xr-x. 8 oracle oinstall 4096 Oct  8 17:03 OPatch.old

[oracle@db-asm-1 db1]$ unzip -d $ORACLE_HOME -qo /media/swrepo/JUL2017PSU/p6880880_112000_Linux-x86-64.zip;echo $?
0
[oracle@db-asm-1 db1]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 11.2.0.3.16

OPatch succeeded.

[oracle@db-asm-1 db1]$ su -
Password:
[root@db-asm-1 ~]# . oraenv <<< +ASM 
ORACLE_SID = [root] ? The Oracle base has been set to /u01/app/oracle 

[root@db-asm-1 ~]# echo $ORACLE_HOME 
/u01/app/oracle/11.2.0.4/grid 
[root@db-asm-1 ~]# export GRID_HOME=$ORACLE_HOME 
[root@db-asm-1 ~]# cd $GRID_HOME 
[root@db-asm-1 grid]# ls -ld OP* 
drwxr-xr-x. 8 oracle oinstall 4096 Oct 8 16:41 OPatch 
[root@db-asm-1 grid]# mv -v OPatch OPatch.old 
'OPatch' -> 'OPatch.old'

[root@db-asm-1 grid]# mkdir OPatch
[root@db-asm-1 grid]# ls -ld OP*
drwxr-xr-x. 2 root   root     4096 Oct  8 17:56 OPatch
drwxr-xr-x. 8 oracle oinstall 4096 Oct  8 16:41 OPatch.old

[root@db-asm-1 grid]# chown oracle:oinstall OPatch
[root@db-asm-1 grid]# ls -ld OP*
drwxr-xr-x. 2 oracle oinstall 4096 Oct  8 17:56 OPatch
drwxr-xr-x. 8 oracle oinstall 4096 Oct  8 16:41 OPatch.old
[root@db-asm-1 grid]# logout

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

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

[oracle@db-asm-1 db1]$ export GRID_HOME=$ORACLE_HOME
[oracle@db-asm-1 db1]$ unzip -d $GRID_HOME -qo /media/swrepo/JUL2017PSU/p6880880_112000_Linux-x86-64.zip;echo $?
0

[oracle@db-asm-1 db1]$ $GRID_HOME/OPatch/opatch version
OPatch Version: 11.2.0.3.16

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

Upgrade 12.2 Journey – Build Virtual 11.2.0.4 Oracle Restart

Sun, 2017-10-08 10:53

I am using oravirt vagrant boxes.

https://github.com/oravirt/vagrantfile
https://github.com/oravirt/vagrant-vbox-si-asm
https://github.com/oravirt/vagrantfile#environment-variables-that-can-be-used-to-override-defaults

Starting the VM’s
vagrant up will build the VM’s to the specification in hosts.yml and run the base provisioner
If there are extra provisioners defined you need one of the following:
setup=true vagrant up (if VM’s are not created)
setup=true vagrant provision (if the VM’s are already up)

setup=true giver=11.2.0.4 dbver=11.2.0.4 cdb=false vagrant up
setup=true vagrant up
setup=true giver=11.2.0.4 dbver=11.2.0.4 cdb=false vagrant provision

Modifications made for shared folders.

vagrant-vbox-si-asmF:\Vagrant\vagrant-vbox-si-asm\hosts.yml
synced_folders:
#- {src: swrepo, dest: /media/swrepo}
- {src: "F:\\OracleSoftware", dest: /sf_OracleSoftware}
- {src: "C:\\dinh\\Dropbox\\working", dest: /sf_working}
- {src: "F:\\OracleSoftware\\11.2.0.4_PatchSet_Linux_x64", dest: /media/swrepo}

F:\Vagrant\vagrant-vbox-si-asm\extra-provision\ansible-oracle\group_vars\vbox-si-asm
oracle_stage_remote: /sf_OracleSoftware
oracle_stage_remote: /sf_working
oracle_stage_remote: /media/swrepo

Environment shared folders.

[vagrant@db-asm-1 ~]$ ps -ef|grep pmon
oracle    1413     1  0 17:18 ?        00:00:00 ora_pmon_orclcdb
vagrant   2435  2417  0 17:19 pts/0    00:00:00 grep pmon
oracle   24615     1  0 16:47 ?        00:00:00 asm_pmon_+ASM
[vagrant@db-asm-1 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              47G  2.3G   43G   6% /
tmpfs                 873M   83M  790M  10% /dev/shm
vagrant               3.7T  172G  3.5T   5% /vagrant
sf_working            420G  133G  287G  32% /sf_working
media_swrepo          3.7T  172G  3.5T   5% /media/swrepo
sf_OracleSoftware     3.7T  172G  3.5T   5% /sf_OracleSoftware
/dev/mapper/vgora-lvora
                       84G   12G   68G  16% /u01

[vagrant@db-asm-1 ~]$ cd /media/swrepo/
[vagrant@db-asm-1 swrepo]$ ls -l
total 4305238
drwxrwxrwx. 1 vagrant vagrant       4096 Jul 20  2016 client
drwxrwxrwx. 1 vagrant vagrant       4096 Jul 20  2016 database
drwxrwxrwx. 1 vagrant vagrant       4096 Jul 20  2016 grid
drwxrwxrwx. 1 vagrant vagrant       4096 Oct  7 15:46 JUL2017PSU
-rwxrwxrwx. 1 vagrant vagrant 1395582860 Feb 11  2014 p13390677_112040_Linux-x86-64_1of7.zip
-rwxrwxrwx. 1 vagrant vagrant 1151304589 Feb 11  2014 p13390677_112040_Linux-x86-64_2of7.zip
-rwxrwxrwx. 1 vagrant vagrant 1205251894 Apr 25  2016 p13390677_112040_Linux-x86-64_3of7.zip
-rwxrwxrwx. 1 vagrant vagrant  656026876 May 12  2014 p13390677_112040_Linux-x86-64_4of7.zip
-rwxrwxrwx. 1 vagrant vagrant     297405 Jul 17  2016 README for 13390677.pdf
-rwxrwxrwx. 1 vagrant vagrant      82385 Jul 17  2016 View Patch Digest.pdf
[vagrant@db-asm-1 swrepo]$

[vagrant@db-asm-1 swrepo]$ cd /sf_OracleSoftware/12.2.0.1_Linux_64
[vagrant@db-asm-1 12.2.0.1_Linux_64]$ ls -l
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
[vagrant@db-asm-1 12.2.0.1_Linux_64]$

Install Log for vagrant-vbox-si-asm


Playing with asmcmd

Sat, 2017-10-07 21:29

ASMCMD Disk Group Management Commands (11.2)

asmcmd -V
asmcmd version 11.2.0.4.0

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/

asmcmd lsdsk
Path
/dev/oracle/crs01
/dev/oracle/data01
/dev/oracle/data02
/dev/oracle/fra01
/dev/oracle/fra02
/dev/oracle/fra03

asmcmd lsattr -G DATA -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         11.2.0.4.0
content.type             data
disk_repair_time         3.6h
idp.boundary             auto
idp.type                 dynamic
sector_size              512

asmcmd lsattr -G FRA -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         11.2.0.4.0
content.type             data
disk_repair_time         3.6h
idp.boundary             auto
idp.type                 dynamic
sector_size              512

asmcmd setattr -G FRA compatible.asm 11.2.0.4
asmcmd setattr -G FRA compatible.rdbms 11.2.0.4

asmcmd spget
+CRS/asm/asmparameterfile/registry.253.956737501

asmcmd iostat -G DATA -t
Group_Name  Dsk_Name   Reads      Writes     Read_Time   Write_Time
DATA        DATA_0000  253212160  101387776  260.330309  13325.418821
DATA        DATA_0001  178841088  27744256   209.972386  152.957184

--io Displays information in number of I/Os, instead of bytes.
asmcmd iostat -G DATA -t --io
Group_Name  Dsk_Name   Reads  Writes  Read_Time   Write_Time
DATA        DATA_0000  16354  8570    260.792741  13331.596792
DATA        DATA_0001  11496  1650    209.973173  152.957184

asmcmd iostat -G DATA -t --io --region
Group_Name  Dsk_Name   Reads  Writes  Cold_Reads  Cold_Writes  Hot_Reads  Hot_Writes  Read_Time   Write_Time
DATA        DATA_0000  16357  8572    16315       5028         0          0           260.793019  13334.818434
DATA        DATA_0001  11497  1650    11436       1645         0          0           209.973268  152.957184

asmcmd lsof -G DATA
DB_Name  Instance_Name  Path
orclcdb  orclcdb        +data/orclcdb/controlfile/current.260.956739025
orclcdb  orclcdb        +data/orclcdb/datafile/sysaux.257.956738871
orclcdb  orclcdb        +data/orclcdb/datafile/system.256.956738871
orclcdb  orclcdb        +data/orclcdb/datafile/undotbs1.258.956738873
orclcdb  orclcdb        +data/orclcdb/datafile/users.259.956738873
orclcdb  orclcdb        +data/orclcdb/onlinelog/group_1.261.956739029
orclcdb  orclcdb        +data/orclcdb/onlinelog/group_2.262.956739055
orclcdb  orclcdb        +data/orclcdb/onlinelog/group_3.263.956739083
orclcdb  orclcdb        +data/orclcdb/tempfile/temp.264.956739127

oracle@db-asm-1:+ASM:/home/oracle
> ps -ef|grep pmon
oracle   15831     1  0 02:22 ?        00:00:00 asm_pmon_+ASM
oracle   16030     1  0 02:22 ?        00:00:00 ora_pmon_orclcdb
oracle   16330 11101  0 02:24 pts/3    00:00:00 grep pmon

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

oracle@db-asm-1:orclcdb:/home/oracle 
> srvctl config database
orclcdb

oracle@db-asm-1:orclcdb:/home/oracle
> srvctl stop database -d orclcdb -o immediate

oracle@db-asm-1:orclcdb:/home/oracle
> ps -ef|grep pmon
oracle   15831     1  0 02:22 ?        00:00:00 asm_pmon_+ASM
oracle   16695 11101  0 02:25 pts/3    00:00:00 grep pmon

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

oracle@db-asm-1:+ASM:/home/oracle 
> asmcmd lsof -G DATA
DB_Name  Instance_Name  Path
oracle@db-asm-1:+ASM:/home/oracle
>

SQLcl comes default with all DB/Client installs 12.2

Mon, 2017-10-02 08:01

I had tweeted, “Wouldn’t it be nice for SQL*Plus have auto size for columns vs having to manually configure each? Larry must have not used Excel before?”

I learned, SQL*Plus does not have this functionality but SQLcl does.

Nice video and demo from SQL Developer Command Line SQLcl 

Here are the tweets

 

 


Mining Goldgate ggserr.log

Fri, 2017-09-29 21:33

Can you imagine me running in circles shouting, “The sky is falling, the sky is falling?”

Replicat Lag at Chkpt: 03:21:45

Here are the trail files at target – look at how fast it is being created.

ls -alrt ./dirdat/aa*|tail -20
-rw-r----- 1 ggsuser ggsuser 499999845 Sep 29 11:09 ./dirdat/aa000020827
-rw-r----- 1 ggsuser ggsuser 499999575 Sep 29 11:09 ./dirdat/aa000020828
-rw-r----- 1 ggsuser ggsuser 499999929 Sep 29 11:10 ./dirdat/aa000020829
-rw-r----- 1 ggsuser ggsuser 499999771 Sep 29 11:11 ./dirdat/aa000020830
-rw-r----- 1 ggsuser ggsuser 499999941 Sep 29 11:11 ./dirdat/aa000020831
-rw-r----- 1 ggsuser ggsuser 499999858 Sep 29 11:12 ./dirdat/aa000020832
-rw-r----- 1 ggsuser ggsuser 499999571 Sep 29 11:12 ./dirdat/aa000020833
-rw-r----- 1 ggsuser ggsuser 499999874 Sep 29 11:13 ./dirdat/aa000020834
-rw-r----- 1 ggsuser ggsuser 499999782 Sep 29 11:14 ./dirdat/aa000020835
-rw-r----- 1 ggsuser ggsuser 499999975 Sep 29 11:14 ./dirdat/aa000020836

My hypothesis: lots of data being capture at source.

After all is said and done. The ggserr.log was mined.

Gather dates for the 10 highest number of trails created by day in 2017.

SOURCE:

grep "^2017" ggserr.log.dinh|grep "p_test.prm:  Rolling over remote file"|awk '{ print $1 }'|uniq -c|sort -nrk 1|head

    224 2017-09-29
    147 2017-06-02
    105 2017-02-28
    101 2017-05-31
    100 2017-03-01
     98 2017-06-01
     97 2017-05-18
     91 2017-05-26
     89 2017-07-25
     85 2017-01-26

TARGET:


grep "^2017" ggserr.log.dinh|grep "r_test.prm:  Switching to next trail file"|awk '{ print $1 }'|uniq -c|sort -nrk 1|head

    279 2017-09-29
    183 2017-02-28
    174 2017-03-01
    148 2017-06-02
    146 2017-02-24
    137 2017-08-29
    137 2017-03-24
    133 2017-08-11
    130 2017-08-16
    128 2017-03-02

Different count between source/target for 2017-09-29 is due to data being collected at different time.


Scheduler Jobs Do Not Run Automatically

Thu, 2017-09-28 21:09

After you have followed – IF: Jobs Do Not Run Automatically (Doc ID 2084527.1) – without any success,
then check to see if services have been created and are running.

RAC DB is 12.1.0.2.0 and was cloned from standby.

It just so happens, service as defined from the SQL below was not created:

select c.SERVICE
from dba_scheduler_jobs j, dba_scheduler_job_classes c
where j.JOB_CLASS=c.JOB_CLASS_NAME
and j.JOB_NAME=UPPER('&jobname')
;

To be honest, I was not able to find the issue and team mate did.

What I found very, very strange is manually running the job using exec dbms_scheduler.run_job is successful.

The manual job ran successfully without the service created and on the wrong node for where the service is defined
(the service is defined to run on node 2, while the manual run is from node 1).

Another unsolved mystery.


Dedicated Network for DataGuard

Tue, 2017-09-26 07:50

Just some notes.

Using a separate network for DataGuard in 12c RAC
https://dbamarco.wordpress.com/2015/04/15/using-a-separate-network-for-dataguard-in-12c-rac/

PUTTING DATA GUARD TRAFFIC ON DEDICATED NETWORK INTERFACE
https://ilmarkerm.eu/blog/2015/07/putting-data-guard-traffic-on-dedicated-network-interface/

How configure Multiples Public Network in a Grid Infrastructure 11g R2 (11.2) environment
https://levipereira.wordpress.com/2011/10/22/how-configure-multiples-public-network-an-grid-infrastructure-11g-r2-11-2-environment/


Grid Infrastructure 12.2.0.1 CRS_SWONLY Silent Install

Sat, 2017-09-23 18:00

Configuring Software Binaries for Oracle Grid Infrastructure for a Standalone Server

CREATE DIRECTORIES:

[root@arrow1 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),994(vboxsf)

[root@arrow1 ~]# mkdir /u01
[root@arrow1 ~]# chmod 775 /u01/
[root@arrow1 ~]# chown oracle:oinstall /u01/

[root@arrow1 ~]# vi /etc/oraInst.loc
[root@arrow1 ~]# chown oracle:oinstall /etc/oraInst.loc

[root@arrow1 ~]# cat /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall

[root@arrow1 ~]# ll /etc/oraInst.loc
-rw-r--r-- 1 oracle oinstall 56 Sep 23 17:11 /etc/oraInst.loc
[root@arrow1 ~]# exit

[oracle@arrow1 ~]$ mkdir -p /u01/app/12.2.0.1/grid
[oracle@arrow1 ~]$ mkdir -p /u01/app/grid
[oracle@arrow1 ~]$ mkdir -p /u01/app/oraInventory

EXTRACT SOFTWARE DIRECTLY TO GRID HOME:

[oracle@arrow1 ~]$ ls -l /sf_OracleSoftware/12.2.0.1_Linux_64/
total 6297251
-rwxrwxrwx 1 vagrant vagrant 3453696911 Apr 12 09:44 linuxx64_12201_database.zip
-rwxrwxrwx 1 vagrant vagrant 2994687209 Apr 12 09:43 linuxx64_12201_grid_home.zip

[oracle@arrow1 ~]$ 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@arrow1 ~]$ ls /u01/app/12.2.0.1/grid
addnode     crs     deinstall    gpnp           inventory  lib      opmn     oui      qos       rootupgrade.sh  srvm      welcome.html
assistants  css     demo         gridSetup.sh   javavm     log      oracore  owm      racg      runcluvfy.sh    suptools  wlm
bin         cv      diagnostics  has            jdbc       md       ord      perl     rdbms     scheduler       tomcat    wwg
cdata       dbjava  dmu          hs             jdk        network  ordim    plsql    relnotes  slax            ucp       xag
cha         dbs     env.ora      install        jlib       nls      ords     precomp  rhp       sqlpatch        usm       xdk
clone       dc_ocm  evm          instantclient  ldap       OPatch   oss      QOpatch  root.sh   sqlplus         utl

[oracle@arrow1 ~]$ ls /u01/app/12.2.0.1/grid/install/response/
grid_2017-01-26_04-10-28PM.rsp  gridsetup.rsp  sample.ccf
[oracle@arrow1 ~]$

CREATE RESPONSE FILE FOR CRS_SWONLY – WITHOUT ASM:

oracle@arrow1::/u01/app/12.2.0.1/grid/install/response
$ cp -v gridsetup.rsp grid_crs_swonly.rsp
‘gridsetup.rsp’ -> ‘grid_crs_swonly.rsp’

oracle@arrow1::/u01/app/12.2.0.1/grid/install/response
$ diff -iwyB --suppress-common-lines -W 150 gridsetup.rsp grid_crs_swonly.rsp; echo
oracle.install.option=                                                    |     oracle.install.option=CRS_SWONLY
ORACLE_BASE=                                                              |     ORACLE_BASE=/u01/app/grid
oracle.install.asm.OSDBA=                                                 |     oracle.install.asm.OSDBA=dba
oracle.install.asm.OSOPER=                                                |     oracle.install.asm.OSOPER=dba
oracle.install.asm.OSASM=                                                 |     oracle.install.asm.OSASM=dba
oracle@arrow1::/u01/app/12.2.0.1/grid/install/response

RUNCLUVFY FOR HACFG:

oracle@arrow1::/u01/app/12.2.0.1/grid
$ ./runcluvfy.sh stage -pre hacfg

Verifying Physical Memory ...FAILED (PRVF-7530)
Verifying Available Physical Memory ...PASSED
Verifying Swap Size ...PASSED
Verifying Free Space: arrow1:/usr,arrow1:/var,arrow1:/etc,arrow1:/sbin,arrow1:/tmp ...PASSED
Verifying User Existence: oracle ...
  Verifying Users With Same UID: 54321 ...PASSED
Verifying User Existence: oracle ...PASSED
Verifying Group Existence: dba ...PASSED
Verifying Group Existence: oinstall ...PASSED
Verifying Group Membership: oinstall(Primary) ...PASSED
Verifying Group Membership: dba ...PASSED
Verifying Run Level ...PASSED
Verifying Hard Limit: maximum open file descriptors ...PASSED
Verifying Soft Limit: maximum open file descriptors ...PASSED
Verifying Hard Limit: maximum user processes ...PASSED
Verifying Soft Limit: maximum user processes ...PASSED
Verifying Soft Limit: maximum stack size ...PASSED
Verifying Architecture ...PASSED
Verifying OS Kernel Version ...PASSED
Verifying OS Kernel Parameter: semmsl ...PASSED
Verifying OS Kernel Parameter: semmns ...PASSED
Verifying OS Kernel Parameter: semopm ...PASSED
Verifying OS Kernel Parameter: semmni ...PASSED
Verifying OS Kernel Parameter: shmmax ...PASSED
Verifying OS Kernel Parameter: shmmni ...PASSED
Verifying OS Kernel Parameter: shmall ...PASSED
Verifying OS Kernel Parameter: file-max ...PASSED
Verifying OS Kernel Parameter: ip_local_port_range ...PASSED
Verifying OS Kernel Parameter: rmem_default ...PASSED
Verifying OS Kernel Parameter: rmem_max ...PASSED
Verifying OS Kernel Parameter: wmem_default ...PASSED
Verifying OS Kernel Parameter: wmem_max ...PASSED
Verifying OS Kernel Parameter: aio-max-nr ...PASSED
Verifying OS Kernel Parameter: panic_on_oops ...PASSED
Verifying Package: binutils-2.23.52.0.1 ...PASSED
Verifying Package: compat-libcap1-1.10 ...PASSED
Verifying Package: libgcc-4.8.2 (x86_64) ...PASSED
Verifying Package: libstdc++-4.8.2 (x86_64) ...PASSED
Verifying Package: libstdc++-devel-4.8.2 (x86_64) ...PASSED
Verifying Package: sysstat-10.1.5 ...PASSED
Verifying Package: ksh ...PASSED
Verifying Package: make-3.82 ...PASSED
Verifying Package: glibc-2.17 (x86_64) ...PASSED
Verifying Package: glibc-devel-2.17 (x86_64) ...PASSED
Verifying Package: libaio-0.3.109 (x86_64) ...PASSED
Verifying Package: libaio-devel-0.3.109 (x86_64) ...PASSED
Verifying Package: nfs-utils-1.2.3-15 ...PASSED
Verifying Package: smartmontools-6.2-4 ...PASSED
Verifying Package: net-tools-2.0-0.17 ...PASSED
Verifying Users With Same UID: 0 ...PASSED
Verifying Current Group ID ...PASSED
Verifying Root user consistency ...PASSED

Pre-check for Oracle Restart configuration was unsuccessful.


Failures were encountered during execution of CVU verification request "stage -pre hacfg".

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


CVU operation performed:      stage -pre hacfg
Date:                         Sep 23, 2017 5:22:21 PM
CVU home:                     /u01/app/12.2.0.1/grid/
User:                         oracle
oracle@arrow1::/u01/app/12.2.0.1/grid
$

INSTALL GRID USING gridSetup.sh

oracle@arrow1::/u01/app/12.2.0.1/grid
$ ./gridSetup.sh -silent -waitforcompletion -skipPrereqs -responseFile /u01/app/12.2.0.1/grid/install/response/grid_crs_swonly.rsp
Launching Oracle Grid Infrastructure Setup Wizard...

[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-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.
You can find the log of this install session at:
 /u01/app/oraInventory/logs/GridSetupActions2017-09-23_05-52-14PM/gridSetupActions2017-09-23_05-52-14PM.log

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

Execute /u01/app/12.2.0.1/grid/root.sh on the following nodes:
[arrow1]


Successfully Setup Software.
oracle@arrow1::/u01/app/12.2.0.1/grid
$

RUN root.sh

[root@arrow1 ~]# /u01/app/12.2.0.1/grid/root.sh
Check /u01/app/12.2.0.1/grid/install/root_arrow1_2017-09-23_17-54-24-226719765.log for the output of root script

[root@arrow1 ~]# cat /u01/app/12.2.0.1/grid/install/root_arrow1_2017-09-23_17-54-24-226719765.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.

To configure Grid Infrastructure for a Cluster or Grid Infrastructure for a Stand-Alone Server execute the following command as oracle user:
/u01/app/12.2.0.1/grid/gridSetup.sh
This command launches the Grid Infrastructure Setup Wizard. The wizard also supports silent operation, and the parameters can be passed through the response file that is available in the installation media.

[root@arrow1 ~]#

This is where Oracle has take a turn for the worse.
Instructions are not provided after running root.sh and are incorrect!

AS ROOT RUN roothas.pl

[root@arrow1 ~]# /u01/app/12.2.0.1/grid/perl/bin/perl -I /u01/app/12.2.0.1/grid/perl/lib -I /u01/app/12.2.0.1/grid/crs/install /u01/app/12.2.0.1/grid/crs/install/roothas.pl
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/grid/crsdata/arrow1/crsconfig/roothas_2017-09-23_06-11-34PM.log
2017/09/23 18:11:35 CLSRSC-363: User ignored prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node arrow1 successfully pinned.
2017/09/23 18:11:44 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'arrow1'
CRS-2673: Attempting to stop 'ora.evmd' on 'arrow1'
CRS-2677: Stop of 'ora.evmd' on 'arrow1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'arrow1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.

arrow1     2017/09/23 18:12:54     /u01/app/12.2.0.1/grid/cdata/arrow1/backup_20170923_181254.olr     0
2017/09/23 18:12:54 CLSRSC-327: Successfully configured Oracle Restart for a standalone server
[root@arrow1 ~]#

AS ORACLE runInstaller -updateNodeList

oracle@arrow1::/u01/app/12.2.0.1/grid
$ cd oui/bin/

oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$ ./runInstaller -updateNodeList ORACLE_HOME=/u01/app/12.2.0.1/grid -defaultHomeName CLUSTER_NODES= CRS=TRUE
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4080 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'UpdateNodeList' was successful.
oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin

CHECK PROCESESS

oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$ ps -ef|grep oracle
root      3796  1125  0 17:03 ?        00:00:00 sshd: oracle [priv]
oracle    3798  3796  0 17:03 ?        00:00:01 sshd: oracle@pts/2
oracle    3799  3798  0 17:03 pts/2    00:00:00 -bash
root     13572  1125  0 17:39 ?        00:00:00 sshd: oracle [priv]
oracle   13574 13572  0 17:39 ?        00:00:00 sshd: oracle@pts/0
oracle   13575 13574  0 17:39 pts/0    00:00:00 -bash
oracle   25084     1  0 18:12 ?        00:00:03 /u01/app/12.2.0.1/grid/bin/ohasd.bin reboot
oracle   25197     1  0 18:12 ?        00:00:02 /u01/app/12.2.0.1/grid/bin/oraagent.bin
oracle   25215     1  0 18:12 ?        00:00:01 /u01/app/12.2.0.1/grid/bin/evmd.bin
oracle   25269 25215  0 18:12 ?        00:00:01 /u01/app/12.2.0.1/grid/bin/evmlogger.bin -o /u01/app/12.2.0.1/grid/log/[HOSTNAME]/evmd/evmlogger.info -l /u01/app/12.2.0.1/grid/log/[HOSTNAME]/evmd/evmlogger.log
oracle   25404  3799  0 18:27 pts/2    00:00:00 ps -ef
oracle   25405  3799  0 18:27 pts/2    00:00:00 grep --color=auto oracle
oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$

CHECK INSTALL LOG

oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$ export LOG=/u01/app/oraInventory/logs/GridSetupActions2017-09-23_05-52-14PM/gridSetupActions2017-09-23_05-52-14PM.log

oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$ grep -e '[[:upper:]]: ' $LOG |cut -d ":" -f1|sort -u
   ACTION
   CAUSE
INFO
WARNING

oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$ grep -e '[[:upper:]]: ' $LOG|grep "^WARNING: "
WARNING:  [Sep 23, 2017 5:52:18 PM] Unable to find the namespace URI. Reason: Start of root element expected.
WARNING:  [Sep 23, 2017 5:52:18 PM] Unable to find the namespace URI. Reason: Start of root element expected.
WARNING:  [Sep 23, 2017 5:52:21 PM] Validation disabled for the state init
WARNING:  [Sep 23, 2017 5:52:26 PM] Validation disabled for the state flowDecider
WARNING:  [Sep 23, 2017 5:52:26 PM] Validation disabled for the state CRSNodeInformationUI
WARNING:  [Sep 23, 2017 5:52:26 PM] No Local Domain found, setting to whatever first domain is available
WARNING:  [Sep 23, 2017 5:52:26 PM] No Domain found, skipping Same Domain validation
WARNING:  [Sep 23, 2017 5:52:27 PM] [WARNING] [INS-41808] Possible invalid choice for OSASM Group.
WARNING:  [Sep 23, 2017 5:52:27 PM] [WARNING] [INS-41809] Possible invalid choice for OSDBA Group.
WARNING:  [Sep 23, 2017 5:52:27 PM] [WARNING] [INS-41810] Possible invalid choice for OSOPER Group.
WARNING:  [Sep 23, 2017 5:52:27 PM] [WARNING] [INS-41813] OSDBA for ASM, OSOPER for ASM, and OSASM are the same OS group.
WARNING:  [Sep 23, 2017 5:52:29 PM] Validation disabled for the state prereqExecutionDecider
WARNING:  [Sep 23, 2017 5:52:29 PM] Validation disabled for the state showSummary
WARNING:  [Sep 23, 2017 5:52:29 PM] Unable to find the namespace URI. Reason: /u01/app/12.2.0.1/grid/install/response/grid_2017-09-23_05-52-14PM.rsp (No such file or directory)
WARNING:  [Sep 23, 2017 5:53:50 PM] Validation disabled for the state finish
WARNING:  [Sep 23, 2017 5:53:50 PM] Could not create directory: /u01/app/oraInventory/logs/GridSetupActions2017-09-23_05-52-14PM
oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$

Bad Practice from Oracle – datapatch

Sat, 2017-09-23 08:18

Not too long ago, I discovered:
dbca does hard coding

Now it gets even worse.

Create TEST environment.

SOURCE ORACLE_HOME:/u01/app/oracle/product/12.1.0/db_1
TARGET ORACLE_HOME:/u01/app/oracle/product/12.1.0/db

DB was cloned from Source to Target.

datapatch failed due to different OH and hard coding from dba_directories as shown below.

SOURCE:
OPATCH_LOG_DIR /u01/app/oracle/product/12.1.0/db_1/QOpatch
OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.1.0/db_1/QOpatch
OPATCH_INST_DIR /u01/app/oracle/product/12.1.0/db_1/OPatch

TARGET:
OPATCH_LOG_DIR /u01/app/oracle/product/12.1.0/db/QOpatch
OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.1.0/db/QOpatch
OPATCH_INST_DIR /u01/app/oracle/product/12.1.0/db/OPatch

I don’t get it! Why not use relative versus absolute path?

You might ask, “Why is the target different!”

To be honest, in this specific case, it should not have been.

BUT there may be scenarios where OH is already used and new location, i.e. db_2, is required.

Looking forward to Oracle AI – sarcasm.

 


RMAN Backup from Standby w Recovery Catalog Part 2

Wed, 2017-09-20 18:15

RMAN Backup from Standby w Recovery Catalog

What! There’s a part 2?

FRA from primary was getting full since archivelog deletion was not working.

  1. Why is there a need to delete archivelog since FRA performs clean up?
  2. If FRA performs clean up then why is the destination full?
  3. Go to 1.

Reminds me of, “Who’s on first, What’s on second, I Don’t Know is on third”

Notice the old configuration.

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
Archivelog deletion.
connect target;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
list archivelog all;
delete noprompt archivelog until time 'sysdate-7/24';
list archivelog all;
Archivelog deleted.

archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31831.359.955164907 RECID=87186 STAMP=955164906
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31832.886.955166705 RECID=87189 STAMP=955166705
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31833.594.955168505 RECID=87191 STAMP=955168504
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31834.411.955170305 RECID=87193 STAMP=955170304
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31835.418.955172107 RECID=87195 STAMP=955172106
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31836.448.955173905 RECID=87197 STAMP=955173906
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31837.438.955175705 RECID=87199 STAMP=955175705
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31838.811.955176699 RECID=87201 STAMP=955176703
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31839.922.955178497 RECID=87203 STAMP=955178499
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31840.468.955180295 RECID=87205 STAMP=955180294
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31841.910.955182097 RECID=87207 STAMP=955182097
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31842.830.955183897 RECID=87209 STAMP=955183897
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31843.986.955185697 RECID=87211 STAMP=955185696
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31844.879.955186507 RECID=87212 STAMP=955186506
Deleted 14 objects
OH SH*T
RMAN> connect target;

connected to target database: QA (DBID=3476258591)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> list backup of archivelog from logseq=31831 until logseq=31844 thread=1 summary;

specification does not match any backup in the repository
Sign of JOY!
RMAN> list backup of archivelog from logseq=31831 until logseq=31844 thread=1 summary for db_unique_name all;


specification does not match any backup in the repository

List of Backups for database with db_unique_name QADR
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
812913  B  A  A DISK        2017-09-20:05:43:11 1       1       YES        DAILY_ARCHLOG_BKUP
812915  B  A  A DISK        2017-09-20:05:43:30 1       1       YES        DAILY_ARCHLOG_BKUP
813550  B  A  A DISK        2017-09-20:09:36:44 1       1       YES        DAILY_ARCHLOG_BKUP
813551  B  A  A DISK        2017-09-20:09:37:55 1       1       YES        DAILY_ARCHLOG_BKUP
813559  B  A  A DISK        2017-09-20:09:41:33 1       1       YES        DAILY_ARCHLOG_BKUP
813570  B  A  A DISK        2017-09-20:09:45:17 1       1       YES        DAILY_ARCHLOG_BKUP
814104  B  A  A DISK        2017-09-20:13:36:44 1       1       YES        DAILY_ARCHLOG_BKUP
814105  B  A  A DISK        2017-09-20:13:37:55 1       1       YES        DAILY_ARCHLOG_BKUP
814112  B  A  A DISK        2017-09-20:13:41:05 1       1       YES        DAILY_ARCHLOG_BKUP
814114  B  A  A DISK        2017-09-20:13:41:45 1       1       YES        DAILY_ARCHLOG_BKUP

RMAN> show all;

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Wondering if
CONFIGURE DEFAULT DEVICE TYPE TO DISK
had anything to do with
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO ‘SBT_TAPE’ APPLIED ON ALL STANDBY
not working?

You might ask, why not just use “CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY” only.

That depends if you want accuracy or not.

Was the requirement backup to tape or to disk?

If the requirement is backup to tape, feel lucky FRA got full; otherwise, there’s a false pretense of good backup going to tape.

 


RMAN Backup from Standby w Recovery Catalog

Tue, 2017-09-19 20:05

Apologies as this is not a very clean post as there is too much info to disseminate.

Hopefully, the info may be useful at some point.

Try connect using connect target sys/password@tns vs connect connect target;

Otherwise, RMAN> resync catalog from db_unique_name all will fail or get the error below.

RMAN-06820: WARNING: failed to archive current log at primary database

When using RMAN recovery catalog, “from db_unique_name and for db_unique_name” are introduced.
Either specify the required db_unique_name or use ALL.

References:

RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured (Doc ID 1598653.1) 
List backup on Standby database returns no data in a Dataguard Configuration (Doc ID 1382885.1) 
ORA-17629 : RMAN Resync Catalog from db_unique_name all fails (Doc ID 1301769.1) 
RESYNC CATALOG FROM DB_UNIQUE_NAME ALL fails ORA-17629, ORA-17628 (Doc ID 1327156.1)

DEMO:

$ rman

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 18 15:26:30 2017

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

RMAN> connect target sys@qa

target database Password:
connected to target database: QA (DBID=147966131)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> resync catalog from db_unique_name all;


starting full resync of recovery catalog
full resync complete

resyncing from database with DB_UNIQUE_NAME QADR

RMAN> show all for db_unique_name all;


RMAN configuration parameters for database with db_unique_name QA are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qa/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backups/rman/qa/%U' MAXPIECEZE 20 G;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' MAXPIECEZE 5 G;
CONFIGURE MAXSETZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESON ALGORITHM 'BAC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'QA' CONNECT IDENTIFIER  'QA';
CONFIGURE DB_UNIQUE_NAME 'QADR' CONNECT IDENTIFIER  'QADR';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/11g/dbs/snapcf_qa.f'; # default

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qadr/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backups/rman/qadr/%U' MAXPIECEZE 32 G;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' MAXPIECEZE 32 G;
CONFIGURE MAXSETZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESON ALGORITHM 'BAC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'QA' CONNECT IDENTIFIER  'QA';
CONFIGURE DB_UNIQUE_NAME 'QADR' CONNECT IDENTIFIER  'QADR';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/11g/dbs/snapcf_qa.f'; # default

RMAN> exit

ALL controlfile autobackup going to disk, will probably needs further investigation.

$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 18 20:08:27 2017

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

RMAN> connect target;

connected to target database: QA (DBID=3476258591)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> list backup of controlfile summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
787503  B  F  A SBT_TAPE    2017-09-14:17:10:41 1       1       NO         TAG20170914T171041

RMAN> list backupset 787503;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
787503  Full    13.75M     SBT_TAPE    00:00:00     2017-09-14:17:10:41
        BP Key: 787508   Status: AVAILABLE  Compressed: NO  Tag: TAG20170914T171041
        Handle: c-3476258591-20170914-05   Media: 758343
  Control File Included: Ckp SCN: 9869799255   Ckp time: 2017-09-14:17:10:41
  SPFILE Included: Modification time: 2017-08-16:17:19:20

RMAN> list backup of controlfile summary for db_unique_name all;


List of Backups for database with db_unique_name QA
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
787503  B  F  A SBT_TAPE    2017-09-14:17:10:41 1       1       NO         TAG20170914T171041

List of Backups for database with db_unique_name QADR
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
795620  B  F  A DISK        2017-09-16:20:12:59 1       1       NO         TAG20170916T201259
796112  B  F  A DISK        2017-09-16:21:35:35 1       1       NO         TAG20170916T213535
796981  B  F  A DISK        2017-09-17:01:38:27 1       1       NO         TAG20170917T013827
798063  B  F  A DISK        2017-09-17:05:38:42 1       1       NO         TAG20170917T053841
799073  B  F  A DISK        2017-09-17:09:40:17 1       1       NO         TAG20170917T094016
800391  B  F  A DISK        2017-09-17:13:40:52 1       1       NO         TAG20170917T134052
801182  B  F  A DISK        2017-09-17:17:41:18 1       1       NO         TAG20170917T174118
801914  B  F  A DISK        2017-09-17:19:55:17 1       1       NO         TAG20170917T195517
802532  B  F  A DISK        2017-09-17:21:35:26 1       1       NO         TAG20170917T213526
803350  B  F  A DISK        2017-09-18:01:35:55 1       1       NO         TAG20170918T013555
804191  B  F  A DISK        2017-09-18:05:36:21 1       1       NO         TAG20170918T053620
805093  B  F  A DISK        2017-09-18:09:37:49 1       1       NO         TAG20170918T093749
805935  B  F  A DISK        2017-09-18:13:38:14 1       1       NO         TAG20170918T133814
807696  B  F  A DISK        2017-09-18:17:38:31 1       1       NO         TAG20170918T173831
808143  B  F  A DISK        2017-09-18:19:51:45 1       1       NO         TAG20170918T195145

RMAN> list backupset 808143;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 09/18/2017 20:10:47
RMAN-06160: no backup pieces found for backup set key: 808143

RMAN> list backupset 808143 for db_unique_name QADR;

List of Backup Set for database with db_unique_name QADR
===================

--- Notice %d is db_name and not db_unique_name which is disappointing

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
808143  Full    13.80M     DISK        00:00:00     2017-09-18:19:51:45
        BP Key: 808152   Status: AVAILABLE  Compressed: NO  Tag: TAG20170918T195145
        Piece Name: /backups/rman/qadr/QA_3476258591_c-3476258591-20170918-05.ctl
  Standby Control File Included: Ckp SCN: 9902230362   Ckp time: 2017-09-18:19:45:07
  SPFILE Included: Modification time: 2017-09-16:22:12:41

RMAN> exit

When configurations are the same for primary and standby, do this:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME ALL

versus

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME 'QA';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME 'QADR';
RMAN> show CONTROLFILE AUTOBACKUP FORMAT for db_unique_name all;

RMAN configuration parameters for database with db_unique_name QA are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qa/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qadr/%d_%I_%F.ctl';

RMAN>

Thoughts on RMAN Backup Strategy Part 3

Mon, 2017-09-18 18:42

Thoughts on RMAN Backup Strategy Part 2

Thoughts on RMAN Backup Strategy

Linux Locking using flock

From cron:

00 03 * * 0          rman_db_backup.sh 0     > /tmp/rman_l0.log 2>&1
00 03 * * 1,2,3,4,5  rman_db_backup.sh 1     > /tmp/rman_l1.log 2>&1
30 * * * *           rman_archive_backup.sh  > /tmp/rman_arch.log 2>1

Notice from cron, DB backup starts at 3am and achivelog backup starts at 30m after the hour.

What happens when DB performs backup database plus archivelog delete input?

What happens when failed backup does not exits from shell script and continue with delete noprompt obsolete;?

Just a few thoughts:

  1. Use flock to prevent 2 backups for running at the same time.
  2. User error trapping to exit from shell script vs continuing to next step.
  3. Don’t delete archivelog from DB backup.
  4. Delete archivelog from ARC backup or schedule MW tasks.

 


Pages