Category Archives: oracle

Install Oracle 12c

  1. Download MobaXterm or use putty
  2. Set up EC2 instance using OL7.2-x86_64-HVM-2015-12-10 Amazon Machine Image (AMI) with three additional volumes of 5, 8 and 10 gb (this will make your life easier). I use additional sizes to accommodate increase in volume workload.
  3. When setting up on EC2 the base use to login with is EC2
  4. Login via MobaXterm as ec2-user and change password
    • sudo passwd ec2-user
  5. Install packages needed for instance
    • sudo yum install wget zip unzip -y
    • sudo yum install perl-libwww-perl.noarch -y
    • sudo yum install oracle-rdbms-server-12cR1-preinstall -y
    • sudo yum install oracle-database
  6. Mount volumes, format disks, mkdir
    1. sudo mkfs -t ext4 /dev/xvdb (be super careful here)
    2. sudo mkfs -t ext4 /dev/xvdc (be super careful here)
    3. sudo mkdir -p /swapfile1
    4. sudo mount /dev/xvdb /swapfile1 (swap file)
    5. sudo mkdir -p /u01/software
    6. sudo mount /dev/xvdf /u01/software (zip files)
    7. sudo mkdir -p /u01/app/oracle/oradata/orcl
    8. sudo mount /dev/xvdc /u01/app/oracle/oradata/orcl (data files)
  7. Add swap file
    • sudo dd if=/dev/zero of=/swapfile1/swapfile  bs=1024 count=3145728 (creates 3G swapfile)
    • sudo chown root:root /swapfile1/swapfile
    • sudo chmod 0600 /swapfile1/swapfile
    • sudo  mkswap /swapfile1/swapfile
    • sudo swapon /swapfile1/swapfile
    • free -m
  8. Make fstab entries
    • lsblk (gives names of volumes)
    • sudo cp /etc/fstab /etc/fstab.orig (back up fstab)
    • sudo vi /etc/fstab
    • /swapfile1 none swap sw 0 0
    • /dev/xvdc /u01/app/oracle/oradata/orcl ext4 defaults 0 0
    • /dev/xvdf /u01/software ext4 defaults 0 0
    • sudo mount -a (remount everything to make sure it worked)
  9.  Give oracle user ownership of directories
    • sudo chown -R oracle.oinstall /u01/app/oracle/oradata/orcl
    • sudo chown -R oracle.oinstall  /u01/software
    • sudo chown -R oracle.oinstall /u01
  10. Change hostfile for instance by adding hostname to localhost
    • hostname
    • sudo vi /etc/hosts
  11. Change password for oracle user and make it possible for user to connect remotely (Managing User Accounts on Your Linux Instance)
    • sudo passwd oracle
    • su oracle (switch to oracle user)
    • cd ~(make sure your are in oracle user home)
    • mkdir .ssh (create location for key file)
    • chmod 700 .ssh (set permissions)
    • touch .ssh/authorized_keys (create file)
    • chmod 600 .ssh/authorized_keys (set permissions)
    • “GET http://169.254.169.254/latest/meta-data/public-keys/0/openssh-key>.ssh/authorized_keys” (copy public key to file)
    • log out and login as oracle user
  12. Upload database zip files into /u01/software (this takes a while)
  13. Unzip files
    • cd /u01/software
    • unzip linuxamd64_12102_database_1of2.zip
    • unzip linuxamd64_12102_database_2of2.zip
  14. Start install
    • cd /ora_software/database
    • ./runInstaller
  15. Run scripts as root (careful here you need to open separate instance of MobaXterm)
    • sudo /u01/app/oraInventory/orainstRoot.sh
    • sudo /u01/app/oracle/product/12.1.0/dbhome_1/root.sh
  16. Say OK to run dbca to create a database
  17. Update tnsnames.ora, listener.ora files by replacing localhost with actual host name
    • cd /u01
    • find -name tnsnames.ora
    • find -name listener.ora
    • hostname
    • vi ./app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
    • vi ./app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  18. Restart listener and database
    • . oraenv
    • lsnrctl stop
    • lsnrctl start
    • sqlplus sys as sysdba
    • shutdown immediate;
    • startup;
  19. At this point you should be able to connect to your database remotely with SQL Developer or your favorite tool, remember if you are shutting down your EC2 instance to first stop the listener and the database.

profile_env

#############################################################################
#   FILENAME: .profile_env
#   PURPOSE: Custom environment variables or additions to the PATH may be
#            placed here.  You can edit anything in this file, this file is
#            used to customize the environment.
#############################################################################
#
#
#  Revision History:
#
#
#
#  REV   DATE         BY        DESCRIPTION
#
#  ---  ------  --------------  -------------------------------------------
#
#
# #############################################################################
# You may change any of these values as needed for your environment
#
# /orgtools = can be setup for a standard place to put your organizations software tools

export SUNWspro=/opt/SUNWspro
# C compiler directory
# export MAIL=/var/mail/oracle
# Location of oracle mail file
# export MAILMSG="You have mail."
# Mail Message to display
# export PBROKER=/orgtools/pbrun/3.2/bin
# Location of PowerBroker
#
#export FGLHOME=/oracle/dba/foglight/4.2
# Current version of Foglight
# export FGL_DIR=/oracle/dba/foglight/fgl_5/bin
# export FGL_STOP_CMD="stop.sh"
# export FGL_START_CMD="start.sh -d"
#

PATH=/opt/VRTSvmsa/bin/vmsa:$PBROKER
# Additional PATH locations
# umask 022
# Default permissions 744
#
# ulimit -n 1024
# Number files opened at once
#
# resize
# V1.2 Issue a "resize" if needed
#
#############################################################################
# Default ORACLE_SID.  CHANGE THIS VALUE BELOW
#
# For application servers with no database, set to "no_db" and put an entry
#
#############################################################################
#                                                                        
# #   FILENAME: .profile_env                                            
# #                                                                           
# #   PURPOSE: Custom environment variables or additions to the PATH may be   
# #       placed here.  You can edit anything in this file, this file is 
# #            used to customize the environment.                         
# #                                                                           
# ############################################################################# #                                                                           
# #  Revision History:                                                        
# #                                                                           
# #  REV   DATE         BY        DESCRIPTION                                 
# #  ---  ------  --------------  ------------------------------------------- 
# 
#                                                                           
# ############################################################################# 
# You may change any of these values as needed for your environment         
#

export SUNWspro=/opt/SUNWspro             
# C compiler directory            
# export MAIL=/var/mail/oracle              
# Location of oracle mail file    
# export MAILMSG="You have mail."           
# Mail Message to display         
# export PBROKER=/banktools/pbrun/3.2/bin   
# Location of PowerBroker         
# 
#export FGLHOME=/oracle/dba/foglight/4.2  
# Current version of Foglight     
# export FGL_DIR=/oracle/dba/foglight/fgl_5/bin                               
# export FGL_STOP_CMD="stop.sh"                                               
# export FGL_START_CMD="start.sh -d"                                          
#

PATH=/opt/VRTSvmsa/bin/vmsa:$PBROKER    
# Additional PATH locations         
# umask 022                               
# Default permissions 744           
# 
#ulimit -n 1024                         
# Number files opened at once       
# 
# resize                                
# V1.2 Issue a "resize" if needed   
#

############################################################################# 
# Default ORACLE_SID.  CHANGE THIS VALUE BELOW                              
# 
# For application servers with no database, set to "no_db" and put an entry 
# 
#   as follows in your oratab:  no_db:<oracle_path>:N                       
#

if [[ ${NEW_ORACLE_SID:-0} = 0 ]];
then
   # If NEW_ORACLE_SID is not set
   export NEW_ORACLE_SID=SAMPLE_SID
# Default ORACLE_SID 
fi 
#############################################################################

# You may add any other custom variables to this section as needed.         
# 
# Be aware that .profile WILL override if you choose the same variable names#

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' ############################################################################# # This was set in the .std_profile but is commented out there in favor of here export ORACLE_BASE=/oracle

export RSP=/oracle/admin/scripts/rspfiles export TMPLT=/oracle/product/11.2.0.3/db_2/assistants/dbca/templates export ADMSCR=/oracle/admin/scripts export DIAG=/oracle/diag export TRCOUT=/oracle/diag/rdbms/dpodst01/DPODST011/trace alias admscr='cd $ADMSCR' alias tmplt='cd $TMPLT' alias rsp='cd $RSP' alias trcout='cd $TRCOUT'

alias gi='cd /oracle_crs/product/11.2.0/crs_1/log/$SNAME' 
alias ocssd='cd /oracle_crs/product/11.2.0/crs_1/log/$SNAME/cssd' 
alias crsd='cd /oracle_crs/product/11.2.0/crs_1/log/$SNAME/crsd'
alias vial='vi $ADMIN/$DB_NAME/bdump/alert_$DB_NAME.log' 
alias diag='cd $DIAG/rdbms/$DBNAME'

alias lv='ls -v ' 
alias rd='rmdir ' 
alias md='mkdir ' 
alias pg='less -EF ' 
alias pgn='less -EFN '

######### GOLDEN GATE SETUP #######################

export GGS_ORA_HOME=/ggate/ggs
ulimit -c unlimited
ulimit -s unlimited

######### Last Statement of: .profile_env #########

Backup Options 12c

Making Compressed Backups

For any use of the BACKUP command that creates backup sets, you can take advantage of RMAN support for binary compression of backup sets. Specify the AS COMPRESSED BACKUPSET option to the BACKUP command.

RMAN compresses the backup set contents before writing them to disk. The details of which binary compression level is used are automatically recorded in the backup set. There is no need to explicitly mention the type of compression used or how to decompress the backup set in the recovery operation.

Binary compression creates some performance overhead during backup and restore operations. Binary compression consumes CPU resources, so do not routinely schedule compressed backups when CPU usage is high. However, the following circumstances may warrant paying the performance penalty:

AMM

The  buffer exterminate wait event is caused when using Oracle’s “automatic memory management” (AMM) when the MMON process shrinks the data buffer cache to re-allocate that RAM for another SGA region. AMM resize operations can hurt overall database performance especially the OLTP environments, and you may want to consider turning off AMM which will relieve the buffer exterminate waits, and manually adjust your SGA regions.

If you suspect AMM resize operations, you have to look into v$sga_resize_ops and v$memory_resize_ops and see how many times it is occurring and effecting the performance of your database. If one sees more events of these and especially during your peak times of database one has to turn of the feature adjusting manually the corresponding SGA and PGA sizes.

If you want to analyze Oracle’s use of memory and look at various memory resizing operations you can use the v$memory_resize_ops view. This view contains a list of the last 800 SGA resize requests handled by Oracle. Here is an example:

select parameter
      ,initial_size
      ,target_size
      ,start_time
 from
   v\$memory_resize_ops
where initial_size > = 0
  and final_size > = 0
order by
parameter,
start_time;

Please find below the various tables that are related to AMM and their descriptions to check the information in the database.

v$memory_dynamic_components – displays information on the current size of all automatically tuned and static memory components, with the last operation (for example, grow or shrink) that occurred on each.

v$sga_dynamic_components — displays the current sizes of all SGA components, and the last operation for each component.

v$sga_dynamic_free_memory — displays information about the amount of SGA memory available for future dynamic SGA resize operations.

v$memory_current_resize_ops — displays information about resize operations that are currently in progress. a resize operation is an enlargement or reduction of the SGA, the instance pPGAga, or a dynamic SGA component.

v$sga_current_resize_ops — displays information about dynamic SGA component resize operations that are currently in progress.

v$memory_resize_ops — displays information about the last 800 completed memory component resize operations, including automatic grow and shrink operations for sga_target and pga_aggregate_target.

v$sga_resize_ops — Displays information about the last 800 completed SGA component resize operations.