Thinking Out Loud

August 6, 2019

19c Grid Dry-Run Upgrade

Filed under: 19c,awk_sed_grep,Grid Infrastructure,upgrade — mdinh @ 12:42 pm

First test using GUI.

[oracle@racnode-dc2-1 grid]$ /u01/app/19.3.0.0/grid/gridSetup.sh -dryRunForUpgrade
Launching Oracle Grid Infrastructure Setup Wizard...

The response file for this session can be found at:
 /u01/app/19.3.0.0/grid/install/response/grid_2019-08-06_00-20-31AM.rsp

You can find the log of this install session at:
 /u01/app/oraInventory/logs/GridSetupActions2019-08-06_00-20-31AM/gridSetupActions2019-08-06_00-20-31AM.log
[oracle@racnode-dc2-1 grid]$

Create dryRunForUpgradegrid.rsp from grid_2019-08-06_00-20-31AM.rsp (above GUI test)

[oracle@racnode-dc2-1 grid]$ grep -v "^#" /u01/app/19.3.0.0/grid/install/response/grid_2019-08-06_00-20-31AM.rsp | grep -v "=$" | awk 'NF' > /home/oracle/dryRunForUpgradegrid.rsp

[oracle@racnode-dc2-1 ~]$ cat /home/oracle/dryRunForUpgradegrid.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v19.0.0
INVENTORY_LOCATION=/u01/app/oraInventory
oracle.install.option=UPGRADE
ORACLE_BASE=/u01/app/oracle
oracle.install.crs.config.scanType=LOCAL_SCAN
oracle.install.crs.config.ClusterConfiguration=STANDALONE
oracle.install.crs.config.configureAsExtendedCluster=false
oracle.install.crs.config.clusterName=vbox-rac-dc2
oracle.install.crs.config.gpnp.configureGNS=false
oracle.install.crs.config.autoConfigureClusterNodeVIP=false
oracle.install.crs.config.gpnp.gnsOption=CREATE_NEW_GNS
oracle.install.crs.config.clusterNodes=racnode-dc2-1:,racnode-dc2-2:
oracle.install.crs.configureGIMR=true
oracle.install.asm.configureGIMRDataDG=false
oracle.install.crs.config.storageOption=FLEX_ASM_STORAGE
oracle.install.crs.config.sharedFileSystemStorage.ocrLocations=
oracle.install.crs.config.useIPMI=false
oracle.install.asm.diskGroup.name=CRS
oracle.install.asm.diskGroup.AUSize=0
oracle.install.asm.gimrDG.AUSize=1
oracle.install.asm.configureAFD=false
oracle.install.crs.configureRHPS=false
oracle.install.crs.config.ignoreDownNodes=false
oracle.install.config.managementOption=NONE
oracle.install.config.omsPort=0
oracle.install.crs.rootconfig.executeRootScript=false
[oracle@racnode-dc2-1 ~]$

Create directory grid home for all nodes:

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

[root@racnode-dc2-1 ~]# mkdir -p /u01/app/19.3.0.0/grid
[root@racnode-dc2-1 ~]# chown oracle:oinstall /u01/app/19.3.0.0/grid
[root@racnode-dc2-1 ~]# chmod 775 /u01/app/19.3.0.0/grid

[root@racnode-dc2-1 ~]# ll /u01/app/19.3.0.0/
total 4
drwxrwxr-x 2 oracle oinstall 4096 Aug  6 02:07 grid
[root@racnode-dc2-1 ~]#

Extract grid software for node1 ONLY:

[oracle@racnode-dc2-1 ~]$ unzip -qo /media/swrepo/LINUX.X64_193000_grid_home.zip -d /u01/app/19.3.0.0/grid/

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

[oracle@racnode-dc2-1 ~]$ du -sh /u01/app/19.3.0.0/grid/
6.0G    /u01/app/19.3.0.0/grid/
[oracle@racnode-dc2-1 ~]$

Run gridSetup.sh -silent -dryRunForUpgrade:

[oracle@racnode-dc2-1 ~]$ env|grep -i ora
USER=oracle
MAIL=/var/spool/mail/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin
PWD=/home/oracle
HOME=/home/oracle
LOGNAME=oracle

[oracle@racnode-dc2-1 ~]$ date
Tue Aug  6 02:35:47 CEST 2019

[oracle@racnode-dc2-1 ~]$ /u01/app/19.3.0.0/grid/gridSetup.sh -silent -dryRunForUpgrade -responseFile /home/oracle/dryRunForUpgradegrid.rsp
Launching Oracle Grid Infrastructure Setup Wizard...

[WARNING] [INS-13014] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/GridSetupActions2019-08-06_02-35-52AM/gridSetupActions2019-08-06_02-35-52AM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/GridSetupActions2019-08-06_02-35-52AM/gridSetupActions2019-08-06_02-35-52AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
 /u01/app/19.3.0.0/grid/install/response/grid_2019-08-06_02-35-52AM.rsp

You can find the log of this install session at:
 /u01/app/oraInventory/logs/GridSetupActions2019-08-06_02-35-52AM/gridSetupActions2019-08-06_02-35-52AM.log


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

Execute /u01/app/19.3.0.0/grid/rootupgrade.sh on the following nodes:
[racnode-dc2-1]

Run the script on the local node.

Successfully Setup Software with warning(s).
[oracle@racnode-dc2-1 ~]$

Run rootupgrade.sh for node1 ONLY and review log:

[root@racnode-dc2-1 ~]# /u01/app/19.3.0.0/grid/rootupgrade.sh
Check /u01/app/19.3.0.0/grid/install/root_racnode-dc2-1_2019-08-06_02-44-59-241151038.log for the output of root script

[root@racnode-dc2-1 ~]# cat /u01/app/19.3.0.0/grid/install/root_racnode-dc2-1_2019-08-06_02-44-59-241151038.log
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/19.3.0.0/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.
Relinking oracle with rac_on option
Performing Dry run of the Grid Infrastructure upgrade.
Using configuration parameter file: /u01/app/19.3.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/oracle/crsdata/racnode-dc2-1/crsconfig/rootcrs_racnode-dc2-1_2019-08-06_02-45-31AM.log
2019/08/06 02:45:44 CLSRSC-464: Starting retrieval of the cluster configuration data
2019/08/06 02:45:52 CLSRSC-729: Checking whether CRS entities are ready for upgrade, cluster upgrade will not be attempted now. This operation may take a few minutes.
2019/08/06 02:47:56 CLSRSC-693: CRS entities validation completed successfully.
[root@racnode-dc2-1 ~]#

Check grid home for node2:

[oracle@racnode-dc2-2 ~]$ du -sh /u01/app/19.3.0.0/grid/
6.6G    /u01/app/19.3.0.0/grid/
[oracle@racnode-dc2-2 ~]$

Check oraInventory for ALL nodes:

[oracle@racnode-dc2-2 ~]$ cat /u01/app/oraInventory/ContentsXML/inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2019, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>12.2.0.7.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/u01/app/12.2.0.1/grid" TYPE="O" IDX="1" CRS="true"/>
<HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/12.2.0.1/db1" TYPE="O" IDX="2"/>
==========================================================================================
<HOME NAME="OraGI19Home1" LOC="/u01/app/19.3.0.0/grid" TYPE="O" IDX="3"/>
==========================================================================================
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
[oracle@racnode-dc2-2 ~]$

Check crs activeversion: 12.2.0.1.0

[oracle@racnode-dc2-1 ~]$ . /media/patch/gi.env
The Oracle base has been set to /u01/app/oracle
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.2.0.1/grid
ORACLE_HOME=/u01/app/12.2.0.1/grid
Oracle Instance alive for sid "+ASM1"

[oracle@racnode-dc2-1 ~]$ crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [12.2.0.1.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [927320293].
[oracle@racnode-dc2-1 ~]$

Check log location:

[oracle@racnode-dc2-1 ~]$ cd /u01/app/oraInventory/logs/GridSetupActions2019-08-06_02-35-52AM/

[oracle@racnode-dc2-1 GridSetupActions2019-08-06_02-35-52AM]$ ls -alrt
total 17420
-rw-r-----  1 oracle oinstall     129 Aug  6 02:35 installerPatchActions_2019-08-06_02-35-52AM.log
-rw-r-----  1 oracle oinstall       0 Aug  6 02:35 gridSetupActions2019-08-06_02-35-52AM.err
drwxrwx---  3 oracle oinstall    4096 Aug  6 02:35 temp_ob
-rw-r-----  1 oracle oinstall       0 Aug  6 02:39 oraInstall2019-08-06_02-35-52AM.err
drwxrwx--- 17 oracle oinstall    4096 Aug  6 02:39 ..
-rw-r-----  1 oracle oinstall     157 Aug  6 02:39 oraInstall2019-08-06_02-35-52AM.out
-rw-r-----  1 oracle oinstall       0 Aug  6 02:43 oraInstall2019-08-06_02-35-52AM.err.racnode-dc2-2
-rw-r-----  1 oracle oinstall     142 Aug  6 02:43 oraInstall2019-08-06_02-35-52AM.out.racnode-dc2-2
-rw-r-----  1 oracle oinstall 9341920 Aug  6 02:43 gridSetupActions2019-08-06_02-35-52AM.out
-rw-r-----  1 oracle oinstall   13419 Aug  6 02:43 time2019-08-06_02-35-52AM.log
-rw-r-----  1 oracle oinstall 8443087 Aug  6 02:43 gridSetupActions2019-08-06_02-35-52AM.log
drwxrwx---  3 oracle oinstall    4096 Aug  6 02:56 .
[oracle@racnode-dc2-1 GridSetupActions2019-08-06_02-35-52AM]$

After dryRunForUpgrade, detach 19.3.0.0 grid home and remove directory (19.3.0.0/grid) from all nodes.

export ORACLE_HOME=/u01/app/19.3.0.0/grid
$ORACLE_HOME/oui/bin/runInstaller -detachHome -silent ORACLE_HOME=$ORACLE_HOME
Advertisements

April 14, 2019

Update Override OPatch

Filed under: awk_sed_grep — mdinh @ 1:47 pm

Framework to source GI/DB RAC environment stored on shared volume.

[oracle@racnode-dc1-2 patch]$ df -h |grep patch
media_patch              3.7T  442G  3.3T  12% /media/patch

[oracle@racnode-dc1-2 patch]$ ps -ef|grep pmon
oracle    3268  2216  0 15:37 pts/0    00:00:00 grep --color=auto pmon
oracle   11254     1  0 06:33 ?        00:00:02 ora_pmon_hawk2
oracle   19995     1  0 05:52 ?        00:00:02 asm_pmon_+ASM2

[oracle@racnode-dc1-2 patch]$ cat /etc/oratab
+ASM2:/u01/app/12.1.0.1/grid:N
hawk2:/u01/app/oracle/12.1.0.1/db1:N

[oracle@racnode-dc1-2 patch]$ cat gi.env
### Michael Dinh : Mar 26, 2019
### Source RAC GI environment
### Prerequisites for hostname: last char from hostname must be digit
### Allow: prodhost01, racnode-dc1-1
### DisAllow: prod01host
set +x
unset ORACLE_UNQNAME
ORAENV_ASK=NO
h=$(hostname -s)
### Extract last character from hostname to create ORACLE_SID
export ORACLE_SID=+ASM${h:${#h} - 1}
. oraenv <<< $ORACLE_SID
export GRID_HOME=$ORACLE_HOME
env|egrep 'ORA|GRID'
sysresv|tail -1

[oracle@racnode-dc1-2 patch]$ cat hawk.env
### Michael Dinh : Mar 26, 2019
### Source RAC DB environment
### Prerequisites for hostname: last char from hostname must be digit
### Allow: prodhost01, racnode-dc1-1
### DisAllow: prod01host
set +x
unset GRID_HOME
h=$(hostname -s)
### Extract filename without extension (.env)
ORAENV_ASK=NO
export ORACLE_UNQNAME=$(basename $BASH_SOURCE .env)
### Extract last character from hostname to create ORACLE_SID
export ORACLE_SID=$ORACLE_UNQNAME${h:${#h} - 1}
. oraenv <<< $ORACLE_SID
env|egrep 'ORA|GRID'
sysresv|tail -1
[oracle@racnode-dc1-2 patch]$

update_opatch.sh

#!/bin/sh -x
update_opatch()
{
set -ex
cd $ORACLE_HOME
$ORACLE_HOME/OPatch/opatch version
unzip -qod . /media/patch/Jan2019/p6880880_122010_Linux-x86-64.zip ; echo $?
$ORACLE_HOME/OPatch/opatch version
}
ls -lh /media/patch/Jan2019/p6880880_122010_Linux-x86-64.zip
. /media/patch/gi.env
update_opatch
. /media/patch/hawk.env
update_opatch
exit

Run update_opatch.sh

[oracle@racnode-dc1-1 patch]$ ./update_opatch.sh
+ ls -lh /media/patch/Jan2019/p6880880_122010_Linux-x86-64.zip
-rwxrwxrwx 1 vagrant vagrant 107M Feb  1 22:08 /media/patch/Jan2019/p6880880_122010_Linux-x86-64.zip
+ . /media/patch/gi.env
++ set +x
The Oracle base has been changed from hawk1 to /u01/app/oracle
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.1.0.1/grid
ORACLE_HOME=/u01/app/12.1.0.1/grid
Oracle Instance alive for sid "+ASM1"
+ cd /u01/app/12.1.0.1/grid
+ /u01/app/12.1.0.1/grid/OPatch/opatch version
OPatch Version: 12.2.0.1.16

OPatch succeeded.
+ unzip -qod . /media/patch/Jan2019/p6880880_122010_Linux-x86-64.zip
+ echo 0
0
+ /u01/app/12.1.0.1/grid/OPatch/opatch version
OPatch Version: 12.2.0.1.16

OPatch succeeded.
+ . /media/patch/hawk.env
++ set +x
The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_UNQNAME=hawk
ORACLE_SID=hawk1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/12.1.0.1/db1
Oracle Instance alive for sid "hawk1"
+ cd /u01/app/oracle/12.1.0.1/db1
+ /u01/app/oracle/12.1.0.1/db1/OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.
+ unzip -qod . /media/patch/Jan2019/p6880880_122010_Linux-x86-64.zip
+ echo 0
0
+ /u01/app/oracle/12.1.0.1/db1/OPatch/opatch version
OPatch Version: 12.2.0.1.16

OPatch succeeded.
+ exit
[oracle@racnode-dc1-1 patch]$


[oracle@racnode-dc1-2 patch]$ ./update_opatch.sh
+ ls -lh /media/patch/Jan2019/p6880880_122010_Linux-x86-64.zip
-rwxrwxrwx 1 vagrant vagrant 107M Feb  1 22:08 /media/patch/Jan2019/p6880880_122010_Linux-x86-64.zip
+ . /media/patch/gi.env
++ set +x
The Oracle base has been set to /u01/app/oracle
ORACLE_SID=+ASM2
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.1.0.1/grid
ORACLE_HOME=/u01/app/12.1.0.1/grid
Oracle Instance alive for sid "+ASM2"
+ cd /u01/app/12.1.0.1/grid
+ /u01/app/12.1.0.1/grid/OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.
+ unzip -qod . /media/patch/Jan2019/p6880880_122010_Linux-x86-64.zip
+ echo 0
0
+ /u01/app/12.1.0.1/grid/OPatch/opatch version
OPatch Version: 12.2.0.1.16

OPatch succeeded.
+ . /media/patch/hawk.env
++ set +x
The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_UNQNAME=hawk
ORACLE_SID=hawk2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/12.1.0.1/db1
Oracle Instance alive for sid "hawk2"
+ cd /u01/app/oracle/12.1.0.1/db1
+ /u01/app/oracle/12.1.0.1/db1/OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.
+ unzip -qod . /media/patch/Jan2019/p6880880_122010_Linux-x86-64.zip
+ echo 0
0
+ /u01/app/oracle/12.1.0.1/db1/OPatch/opatch version
OPatch Version: 12.2.0.1.16

OPatch succeeded.
+ exit
[oracle@racnode-dc1-2 patch]$

March 16, 2019

Playing with oracleasm and ASMLib

Filed under: 12c,ASM,awk_sed_grep — mdinh @ 12:02 am

Forgot about script I wrote some time ago: Be Friend With awk/sed | ASM Mapping

[root@racnode-dc1-1 ~]# cat /sf_working/scripts/asm_mapping.sh
#!/bin/sh -e
for disk in `/etc/init.d/oracleasm listdisks`
do
oracleasm querydisk -d $disk
#ls -l /dev/*|grep -E `oracleasm querydisk -d $disk|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'|awk -F, '{print $1 ",.*" $2}'`
# Alternate option to remove []
ls -l /dev/*|grep -E `oracleasm querydisk -d $disk|awk '{print $NF}'|sed 's/[][]//g'|awk -F, '{print $1 ",.*" $2}'`
echo
done

[root@racnode-dc1-1 ~]# /sf_working/scripts/asm_mapping.sh
Disk "CRS01" is a valid ASM disk on device [8,33]
brw-rw---- 1 root    disk      8,  33 Mar 16 10:25 /dev/sdc1

Disk "DATA01" is a valid ASM disk on device [8,49]
brw-rw---- 1 root    disk      8,  49 Mar 16 10:25 /dev/sdd1

Disk "FRA01" is a valid ASM disk on device [8,65]
brw-rw---- 1 root    disk      8,  65 Mar 16 10:25 /dev/sde1

[root@racnode-dc1-1 ~]#

HOWTO: Which Disks Are Handled by ASMLib Kernel Driver? (Doc ID 313387.1)

[root@racnode-dc1-1 ~]# oracleasm listdisks
CRS01
DATA01
FRA01

[root@racnode-dc1-1 dev]# ls -l /dev/oracleasm/disks
total 0
brw-rw---- 1 oracle dba 8, 33 Mar 15 10:46 CRS01
brw-rw---- 1 oracle dba 8, 49 Mar 15 10:46 DATA01
brw-rw---- 1 oracle dba 8, 65 Mar 15 10:46 FRA01

[root@racnode-dc1-1 dev]# ls -l /dev | grep -E '33|49|65'|grep -E '8'
brw-rw---- 1 root    disk      8,  33 Mar 15 23:47 sdc1
brw-rw---- 1 root    disk      8,  49 Mar 15 23:47 sdd1
brw-rw---- 1 root    disk      8,  65 Mar 15 23:47 sde1

[root@racnode-dc1-1 dev]# /sbin/blkid | grep oracleasm
/dev/sde1: LABEL="FRA01" TYPE="oracleasm" PARTLABEL="primary" PARTUUID="205115d9-730d-4f64-aedd-d3886e73d123"
/dev/sdd1: LABEL="DATA01" TYPE="oracleasm" PARTLABEL="primary" PARTUUID="714e56a4-210c-4836-a9cd-ff2162c1dea7"
/dev/sdc1: LABEL="CRS01" TYPE="oracleasm" PARTLABEL="primary" PARTUUID="232e214d-07bb-4f36-aba8-fb215437fb7e"
[root@racnode-dc1-1 dev]#

Various commands to retrieved oracleasm info and more.

[root@racnode-dc1-1 ~]# cat /etc/oracle-release
Oracle Linux Server release 7.3

[root@racnode-dc1-1 ~]# cat /etc/system-release
Oracle Linux Server release 7.3

[root@racnode-dc1-1 ~]# uname -r
4.1.12-61.1.18.el7uek.x86_64

[root@racnode-dc1-1 ~]# rpm -q oracleasm-`uname -r`
package oracleasm-4.1.12-61.1.18.el7uek.x86_64 is not installed

[root@racnode-dc1-1 ~]# rpm -qa |grep oracleasm
oracleasmlib-2.0.4-1.el6.x86_64
oracleasm-support-2.1.8-3.1.el7.x86_64
kmod-oracleasm-2.0.8-17.0.1.el7.x86_64

[root@racnode-dc1-1 ~]# oracleasm -V
oracleasm version 2.1.9

[root@racnode-dc1-1 ~]# oracleasm -h
Usage: oracleasm [--exec-path=<exec_path>] <command> [ <args> ]
       oracleasm --exec-path
       oracleasm -h
       oracleasm -V

The basic oracleasm commands are:
    configure        Configure the Oracle Linux ASMLib driver
    init             Load and initialize the ASMLib driver
    exit             Stop the ASMLib driver
    scandisks        Scan the system for Oracle ASMLib disks
    status           Display the status of the Oracle ASMLib driver
    listdisks        List known Oracle ASMLib disks
    querydisk        Determine if a disk belongs to Oracle ASMlib
    createdisk       Allocate a device for Oracle ASMLib use
    deletedisk       Return a device to the operating system
    renamedisk       Change the label of an Oracle ASMlib disk
    update-driver    Download the latest ASMLib driver

[root@racnode-dc1-1 ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface [oracle]:
Default group to own the driver interface [dba]:
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done

[root@racnode-dc1-1 ~]# oracleasm configure
ORACLEASM_ENABLED=true
ORACLEASM_UID=oracle
ORACLEASM_GID=dba
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"

[root@racnode-dc1-1 ~]# cat /etc/sysconfig/oracleasm
#
# This is a configuration file for automatic loading of the Oracle
# Automatic Storage Management library kernel driver.  It is generated
# By running /etc/init.d/oracleasm configure.  Please use that method
# to modify this file
#

# ORACLEASM_ENABLED: 'true' means to load the driver on boot.
ORACLEASM_ENABLED=true

# ORACLEASM_UID: Default user owning the /dev/oracleasm mount point.
ORACLEASM_UID=oracle

# ORACLEASM_GID: Default group owning the /dev/oracleasm mount point.
ORACLEASM_GID=dba

# ORACLEASM_SCANBOOT: 'true' means scan for ASM disks on boot.
ORACLEASM_SCANBOOT=true

# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER=""

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE=""

# ORACLEASM_USE_LOGICAL_BLOCK_SIZE: 'true' means use the logical block size
# reported by the underlying disk instead of the physical. The default
# is 'false'
ORACLEASM_USE_LOGICAL_BLOCK_SIZE=false

[root@racnode-dc1-1 ~]# oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

[root@racnode-dc1-1 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...

[root@racnode-dc1-1 ~]# oracleasm querydisk -d DATA01
Disk "DATA01" is a valid ASM disk on device [8,49]

[root@racnode-dc1-1 ~]# oracleasm querydisk -p DATA01
Disk "DATA01" is a valid ASM disk
/dev/sdd1: LABEL="DATA01" TYPE="oracleasm" PARTLABEL="primary" PARTUUID="714e56a4-210c-4836-a9cd-ff2162c1dea7"

[root@racnode-dc1-1 ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:CRS01 [104853504 blocks (53684994048 bytes), maxio 1024]
Discovered disk: ORCL:DATA01 [104853504 blocks (53684994048 bytes), maxio 1024]
Discovered disk: ORCL:FRA01 [104853504 blocks (53684994048 bytes), maxio 1024]

[root@racnode-dc1-1 ~]# lsmod | grep oracleasm
oracleasm              57344  1

[root@racnode-dc1-1 ~]# modinfo oracleasm
filename:       /lib/modules/4.1.12-61.1.18.el7uek.x86_64/kernel/drivers/block/oracleasm/oracleasm.ko
description:    Kernel driver backing the Generic Linux ASM Library.
author:         Joel Becker, Martin K. Petersen <martin.petersen@oracle.com>
version:        2.0.8
license:        GPL
srcversion:     4B3524FDA590726E8D378CB
depends:
intree:         Y
vermagic:       4.1.12-61.1.18.el7uek.x86_64 SMP mod_unload modversions
signer:         Oracle CA Server
sig_key:        AC:74:F5:41:96:B5:9D:EB:61:BA:02:F9:C2:02:8C:9C:E5:94:53:06
sig_hashalgo:   sha512
parm:           use_logical_block_size:Prefer logical block size over physical (Y=logical, N=physical [default]) (bool)

[root@racnode-dc1-1 ~]# ls -la /etc/sysconfig/oracleasm
lrwxrwxrwx 1 root root 24 Mar  5 20:21 /etc/sysconfig/oracleasm -> oracleasm-_dev_oracleasm

[root@racnode-dc1-1 ~]# rpm -qa | grep oracleasm
oracleasmlib-2.0.4-1.el6.x86_64
oracleasm-support-2.1.8-3.1.el7.x86_64
kmod-oracleasm-2.0.8-17.0.1.el7.x86_64

[root@racnode-dc1-1 ~]# rpm -qi oracleasmlib-2.0.4-1.el6.x86_64
Name        : oracleasmlib
Version     : 2.0.4
Release     : 1.el6
Architecture: x86_64
Install Date: Tue 18 Apr 2017 10:56:40 AM CEST
Group       : System Environment/Kernel
Size        : 27192
License     : Oracle Corporation
Signature   : RSA/SHA256, Mon 26 Mar 2012 10:22:51 PM CEST, Key ID 72f97b74ec551f03
Source RPM  : oracleasmlib-2.0.4-1.el6.src.rpm
Build Date  : Mon 26 Mar 2012 10:22:44 PM CEST
Build Host  : ca-build44.us.oracle.com
Relocations : (not relocatable)
Packager    : Joel Becker <joel.becker@oracle.com>
Vendor      : Oracle Corporation
URL         : http://oss.oracle.com/
Summary     : The Oracle Automatic Storage Management library userspace code.
Description :
The Oracle userspace library for Oracle Automatic Storage Management
[root@racnode-dc1-1 ~]#

References for ASMLib

Do you need asmlib?

Oracleasmlib Not Necessary

February 23, 2019

Sed’ing Through ora.cvu Hell

Filed under: 12c,awk_sed_grep,Grid Infrastructure — mdinh @ 12:02 pm

Don’t know why I always look for trouble.

The trouble found was CHECK_RESULTS from ora.cvu.type had many issues which look to be BUGS related.

Here is the RAC environment from VM.

[oracle@racnode-dc1-1 ~]$ cat /etc/system-release
Oracle Linux Server release 7.3
[oracle@racnode-dc1-1 ~]$

[oracle@racnode-dc1-1 ~]$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [0] and no patches have been applied on the local node.

[oracle@racnode-dc1-1 ~]$ crsctl query crs softwarepatch
Oracle Clusterware patch level on node racnode-dc1-1 is [0].

[oracle@racnode-dc1-1 ~]$ crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [0].

[oracle@racnode-dc1-1 ~]$ crsctl stat res -w "TYPE = ora.cvu.type" -p|grep RESULTS | sed 's/,/\n/g'
CHECK_RESULTS=PRVF-5507 : NTP daemon or service is not running on any node but NTP configuration file exists on the following node(s):
racnode-dc1-2
racnode-dc1-1
PRVF-5415 : Check to see if NTP daemon or service is running failed
PRVF-7573 : Sufficient swap size is not available on node "racnode-dc1-2" [Required = 2.7844GB (2919680.0KB) ; Found = 2GB (2097148.0KB)]
PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm
PRCW-1015 : Wallet hawk does not exist.
CLSW-9: The cluster wallet to be operated on does not exist. :[1015]
PRVF-7573 : Sufficient swap size is not available on node "racnode-dc1-1" [Required = 2.7844GB (2919680.0KB) ; Found = 2GB (2097148.0KB)]
PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm
PRCW-1015 : Wallet hawk does not exist.
CLSW-9: The cluster wallet to be operated on does not exist. :[1015]
[oracle@racnode-dc1-1 ~]$

BUGS?

Linux OL7/RHEL7: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm (Doc ID 2065603.1)

Bug 24696235 – cvu check results shows errors PRCW-1015 and CLSW-9 (Doc ID 24696235.8)

[root@racnode-dc1-1 ~]# ocrdump
[root@racnode-dc1-1 ~]# cat OCRDUMPFILE |grep -i SYSTEM.WALLET
[SYSTEM.WALLET]
[SYSTEM.WALLET.APPQOSADMIN]
[SYSTEM.WALLET.MGMTDB]
[root@racnode-dc1-1 ~]#

There’s is indeed no wallet for database hawk. But if wallet is created, will only result in another bug?

cluvfy:PRCQ-1000 : An error occurred while establishing connection to database with user name “DBSNMP” (Doc ID 2288958.1)

PRCQ-1000 : An error occurred while establishing connection to database with user name "DBSNMP" and connect descriptor:
ORA-01017: invalid username/password; logon denied

Cluster Verification Utility (CVU) Check Fails With NTP Configuration (Doc ID 2162408.1)

Some Good References:

Slimming Down Oracle RAC 12c’s Resource Footprint

Oracle Grid Infrastructure: change the interval for the Cluster Verification Utility (ora.cvu)

Small Notes on Clusterware resource ora.cvu

February 19, 2019

Using awk to remove attributes without values

Filed under: awk_sed_grep — mdinh @ 2:41 am

Thanks to suggestion from zhwsh: crsctl stat res -w "TYPE = ora.cvu.type" -p|grep -v "=$" (is another option for crsctl stat res -w "TYPE = ora.cvu.type" -p|awk -F'=' '$2')

Attributes without values are displayed.

[oracle@racnode-dc1-1 ~]$ crsctl stat res -w "TYPE = ora.cvu.type" -p
NAME=ora.cvu
TYPE=ora.cvu.type
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTIONS=
ACTION_SCRIPT=
ACTION_TIMEOUT=60
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/orajagent
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=60
CHECK_RESULTS=1122099754
CHECK_TIMEOUT=600
CLEAN_TIMEOUT=60
CRSHOME_SPACE_ALERT_STATE=OFF
CSS_CRITICAL=no
CV_DESTLOC=
DEGREE=1
DELETE_TIMEOUT=60
DESCRIPTION=Oracle CVU resource
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
GEN_NEXT_CHECK_TIME=1550563672
GEN_RUNNING_NODE=racnode-dc1-2
HOSTING_MEMBERS=
IGNORE_TARGET_ON_FAILURE=no
INSTANCE_FAILOVER=1
INTERMEDIATE_TIMEOUT=0
LOAD=1
LOGGING_LEVEL=1
MODIFY_TIMEOUT=60
NEXT_CHECK_TIME=
NLS_LANG=
OFFLINE_CHECK_INTERVAL=0
PLACEMENT=restricted
RELOCATE_BY_DEPENDENCY=1
RELOCATE_KIND=offline
RESOURCE_GROUP=
RESTART_ATTEMPTS=5
RESTART_DELAY=0
RUN_INTERVAL=21600
SCRIPT_TIMEOUT=30
SERVER_CATEGORY=ora.hub.category
SERVER_POOLS=*
START_CONCURRENCY=0
START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network)
START_TIMEOUT=0
STOP_CONCURRENCY=0
STOP_DEPENDENCIES=hard(intermediate:ora.net1.network)
STOP_TIMEOUT=0
TARGET_DEFAULT=default
TYPE_VERSION=1.1
UPTIME_THRESHOLD=1h
USER_WORKLOAD=no
USE_STICKINESS=0
USR_ORA_ENV=
WORKLOAD_CPU=0
WORKLOAD_CPU_CAP=0
WORKLOAD_MEMORY_MAX=0
WORKLOAD_MEMORY_TARGET=0

[oracle@racnode-dc1-1 ~]$

Attributes without values are NOT displayed.

[oracle@racnode-dc1-1 ~]$ crsctl stat res -w "TYPE = ora.cvu.type" -p|awk -F'=' '$2'
NAME=ora.cvu
TYPE=ora.cvu.type
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTION_TIMEOUT=60
AGENT_FILENAME=%CRS_HOME%/bin/orajagent
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=60
CHECK_RESULTS=1122099754
CHECK_TIMEOUT=600
CLEAN_TIMEOUT=60
CRSHOME_SPACE_ALERT_STATE=OFF
CSS_CRITICAL=no
DEGREE=1
DELETE_TIMEOUT=60
DESCRIPTION=Oracle CVU resource
ENABLED=1
GEN_NEXT_CHECK_TIME=1550563672
GEN_RUNNING_NODE=racnode-dc1-2
IGNORE_TARGET_ON_FAILURE=no
INSTANCE_FAILOVER=1
LOAD=1
LOGGING_LEVEL=1
MODIFY_TIMEOUT=60
PLACEMENT=restricted
RELOCATE_BY_DEPENDENCY=1
RELOCATE_KIND=offline
RESTART_ATTEMPTS=5
RUN_INTERVAL=21600
SCRIPT_TIMEOUT=30
SERVER_CATEGORY=ora.hub.category
SERVER_POOLS=*
START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network)
STOP_DEPENDENCIES=hard(intermediate:ora.net1.network)
TARGET_DEFAULT=default
TYPE_VERSION=1.1
UPTIME_THRESHOLD=1h
USER_WORKLOAD=no
[oracle@racnode-dc1-1 ~]$

You might ask why I am doing this.

I am reviewing configuration before implementation and will compare them with after implementation.

The less items I need to look at before implementation, the better.

January 21, 2018

Be Friend With awk/sed | ASM Mapping

Filed under: 12c,ASM,awk_sed_grep — mdinh @ 5:10 pm

I had request to add disks to ASM Disk Group without any further details for what new disks were added.

Need to figure out which disks are on ASM now, which disks should be used as new ones.

Got lazy and created scripts for this for future use.

[root@racnode-dc1-1 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@racnode-dc1-1 ~]#

[oracle@racnode-dc1-1 ~]$ /etc/init.d/oracleasm listdisks
CRS01
DATA01
FRA01

--- [8,49] is major,minor for device
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01
Disk "DATA01" is a valid ASM disk on device [8,49]

--- Extract major,minor for devide
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01|awk '{print $NF}'
[8,49]

--- Remove [] brackets
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01|awk '{print $NF}'|sed s'/.$//'
[8,49
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'
8,49

--- Alternative option to remove []
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01|awk '{print $NF}'|sed 's/[][]//g'
8,49

--- Create patterns for grep
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'|awk -F, '{print $1 ",.*" $2}'
8,.*49

--- Test grep using pattern
[oracle@racnode-dc1-1 ~]$ ls -l /dev/* | grep -E '8,.*49'
brw-rw---- 1 root    disk      8,  49 Jan 21 16:42 /dev/sdd1
[oracle@racnode-dc1-1 ~]$

--- Test grep with command line syntax
[oracle@racnode-dc1-1 ~]$ ls -l /dev/*|grep -E `oracleasm querydisk -d DATA01|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'|awk -F, '{print $1 ",.*" $2}'`
brw-rw---- 1 root    disk      8,  49 Jan 21 16:42 /dev/sdd1
[oracle@racnode-dc1-1 ~]$

--- Run script
[oracle@racnode-dc1-1 ~]$ /sf_working/scripts/asm_mapping.sh
Disk "CRS01" is a valid ASM disk on device [8,33]
brw-rw---- 1 root    disk      8,  33 Jan 21 21:42 /dev/sdc1

Disk "DATA01" is a valid ASM disk on device [8,49]
brw-rw---- 1 root    disk      8,  49 Jan 21 21:42 /dev/sdd1

Disk "FRA01" is a valid ASM disk on device [8,65]
brw-rw---- 1 root    disk      8,  65 Jan 21 21:42 /dev/sde1

[oracle@racnode-dc1-1 ~]$

--- ASM Lib version
[oracle@racnode-dc1-1 ~]$ rpm -qa|grep asm
oracleasmlib-2.0.4-1.el6.x86_64
oracleasm-support-2.1.8-3.1.el7.x86_64
kmod-oracleasm-2.0.8-19.0.1.el7.x86_64
[oracle@racnode-dc1-1 ~]$

--- Script
[oracle@racnode-dc1-1 ~]$ cat /sf_working/scripts/asm_mapping.sh

#!/bin/sh -e
for disk in `/etc/init.d/oracleasm listdisks`
do
oracleasm querydisk -d $disk
#ls -l /dev/*|grep -E `oracleasm querydisk -d $disk|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'|awk -F, '{print $1 ",.*" $2}'`
# Alternate option to remove []
ls -l /dev/*|grep -E `oracleasm querydisk -d $disk|awk '{print $NF}'|sed 's/[][]//g'|awk -F, '{print $1 ",.*" $2}'`
echo
done
[root@racnode-dc1-1 ~]# fdisk -l /dev/sdd1

Disk /dev/sdd1: 8587 MB, 8587837440 bytes, 16773120 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

[root@racnode-dc1-1 ~]#

January 5, 2018

More Fun With sed

Filed under: awk_sed_grep — mdinh @ 2:33 am

Objective is to convert what looks to be Samba share from Windows to Linux current directory path.

Basically, the core of the code using sed.


sed -i.bak -e 's|C\:\\\\scripts|'"$PWD"'|g' -e 's|\\\\|\/|g' "$f"

[vagrant@db-asm-1 ~]$ pwd
/home/vagrant
[vagrant@db-asm-1 ~]$

[vagrant@db-asm-1 ~]$ ll
total 8
-rw-rw-r-- 1 vagrant vagrant 56 Jan  5 03:22 t.1
-rw-rw-r-- 1 vagrant vagrant 55 Jan  5 03:22 t.2
drwxrwxr-x 3 vagrant vagrant 18 Jan  5 03:21 working

[vagrant@db-asm-1 ~]$ cat t.1
set file_path "C:\\scripts"
source "$file_path\\t1.sql"

[vagrant@db-asm-1 ~]$ cat t.2
set file_path "C:\\scripts"
source "$file_path\\t2.sql


[vagrant@db-asm-1 ~]$ cat ~/working/dinh/test.sh
#!/bin/bash -ex
# Make sure you always put $f in double quotes to avoid any nasty surprises i.e. "$f"
for f in t.*
do
  echo "Processing $f file..."
  sed -i.bak -e 's|C\:\\\\scripts|'"$PWD"'|g' -e 's|\\\\|\/|g' "$f"
done


[vagrant@db-asm-1 ~]$ ~/working/dinh/test.sh
+ for f in 't.*'
+ echo 'Processing t.1 file...'
Processing t.1 file...
+ sed -i.bak -e 's|C\:\\\\scripts|/home/vagrant|g' -e 's|\\\\|\/|g' t.1
+ for f in 't.*'
+ echo 'Processing t.2 file...'
Processing t.2 file...
+ sed -i.bak -e 's|C\:\\\\scripts|/home/vagrant|g' -e 's|\\\\|\/|g' t.2


[vagrant@db-asm-1 ~]$ cat t.1
set file_path "/home/vagrant"
source "$file_path/t1.sql"

[vagrant@db-asm-1 ~]$ cat t.1.bak
set file_path "C:\\scripts"
source "$file_path\\t1.sql"


[vagrant@db-asm-1 ~]$ cat t.2
set file_path "/home/vagrant"
source "$file_path/t2.sql

[vagrant@db-asm-1 ~]$ cat t.2.bak
set file_path "C:\\scripts"
source "$file_path\\t2.sql

[vagrant@db-asm-1 ~]$ ll
total 16
-rw-rw-r-- 1 vagrant vagrant 57 Jan  5 03:26 t.1
-rw-rw-r-- 1 vagrant vagrant 56 Jan  5 03:22 t.1.bak
-rw-rw-r-- 1 vagrant vagrant 56 Jan  5 03:26 t.2
-rw-rw-r-- 1 vagrant vagrant 55 Jan  5 03:22 t.2.bak
drwxrwxr-x 3 vagrant vagrant 18 Jan  5 03:21 working
[vagrant@db-asm-1 ~]$

 

December 14, 2017

Using sed to backup file and remove lines

Filed under: awk_sed_grep — mdinh @ 1:13 am
[oracle@racnode-dc1-2 ~]$ cd /u01/app/oracle/12.1.0.2/db1/rdbms/log/

--- DDL will fail since datafile is hard coded!
[oracle@racnode-dc1-2 log]$ cat tablespaces_ddl.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/TABLESPACE
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
  SIZE 26214400
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

   ALTER DATABASE DATAFILE
  '+DATA/HAWK/DATAFILE/undotbs1.260.962253853' RESIZE 152043520;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  SIZE 213909504
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE
  SIZE 26214400
  AUTOEXTEND ON NEXT 26214400 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

   ALTER DATABASE DATAFILE
  '+DATA/HAWK/DATAFILE/undotbs2.265.962254263' RESIZE 235929600;
CREATE TABLESPACE "USERS" DATAFILE
  SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M,
  SIZE 4194304
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

   ALTER DATABASE DATAFILE
  '+DATA/HAWK/DATAFILE/users.269.962674885' RESIZE 5242880;

--- Remove ALTER and RESIZE from sql file.
--- Most likely the incorrect way to do this since TBS may be undersized.

12.2 Datapump Improvements actually does this the right way.

[oracle@racnode-dc1-2 log]$ sed -i.bak '/ALTER DATABASE DATAFILE\|RESIZE/ d' tablespaces_ddl.sql

[oracle@racnode-dc1-2 log]$ ls -l tablespace*
-rw-r--r-- 1 oracle dba 1214 Dec 14 02:03 tablespaces_ddl.sql
-rw-r--r-- 1 oracle dba 1488 Dec 14 01:45 tablespaces_ddl.sql.bak

[oracle@racnode-dc1-2 log]$ diff tablespaces_ddl.sql tablespaces_ddl.sql.bak
14a15,16
>    ALTER DATABASE DATAFILE
>   '+DATA/HAWK/DATAFILE/undotbs1.260.962253853' RESIZE 152043520;
24a27,28
>    ALTER DATABASE DATAFILE
>   '+DATA/HAWK/DATAFILE/undotbs2.265.962254263' RESIZE 235929600;
32a37,38
>    ALTER DATABASE DATAFILE
>   '+DATA/HAWK/DATAFILE/users.269.962674885' RESIZE 5242880;

[oracle@racnode-dc1-2 log]$ cat tablespaces_ddl.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/TABLESPACE
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
  SIZE 26214400
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  SIZE 213909504
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE
  SIZE 26214400
  AUTOEXTEND ON NEXT 26214400 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE TABLESPACE "USERS" DATAFILE
  SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M,
  SIZE 4194304
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

[oracle@racnode-dc1-2 log]$

May 15, 2016

Automating DG Broker

Filed under: 11g,awk_sed_grep,Dataguard,oracle,shell scripting — mdinh @ 2:11 am

I have been applying PSU lately and what’s so hard out it?

Four+ databases running on Primary with DG Broker for standby.

There are no conventions, as some standby databases have dr appended to primary name while others have 2 appended to primary name.

I wanted to view the DG configuration for currently active instances and show_dg_config.sh will show me this.

Next, I want a faster way to shutdown DG by having syntax generated and  gen_dg_cmd.sh does this.

Guess I could have taken it further by creating a shell script to create shell scripts to shutdown DG.

One day when I am really bore, I might OR may be you are so nice to complete my mission.

Tested on AIX 7.1

Note: the ps -ef syntax is for AIX and will not work with Linux.

See below for the Linux alternative.

$ ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ

$ ps -eo args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
thor
hulk

show_dg_config.sh

#!/bin/sh -e
ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
export ORAENV_ASK=NO
for SID in ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'`
do
export ORACLE_SID=$SID
. /usr/local/bin/oraenv
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
echo "+++: " $ORACLE_SID $ORACLE_HOME
sysresv
dgmgrl -echo << END
connect /
show configuration
exit
END
done
exit

gen_dg_cmd.sh

#!/bin/sh -e
for XB in `egrep 'Primary|Physical' /tmp/dg.log |sort |awk -F" " '{print $3 $1}'`
do
#echo $XB
#echo $XB|awk '{print substr($1,1,7)}'
if [ "`echo $XB|awk '{print substr($1,1,7)}'`" == "Primary" ]
then
PRI=`echo $XB|awk '{print substr($1,8)}'`
echo "edit database $PRI set state='LOG-TRANSPORT-OFF';"
echo "show database $PRI"
echo "edit database $PRI set state='ONLINE';"
echo "show database $PRI"
fi
if [ "`echo $XB|awk '{print substr($1,1,8)}'`" == "Physical" ]
then
SBY=`echo $XB|awk '{print substr($1,9)}'`
echo "edit database $SBY set state='APPLY-OFF';"
echo "show database $SBY"
echo "edit database $SBY set state='APPLY-ON';"
echo "show database $SBY"
fi
done
exit

./show_dg_config.sh > /tmp/dg.log

egrep ‘Primary|Physical’ /tmp/dg.log |sort |awk -F” ” ‘{print $3 $1}’

Primarydb02
Physicaldb02dr
Primarydb01
Physicaldb01dr
Primarystageqa
Physicalstageqa2
Primarytest
Physicaltestdr

./gen_dg_cmd.sh

edit database db01 set state='LOG-TRANSPORT-OFF';
show database db01
edit database db01 set state='ONLINE';
show database db01
edit database db01dr set state='APPLY-OFF';
show database db01dr
edit database db01dr set state='APPLY-ON';
show database db01dr
edit database db02 set state='LOG-TRANSPORT-OFF';
show database db02
edit database db02 set state='ONLINE';
show database db02
edit database db02dr set state='APPLY-OFF';
show database db02dr
edit database db02dr set state='APPLY-ON';
show database db02dr
edit database stageqa set state='LOG-TRANSPORT-OFF';
show database stageqa
edit database stageqa set state='ONLINE';
show database stageqa
edit database stageqa2 set state='APPLY-OFF';
show database stageqa2
edit database stageqa2 set state='APPLY-ON';
show database stageqa2
edit database test set state='LOG-TRANSPORT-OFF';
show database test
edit database test set state='ONLINE';
show database test
edit database testdr set state='APPLY-OFF';
show database testdr
edit database testdr set state='APPLY-ON';
show database testdr
oracle:/home/oracle/working/dinh$

Create a free website or blog at WordPress.com.