Saturday, August 6, 2022

My Work with GitHub

Hello everyone, I don't have much time to post something here but I would like to share with you all some scripts I am developing for a while, everything you can find in this link:

https://github.com/dbaribas/dbnitro

You can download and use it as you wish.

The main script is the OracleMenu.sh

This script will provide to you a lot of benefits to select and use different Oracle Homes, Instances and sql statements to help you day-by-day as Oracle Experts.

Enjoy it.

Monday, April 5, 2021

Migrate ASMLIB to ASMFD

# --------------------------------------------------------------------------------------------

# ASMFD : How to Migrate ASM Diskgroups from ASMLIB to ASMFD (ASM Filter Driver) on Oracle Grid Infrastructure (RAC) (Doc ID 2172754.1)


# --------------------------------------------------------------------------------------------

# Applies to:


Oracle Database Backup Service - Version N/A and later

Oracle Database Cloud Exadata Service - Version N/A and later

Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later Linux x86-64


# --------------------------------------------------------------------------------------------

# What is Oracle ASM Filter Driver (Oracle ASMFD) ?


Oracle ASM Filter Driver (Oracle ASMFD) is a kernel module that resides in the I/O path of the Oracle ASM disks. Oracle ASM uses the filter driver to validate write I/O requests to Oracle ASM disks.

The Oracle ASMFD simplifies the configuration and management of disk devices by eliminating the need to rebind disk devices used with Oracle ASM each time the system is restarted.

The Oracle ASM Filter Driver rejects any I/O requests that are invalid. This action eliminates accidental overwrites of Oracle ASM disks that would cause corruption in the disks and files within the disk group. For example, the Oracle ASM Filter Driver filters out all non-Oracle I/Os which could cause accidental overwrites.

Oracle ASM Filter Driver (Oracle ASMFD) is installed with an Oracle Grid Infrastructure installation. If you have an existing Oracle ASM library driver (Oracle ASMLIB) configuration, then this document will guide you to migrate the existing ASM diskgroup disks which are stamped with ASMLib to ASMFD (ASM Filter Driver).


# --------------------------------------------------------------------------------------------

# Solution


Following assumptions are made before we proceed with the next steps 


1.  Please check the existing environment is certified or not. Check below document


ASMFD (ASM Filter Driver) Support on OS Platforms (Certification Matrix).(Doc ID 2034681.1)


2.  It is recommended to apply latest PSU for successful migration.


3.  Take the backup of the database before proceeding this activity.


# --------------------------------------------------------------------------------------------

# Step by Step Procedure


1.  As a root user run below command to generate the which ASM Disk is associated with the OS disk. In a multipath configuration, please use the multipath name


[ root@srv10:/home: ]$ oracleasm listdisks | xargs oracleasm querydisk -p

Disk "DATA" is a valid ASM disk /dev/sdb1: LABEL="DATA" TYPE="oracleasm"

Disk "FRA" is a valid ASM disk /dev/sdc1: LABEL="FRA" TYPE="oracleasm"


2.  As a Grid Infrastructure (GI) software owner, get the present ASM diskstring value

[ +ASM ]|[ oracle@srv10:/home/oracle: ]$ asmcmd -p dsget

parameter:/dev/oracleasm/disks/*

profile:/dev/oracleasm/disks/*

[ +ASM ]|[ oracle@srv10:/home/oracle: ]$


In case the ASMLIB disk discovery string is NOT /dev/oracleasm/disks/* but "ORCL:*".

grid@hostname1 ~]$ asmcmd dsget

parameter:ORCL:*

profile:ORCL:*


3.  As a GI owner update the Oracle ASM disk discovery string to enable Oracle ASMFD to discover devices in the future.


[ +ASM ]|[ oracle@srv10:/home/oracle: ]$ asmcmd -p dsset '/dev/oracleasm/disks/*','AFD:*'

[ +ASM ]|[ oracle@srv10:/home/oracle: ]$ asmcmd -p dsget

parameter:/dev/oracleasm/disks/*, AFD:*

profile:/dev/oracleasm/disks/*,AFD:*



In case the ASMLIB disk discovery string is NOT /dev/oracleasm/disks/* but "ORCL:*".


[grid@hostname1 ~]$ asmcmd dsset 'ORCL:*','AFD:*'

[grid@hostname1 ~]$ asmcmd dsget

parameter:ORCL:*, AFD:*

profile:ORCL:*,AFD:*



4.  As a root user stop the cluster accessing the shared ASMLib disks


# Cluster

[ root@srv10:/root: ]$ crsctl stop crs



# Standalone

[ root@srv10:/root: ]$ crsctl stop has

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'srv10'

CRS-2673: Attempting to stop 'ora.dbprod.db' on 'srv10'

CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'srv10'

CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'srv10' succeeded

CRS-2677: Stop of 'ora.dbprod.db' on 'srv10' succeeded

CRS-2673: Attempting to stop 'ora.DATA.dg' on 'srv10'

CRS-2673: Attempting to stop 'ora.FRA.dg' on 'srv10'

CRS-2677: Stop of 'ora.DATA.dg' on 'srv10' succeeded

CRS-2677: Stop of 'ora.FRA.dg' on 'srv10' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'srv10'

CRS-2677: Stop of 'ora.asm' on 'srv10' succeeded

CRS-2673: Attempting to stop 'ora.evmd' on 'srv10'

CRS-2677: Stop of 'ora.evmd' on 'srv10' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'srv10'

CRS-2677: Stop of 'ora.cssd' on 'srv10' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'srv10' has completed

CRS-4133: Oracle High Availability Services has been stopped.


5.  Check and Stop the oracleasm driver and acfsload as root user


[ root@srv10:/root: ]$ oracleasm status

Checking if ASM is loaded: yes

Checking if /dev/oracleasm is mounted: yes


[ root@srv10:/root: ]$ oracleasm exit

Unmounting ASMlib driver filesystem: /dev/oracleasm

Unloading module "oracleasm": oracleasm


[ root@srv10:/root: ]$ ls -ltr /dev/oracleasm/

total 0


[ root@srv10:/root: ]$ lsmod | grep acfs

oracleacfs 3498241 2


oracleoks 503994 2 oracleacfs,oracleadvm


[ root@srv10:/root: ]$ acfsload stop


[ root@srv10:/root: ]$ lsmod | grep acfs


# --------------------------------------------------------------------------------------------

# Note: It is recommended to remove the ASMLib related RPMs to avoid any conflict.


[ root@srv10:/root: ]$ rpm -qa | grep asm

oracleasmlib-2.0.12-1.el7.x86_64

kmod-oracleasm-2.0.8-28.0.1.el7.x86_64

oracleasm-support-2.1.11-2.el7.x86_64


[ root@srv10:/root: ]$ rpm -e oracleasm-support-2.1.11-2.el7.x86_64 kmod-oracleasm-2.0.8-28.0.1.el7.x86_64 oracleasmlib-2.0.12-1.el7.x86_64

warning: /etc/sysconfig/oracleasm saved as /etc/sysconfig/oracleasm.rpmsave


6.  As root, configure Oracle ASMFD to filter at the node level:


[ root@srv10:/root: ]$ afdroot version_check

AFD-616: Valid AFD distribution media detected at: '/u01/app/19.3.0.1/grid/usm/install/Oracle/EL7UEK/x86_64/5.4.17-2011.0.7/5.4.17-2011.0.7-x86_64/bin'


[ root@srv10:/root: ]$ afddriverstate supported

AFD-9200: Supported


[ root@srv10:/root: ]$ asmcmd afd_configure

AFD-627: AFD distribution files found.

AFD-634: Removing previous AFD installation.

AFD-635: Previous AFD components successfully removed.

AFD-9294: updating file /etc/sysconfig/oracledrivers.conf

AFD-636: Installing requested AFD software.

AFD-637: Loading installed AFD drivers.

AFD-9321: Creating udev for AFD.

AFD-9323: Creating module dependencies - this may take some time.

AFD-9154: Loading 'oracleafd.ko' driver.

AFD-649: Verifying AFD devices.

AFD-9156: Detecting control device '/dev/oracleafd/admin'.

AFD-638: AFD installation correctness verified.

Modifying resource dependencies - this may take some time.



7.  As the Oracle Grid Infrastructure / root owner verify the status of Oracle ASMFD:

[ root@srv10:/root: ]$ asmcmd afd_state

ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'srv10.dbnitro.net'


# Case is disabled

ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'DISABLED' on host 'hostname1'           <<<< Filtering is disabled


8.  As a root user set the AFD diskstring and enable the Filtering


[ root@srv10:/root: ]$ asmcmd -p afd_filter -e

[ root@srv10:/root: ]$ asmcmd -p afd_state

ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'srv10.dbnitro.net'      <<<< Filtering is enabled



9.  As a root user, with the help of the data gathered in step 1, migrate (label) all the ASMLib disks 

$ORACLE_HOME/bin/asmcmd afd_label <OLD ASMLib Disk Label Name> <Disk Path> --migrate


[ root@srv10:/root: ]$ cat /etc/oracleafd.conf

afd_diskstring='/dev/sd*'

afd_filtering=enable


[ root@srv10:/root: ]$ asmcmd afd_scan


[ root@srv10:/root: ]$ asmcmd afd_lsdsk


[ root@srv10:/root: ]$ asmcmd -p afd_label DATA /dev/sdb1 --migrate


[ root@srv10:/root: ]$ asmcmd -p afd_label FRA /dev/sdc1 --migrate



10.  Validate that all the disks have been migrated to the ASMFD.


[ root@srv10:/root: ]$ asmcmd afd_scan


[ root@srv10:/root: ]$ asmcmd afd_lsdsk

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

Label                     Filtering   Path

================================================================================

DATA                        ENABLED   /dev/sdb1

FRA                         ENABLED   /dev/sdc1



[ root@srv10:/root: ]$ ls -ltr /dev/oracleafd/disks/

total 8

-rw-rw-r-- 1 oracle oinstall 10 Mar 25 08:33 DATA

-rw-rw-r-- 1 oracle oinstall 10 Mar 25 08:41 FRA



11.  Edit the afd_diskstring with below content. (Ensure that you put the correct afd_diskstring, otherwise ASMFD disks will not get mounted in the mount point i.e in "/dev/oracleafd/disks/" folder)

[root@hostname1 ~]# vi /etc/afd.conf

#afd_diskstring='/dev/oracleasm/disks/*'

afd_diskstring='/dev/xvd*'

afd_filtering=enable 



# --------------------------------------------------------------------------------------------

# Tips:- This value can be derived from the path value displayed while running the "asmcmd afd_lsdsk" 


12.  Repeat above steps on all the nodes of the cluster.


13.  Start the CRS on all the nodes  ( before starting the CRS, start the ACFS module)


[ root@srv10:/root: ]$ lsmod | grep acfs

[ root@srv10:/root: ]$ asmcmd -p afd_lslbl

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

Label                     Duplicate  Path

================================================================================

DATA                                  /dev/sdb1

FRA                                   /dev/sdc1


[ root@srv10:/root: ]$ asmcmd -p afd_lsdsk

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

Label                     Filtering   Path

================================================================================

DATA                        ENABLED   /dev/sdb1

FRA                         ENABLED   /dev/sdc1


[ root@srv10:/root: ]$ asmcmd -p afd_state

ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'srv10.dbnitro.net'


[ root@srv10:/root: ]$ lsmod | egrep "oracle|asm|afd|acfs|advm"

oracleafd             221184  0


[ root@srv10:/root: ]$ acfsload start

ACFS-9391: Checking for existing ADVM/ACFS installation.

ACFS-9129: ADVM/ACFS not installed


# Cluster

[root@hostname1 ~]# $ORACLE_HOME/bin/crsctl start crs

CRS-4123: Oracle High Availability Services has been started.


[root@hostname2 ~]# $ORACLE_HOME/bin/crsctl start crs

CRS-4123: Oracle High Availability Services has been started.


[root@hostname1 ~]# crsctl check cluster -all

**************************************************************

hostname1:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

hostname2:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************


# Standalone

[ root@srv10:/root: ]$ crsctl start has

CRS-4123: Oracle High Availability Services has been started.



14. As the Disks are migrated to ASMFD, remove ASMLIB disk path from disk discovery string. Execute the following command as a Grid Software owner from any one of the node :-


[ +ASM ]|[ oracle@srv10:/home/oracle: ]$ asmcmd -p dsget

parameter:/dev/oracleasm/disks/*, AFD:*

profile:/dev/oracleasm/disks/*,AFD:*


[ +ASM ]|[ oracle@srv10:/home/oracle: ]$ asmcmd -p dsset 'AFD:*'


[ +ASM ]|[ oracle@srv10:/home/oracle: ]$ asmcmd -p dsget

parameter:AFD:*

profile:AFD:*



15.  As a Grid Software owner validate that all the diskgroups are mounted properly


[ +ASM ]|[ oracle@srv10:/home/oracle: ]$ asmcmd -p 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     51196    48820                0           48820              0             N  DATA/

MOUNTED  EXTERN  N         512             512   4096  4194304     51196    49576                0           49576              0             N  FRA/


[ +ASM ]|[ oracle@srv10:/home/oracle: ]$ asmcmd -p lsdsk -G DATA

Path

AFD:DATA


[ +ASM ]|[ oracle@srv10:/home/oracle: ]$ asmcmd -p lsdsk -G FRA

Path

AFD:FRA



16.  Lastly validate all the disks are using ASMFD Library :- (Login into ASM instance as sysasm) 


col PATH for a20

col LIBRARY for a45

col NAME for a15

select inst_id

  , group_number grp_num

  , name

  , state

  , header_status header

  , mount_status mount

  , path

  , library

from gv$asm_disk

order by inst_id, group_number, name;


         INST_ID|         GRP_NUM|NAME           |STATE   |HEADER      |MOUNT  |PATH                |LIBRARY

----------------|----------------|---------------|--------|------------|-------|--------------------|---------------------------------------------

               1|               1|DATA_0000      |NORMAL  |MEMBER      |CACHED |AFD:DATA            |AFD Library - Generic , version 3 (KABI_V3)

               1|               2|FRA_0000       |NORMAL  |MEMBER      |CACHED |AFD:FRA             |AFD Library - Generic , version 3 (KABI_V3)



17.  Check whether the ASMFD disk is accessible even by the root user also.

[ root@srv10:/root: ]$ kfed read /dev/oracleafd/disks/DATA

KFED-00303: unable to open file '/dev/oracleafd/disks/DATA'


18. Now the migration is completed and all the disks are using ASM Filter Driver.

Saturday, April 3, 2021

Create Standby Database one command only

Sometimes we just have to create a standby as far as possible, it doesn't means that we can't use the easier way to do that. 

Here is just some examples about that:


# ---------------------------------------------------------------------

# SINGLE Instance on ASM


dbca -silent \
 -createDuplicateDB \
 -gdbName dbprod \ # Source Database
 -primaryDBConnectionString srv01:1521/dbprod \ # Source Database

 -createAsStandby \
 -sid dbstby -\ # Target Database

 -dbUniqueName dbstby \ # Target Database

 -databaseConfigType single \ # Instance Creation Type
 -initParams db_create_file_dest=+DATA,db_recovery_file_dest=+FRA,dg_broker_start=true


# During this process, I found this error, when we create the standby with Grid Infrastructure the new instance is created as NON STANDBY, we need to change it using the following command.


srvctl modify database -db dbstby -role PHYSICAL_STANDBY -startoption MOUNT -stopoption immediate


# Also, pay attention on the status of your new instance, it is OPEN, but you must to shutdown and start it as mount before to start the standby configuration with or without Dataguard Broker.


# ---------------------------------------------------------------------

# SINGLE Instance on File System


dbca -silent \
 -createDuplicateDB \
 -gdbName dbprod \ # Source Database
 -primaryDBConnectionString srv01:1521/dbprod \ # Source Database

 -createAsStandby \
 -sid dbstby \ # Target Database
 -dbUniqueName dbstby \ # Target Database
 -databaseConfigType single \ # Instance Creation Type
 -initParams db_create_file_dest=/u01/oradata/dbprod,db_recovery_file_dest=/u01/oradata/dbprod/arch,dg_broker_start=true


# During this process, I found this error, when we create the standby with Grid Infrastructure the new instance is created as NON STANDBY, we need to change it using the following command


srvctl modify database -db dbstby -role PHYSICAL_STANDBY -startoption MOUNT -stopoption immediate


# Also, pay attention on the status of your new instance, it is OPEN, but you must to shutdown and start it as mount before to start the standby configuration with or without Dataguard Broker.


# ---------------------------------------------------------------------

# RAC Instance


dbca -silent \
 -createDuplicateDB \
 -gdbName dbprod \ # Source Database
 -primaryDBConnectionString srv01:1521/dbprod \ # Source Database

 -adminManaged \
 -createAsStandby \
 -sid dbstby \ # Target Database
 -dbUniqueName dbstby \ # Target Database
 -databaseConfigType rac \ # Instance Creation Type
 -nodelist srv04,srv05,srv06 \
 -initParams db_create_file_dest=+DATA,db_recovery_file_dest=+FRA,dg_broker_start=true


# During this process, I found this error, when we create the standby with Grid Infrastructure the new instance is created as NON STANDBY, we need to change it using the following command


srvctl modify database -db dbstby -role PHYSICAL_STANDBY -startoption MOUNT -stopoption immediate


# Also, pay attention on the status of your new instance, it is OPEN, but you must to shutdown and start it as mount before to start the standby configuration with or without Dataguard Broker.

Wednesday, December 9, 2020

DBCA Silent Mode for Container Database

# ---------------------------------------------------------------------
# glogin.sql
# ---------------------------------------------------------------------
# Modify the configuration of GLOGIN to show you better results.

cat >> $ORACLE_HOME/sqlplus/admin/glogin.sql <<EOF
set pages 700 lines 700 timing on time on colsep '|' trim on trims on numformat 999999999999999 heading on feedback on
COLUMN NAME FORMAT A20
COLUMN FILE_NAME FORMAT A80
SET SQLPROMPT '&_user@&_connect_identifier> '
DEFINE _EDITOR=vi
EOF

# ---------------------------------------------------------------------
# Database Creation Version 19c (With CDB/PDB on ASM Disks)
# SINGLE Create Database Silent on ASM and Container True

dbca -silent -createDatabase -templateName General_Purpose.dbc \
  -gdbName cdbprod \
  -sid cdbprod \
  -createAsContainerDatabase true \
  -databaseConfigType single \
  -SysPassword Welcome1 \
  -SystemPassword Welcome1 \
  -emConfiguration NONE \
  -storageType ASM \
  -datafileDestination +DATA \
  -redoLogFileSize 200 \
  -characterSet AL32UTF8 \
  -enableArchive true \
  -archiveLogMode auto \
  -recoveryAreaDestination +FRA \
  -memoryPercentage 50 \
  -dbOptions IMEDIA:false,CWMLITE:false,SAMPLE_SCHEMA:false,ORACLE_TEXT:false,APEX:false,OMS:false,DV:false,JSERVER:false,SPATIAL:false

# ---------------------------------------------------------------------
# This is to setup the password of your new CDP, necessary when you use DBCA Silent.
# ---------------------------------------------------------------------

orapwd dbuniquename=cdbprod file='+DATA' force=y entries=10 password="Welcome1+"

# ---------------------------------------------------------------------
# Validate the Database Creation
# ---------------------------------------------------------------------

srvctl config database -db cdbprod

ORACLE Configuration

# ------------------------------------------------------------------------
su - oracle

# ------------------------------------------------------------------------
# Version Variable
# ------------------------------------------------------------------------
#

ORA_VER_INST="11.2.0.4"
ORA_VER_INST="12.1.0.2"
ORA_VER_INST="12.2.0.1"
ORA_VER_INST="12.2.0.2"
ORA_VER_INST="18.1.0.1"
ORA_VER_INST="18.1.0.2"
ORA_VER_INST="18.2.0.1"
ORA_VER_INST="18.2.0.2"
ORA_VER_INST="18.3.0.1"
ORA_VER_INST="18.3.0.2"
ORA_VER_INST="18.4.0.1"
ORA_VER_INST="19.1.0.1"
ORA_VER_INST="19.2.0.2"
ORA_VER_INST="19.3.0.1"

# ------------------------------------------------------------------------
# Oracle Edition
# ------------------------------------------------------------------------
#

ORA_EDITION="db_SE_01"
ORA_EDITION="db_SE1_01"
ORA_EDITION="db_SE2_01"
ORA_EDITION="db_EE_01"
ORA_EDITION="db_PE_01"
ORA_EDITION="db_XE_01"

# ------------------------------------------------------------------------
# SRV01 - ORACLE
# ------------------------------------------------------------------------
#

cat > /home/oracle/.cdb1 <<EOF
# Variables Of Environment ORACLE [ ORACLE ]
export TMP=/tmp
export TMPDIR=\${TMP}
export HOST=\`hostname\`
export UPTIME=\`uptime | sed 's/.*up \([^,]*\), .*/\1/'\`
export ORACLE_HOSTNAME=\${HOST}
export ORACLE_BASE=/u01/app/oracle
export ORACLE_VERSION=${ORA_VER_INST}
export ORACLE_EDITION=${ORA_EDITION}
export ORACLE_HOME=\${ORACLE_BASE}/product/\${ORACLE_VERSION}/\${ORACLE_EDITION}
export GRID_HOME=/u01/app/\${ORACLE_VERSION}/grid
export ORACLE_TERM=xterm
export ORACLE_UNQNAME=cdbprod
export ORACLE_SID=cdbprod
export GRID_SID="+ASM"
export OGG_HOME=\${ORACLE_BASE}/product/ogg_19c
export TFA_HOME=\${ORACLE_HOME}/suptools/tfa/release/tfa_home
export OCK_HOME=\${ORACLE_HOME}/suptools/orachk
export OH=\${ORACLE_HOME}
export DBS=\${ORACLE_HOME}/dbs
export TNS=\${ORACLE_HOME}/network/admin
export OGG=\${OGG_HOME}
export TFA=\${TFA_HOME}
export OCK=\${OCK_HOME}
export ORATOP=\${ORACLE_HOME}/suptools/oratop/oratop
export OPATCH=\${ORACLE_HOME}/OPatch
export JAVA_HOME=\${ORACLE_HOME}/jdk
export PS1=\$'[ \${ORACLE_SID} ]|[ \${LOGNAME}@\h:\`pwd\`: ]\$ '
export PATH=\${PATH}:/usr/sbin:/usr/bin:/sbin:/bin:\${ORACLE_HOME}/bin:\${GRID_HOME}/bin:\${OPATCH}:\${ORACLE_HOME}/perl/bin:\${JAVA_HOME}/bin:\${OGG_HOME}:\${TFA_HOME}/bin:\${OCK_HOME}/
export LD_LIBRARY_PATH=\${ORACLE_HOME}/lib:\${ORACLE_HOME}/perl/lib:\${GRID_HOME}/lib:/lib:/usr/lib
export CLASSPATH=\${ORACLE_HOME}/JRE:\${ORACLE_HOME}/jlib:\${ORACLE_HOME}/rdbms/jlib
export ALERTDB=\${ORACLE_BASE}/diag/rdbms/\${ORACLE_UNQNAME}/\${ORACLE_SID}/trace/alert_\${ORACLE_SID}.log
export ALERTDG=\${ORACLE_BASE}/diag/rdbms/\${ORACLE_UNQNAME}/\${ORACLE_SID}/trace/drc\${ORACLE_SID}.log
export ALERTGG=\${OGG_HOME}/ggserr.log
export ALERTASM=\${ORACLE_BASE}/diag/asm/+asm/\${GRID_SID}/trace/alert_\${GRID_SID}.log
export LSNRCTL=\`ps -ef | grep tnslsnr | grep -v "grep" | wc -l\`
alias dblog='tail -f \${ALERTDB}'
alias dglog='tail -f \${ALERTDG}'
alias gglog='tail -f \${ALERTGG}'
alias asmlog='tail -f \${ALERTASM}'
alias sqlplus='rlwrap sqlplus'
alias s='rlwrap sqlplus / as sysdba'
alias rman='rlwrap rman'
alias r='rlwrap rman target /'
alias dgmgrl='rlwrap dgmgrl'
alias d='rlwrap dgmgrl /'
alias asmcmd='rlwrap asmcmd'
alias a='rlwrap asmcmd'
alias adrci='rlwrap adrci'
alias ad='rlwrap adrci'
alias ggsci='rlwrap \${OGG_HOME}/ggsci'
alias ogg='rlwrap \${OGG_HOME}/ggsci'
alias p='ps -ef | grep pmon | grep -v grep'
alias l='rlwrap lsnrctl status'
alias rest='crsctl stat res -t -init'
alias res='crsctl stat res -t'
alias orat='\${ORATOP}/oratop -f -i 10 / as sysdba'
alias meminfo='free -m -l -t'
alias psmem='ps auxf | sort -nr -k 4'
alias psmem10='ps auxf | sort -nr -k 4 | head -10'
alias pscpu='ps auxf | sort -nr -k 3'
alias pscpu10='ps auxf | sort -nr -k 3 | head -10'
alias cpuinfo='lscpu'
umask 0022
clear
if [[ \${ORACLE_SID} = \`ps -ef | grep pmon | grep -i \${ORACLE_SID} | awk '{ print \$NF }' | sed s/ora_pmon_//g\` ]]
then
  DB_STATUS="ONLINE"
else
  DB_STATUS="OFFLINE"
fi
if [ \${LSNRCTL} != 0 ]
then
  DB_LISTNER="ONLINE"
else
  DB_LISTNER="OFFLINE"
fi
echo ">----------------------------------------------------------------------------------------------"
echo "# SERVER_UPTIME.....: [ \${UPTIME} ]"
echo "# ORACLE_BASE.......: [ \${ORACLE_BASE} ]"
echo "# ORACLE_HOME.......: [ \${ORACLE_HOME} ]"
echo "# ORACLE_VERSION....: [ \${ORACLE_VERSION} ]"
echo "# ORACLE_SID........: [ \${ORACLE_SID} ]"
echo "# DATABASE_STATUS...: [ \${DB_STATUS} ]"
echo "# LISTENER_STATUS...: [ \${DB_LISTNER} ]"
echo ">----------------------------------------------------------------------------------------------"
EOF

grep -v '^#\|^$' /etc/oratab

# ------------------------------------------------------------------------
chown oracle.oinstall /home/oracle/.cdb1

echo ". /home/oracle/.cdb1" >> /home/oracle/.bash_profile

Tuesday, December 8, 2020

AFD / ACFS Configuration

# ------------------------------------------------------------------------
# AFD Manually Installation
# ------------------------------------------------------------------------
# AS ROOT

/u01/app/19.3.0.1/grid/bin/afdroot version_check      ---> only to enable acfs
/u01/app/19.3.0.1/grid/bin/afddriverstate supported   ---> only to enable acfs

# SINGLE RESTART
/u01/app/19.3.0.1/grid/bin/asmcmd afd_label DATA /dev/sdb --init
/u01/app/19.3.0.1/grid/bin/asmcmd afd_label FRA /dev/sdc --init

/u01/app/19.3.0.1/grid/bin/asmcmd afd_lslbl
/u01/app/19.3.0.1/grid/bin/asmcmd afd_lsdsk
/u01/app/19.3.0.1/grid/bin/asmcmd afd_state

# RAC
# /u01/app/19.3.0.1/grid/bin/asmcmd afd_label CONFIG /dev/sdb
/u01/app/19.3.0.1/grid/bin/asmcmd afd_label CONFIG /dev/sdb --init

# /u01/app/19.3.0.1/grid/bin/asmcmd afd_label DATA /dev/sdc
/u01/app/19.3.0.1/grid/bin/asmcmd afd_label DATA /dev/sdc --init

# /u01/app/19.3.0.1/grid/bin/asmcmd afd_label FRA /dev/sdd
/u01/app/19.3.0.1/grid/bin/asmcmd afd_label FRA /dev/sdd --init

/u01/app/19.3.0.1/grid/bin/asmcmd afd_lslbl
/u01/app/19.3.0.1/grid/bin/asmcmd afd_lsdsk
/u01/app/19.3.0.1/grid/bin/asmcmd afd_state

#
/u01/app/19.3.0.1/grid/bin/asmcmd lsdsk --statistics


# chown grid.asmadmin /dev/sdb
# chown grid.asmadmin /dev/sdc
# chown grid.asmadmin /dev/sdd

#
/u01/app/19.3.0.1/grid/crs/config/config.sh


# Clear

asmcmd afd_unlabel 'CONFIG'
asmcmd afd_unlabel 'DATA'
asmcmd afd_unlabel 'FRA'


disk /dev/sdb1 is already provisioned for ASM
disk /dev/sdc1 is already provisioned for ASM


dd if=/dev/zero of=/dev/sdb1 bs=1024 count=100
dd if=/dev/zero of=/dev/sdc1 bs=1024 count=100

# ------------------------------------------------------------------------
# Pre-Checks of Oracle Restart Installation
# ------------------------------------------------------------------------

./runcluvfy.sh stage -pre hacfg

GRID Configuration

# ------------------------------------------------------------------------
# ------------------------------------------------------------------------
# Version Variable
#

ORA_VER_INST="11.2.0.4"
ORA_VER_INST="12.1.0.2"
ORA_VER_INST="12.2.0.1"
ORA_VER_INST="12.2.0.2"
ORA_VER_INST="18.1.0.1"
ORA_VER_INST="18.1.0.2"
ORA_VER_INST="18.2.0.1"
ORA_VER_INST="18.2.0.2"
ORA_VER_INST="18.3.0.1"
ORA_VER_INST="18.3.0.2"
ORA_VER_INST="18.4.0.1"
ORA_VER_INST="19.3.0.1"

# ------------------------------------------------------------------------
# Oracle Edition
#

ORA_EDITION="db_SE_01"
ORA_EDITION="db_SE1_01"
ORA_EDITION="db_SE2_01"
ORA_EDITION="db_EE_01"
ORA_EDITION="db_PE_01"

# ------------------------------------------------------------------------
# Grid Folders
# ------------------------------------------------------------------------
#

mkdir -p /u01/app/grid
mkdir -p /u01/app/${ORA_VER_INST}/grid
mkdir -p /u01/app/oraInventory/
#
chown grid.oinstall -R /u01/app/grid
chown grid.oinstall -R /u01/app/${ORA_VER_INST}

#
# chown grid.oinstall /u01/app/oraInventory/

# ------------------------------------------------------------------------
# Database Folders
# ------------------------------------------------------------------------
#

mkdir -p /u01/app/oracle
mkdir -p /u01/app/oracle/product/${ORA_VER_INST}/${ORA_EDITION}

#
chown oracle.oinstall -R /u01/app/oracle
chown oracle.oinstall -R /u01/app/oracle/product/${ORA_VER_INST}
#
# chown oracle.oinstall /u01/app/oraInventory/
 

# ------------------------------------------------------------------------
# Configuration
# ------------------------------------------------------------------------

su - grid

# ------------------------------------------------------------------------
# Version Variable
#

ORA_VER_INST="11.2.0.4"
ORA_VER_INST="12.1.0.2"
ORA_VER_INST="12.2.0.1"
ORA_VER_INST="12.2.0.2"
ORA_VER_INST="18.1.0.1"
ORA_VER_INST="18.1.0.2"
ORA_VER_INST="18.2.0.1"
ORA_VER_INST="18.2.0.2"
ORA_VER_INST="18.3.0.1"
ORA_VER_INST="18.3.0.2"
ORA_VER_INST="18.4.0.1"
ORA_VER_INST="19.1.0.1"
ORA_VER_INST="19.2.0.2"
ORA_VER_INST="19.3.0.1"

# ------------------------------------------------------------------------
# Oracle Edition
#

ORA_EDITION="db_SE_01"
ORA_EDITION="db_SE1_01"
ORA_EDITION="db_SE2_01"
ORA_EDITION="db_EE_01"
ORA_EDITION="db_PE_01"
ORA_EDITION="db_XE_01"

#
# ------------------------------------------------------------------------
# SRV01 - GRID
# ------------------------------------------------------------------------
#

cat > /home/grid/.asm <<EOF
# Variables Of Environment ORACLE [ GRID ]
export TMP=/tmp
export TMPDIR=\${TMP}
export HOST=\`hostname\`
export HOSTNAME=\`hostname -a\`
export UPTIME=\`uptime | sed 's/.*up \([^,]*\), .*/\1/'\`
export ORACLE_HOSTNAME=\${HOST}
export ORACLE_BASE=/u01/app/grid
export ORACLE_VERSION=${ORA_VER_INST}
export GRID_HOME=/u01/app/\${ORACLE_VERSION}/grid
export ORACLE_HOME=\${GRID_HOME}
export OH=\${ORACLE_HOME}
export DBS=\${ORACLE_HOME}/dbs
export TNS=\${ORACLE_HOME}/network/admin
export ORACLE_SID=+ASM
export ORACLE_TERM=xterm
export OPATCH=\${ORACLE_HOME}/OPatch
export JAVA_HOME=\${ORACLE_HOME}/jdk
export PS1=\$'[ \${ORACLE_SID} ]|[ \${LOGNAME}@\h:\`pwd\`: ]\$ '
export PATH=\${PATH}:/usr/sbin:/usr/bin:/sbin:/bin:\${ORACLE_HOME}/bin:\${GRID_HOME}/bin:\${OPATCH}:\${ORACLE_HOME}/perl/bin:\${JAVA_HOME}/bin
export LD_LIBRARY_PATH=\${ORACLE_HOME}/lib:\${ORACLE_HOME}/perl/lib:\${GRID_HOME}/lib:/lib:/usr/lib
export CLASSPATH=\${ORACLE_HOME}/JRE:\${ORACLE_HOME}/jlib:\${ORACLE_HOME}/rdbms/jlib
export ALERTASM=\${ORACLE_BASE}/diag/asm/+asm/\${ORACLE_SID}/trace/alert_\${ORACLE_SID}.log
export LSNRCTL=\`ps -ef | grep tnslsnr | grep -v "grep" | wc -l\`
alias asmlog='tail -f \${ALERTASM}'
alias sqlplus='rlwrap sqlplus'
alias s='rlwrap sqlplus / as sysasm'
alias rman='rlwrap rman'
alias r='rlwrap rman target /'
alias asmcmd='rlwrap asmcmd -p'
alias a='rlwrap asmcmd -p'
alias adrci='rlwrap adrci'
alias ad='rlwrap adrci'
alias p='ps -ef | grep pmon | grep -v grep'
alias l='rlwrap lsnrctl status'
alias rest='crsctl stat res -t -init'
alias res='crsctl stat res -t'
alias wr='watch -d crsctl stat res -t'
alias meminfo='free -m -l -t'
alias psmem='ps auxf | sort -nr -k 4'
alias psmem10='ps auxf | sort -nr -k 4 | head -10'
alias pscpu='ps auxf | sort -nr -k 3'
alias pscpu10='ps auxf | sort -nr -k 3 | head -10'
alias cpuinfo='lscpu'
umask 0022
clear
if [[ \${ORACLE_SID} = \`ps -ef | grep pmon | grep -i \${ORACLE_SID} | awk '{ print \$NF }' | sed s/asm_pmon_//g\` ]]
then
  DB_STATUS="ONLINE"
else
  DB_STATUS="OFFLINE"
fi
if [ \${LSNRCTL} != 0 ]
then
  DB_LISTNER="ONLINE"
else
  DB_LISTNER="OFFLINE"
fi
echo ">----------------------------------------------------------------------------------------------"
echo "# SERVER_UPTIME.....: [ \${UPTIME} ]"
echo "# ORACLE_BASE.......: [ \${ORACLE_BASE} ]"
echo "# ORACLE_HOME.......: [ \${ORACLE_HOME} ]"
echo "# ORACLE_VERSION....: [ \${ORACLE_VERSION} ]"
echo "# ORACLE_SID........: [ \${ORACLE_SID} ]"
echo "# DATABASE_STATUS...: [ \${DB_STATUS} ]"
echo "# LISTENER_STATUS...: [ \${DB_LISTNER} ]"
echo ">----------------------------------------------------------------------------------------------"
EOF

# ------------------------------------------------------------------------
# Configure the Environment of Grid per File
# ------------------------------------------------------------------------
chown grid.oinstall /home/grid/.asm
echo ". /home/grid/.asm" >> /home/grid/.bash_profile