Saturday, April 5, 2014

Grid Infrastructure upgrade to 11.2.0.4 on AIX - always read log files

Oracle Restart 11.2.0.3 and same version of database has to be upgraded into 11.2.0.4. Looks like quite easy task but I hit some problems with it.

I had 31.9 GB free and OUI through error about disk space. Fair enough cause it also proposed a parameter to ignore it. Current version of GI (11.2.0.3) occupied around 25 GB so i though it will be OK to use a parameter.

-bash-3.2$ ./runInstaller -silent -responseFile /software/oracle/database/aix/grid_reponse_file_11204.rsp \
-waitforcompletion -ignoreDiskWarning
********************************************************************************

Your platform requires the root user to perform certain pre-installation
OS preparation.  The root user should run the shell script 'rootpre.sh' before
you proceed with Oracle installation.  rootpre.sh can be found at the top level
of the CD or the stage area.

Answer 'y' if root has run 'rootpre.sh' so you can proceed with Oracle
installation.
Answer 'n' to abort installation and then ask root to run 'rootpre.sh'.

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

Has 'rootpre.sh' been run by root on all nodes? [y/n] (n)
y

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 190 MB.   Actual 1265 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 16512 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-04-01_08-46-59AM. Please wait ...[WARNING] [INS-13014] Target environment do not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/installActions2014-04-01_08-46-59AM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/installActions2014-04-01_08-46-59AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2014-04-01_08-46-59AM.log
You may not have enough space on your drive for a successful install. Do you still want to continue?
y
The installation of Oracle Grid Infrastructure 11g was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2014-04-01_08-46-59AM.log' for more details.

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


As install user, execute the following script to complete the configuration.
        1. /u01/app/grid/product/11.2.0/grid4/cfgtoollogs/configToolAllCommands RESPONSE_FILE=

        Note:
        1. This script must be run on the same host from where installer was run.
        2. This script needs a small password properties file for configuration assistants that require passwords (refer to install guide documentation).


Successfully Setup Software.

Actually note to myself - CHECK LOGS - especially oraInstall.err and silentInstall.log and to not trust OUI that installation was successful.
In next step I run I run rootupgrade but there was execution permissions errors for these files: rootadd_rdbms.sh, rootadd_filemap.sh, setowner.sh
/u01/app/grid/product/11.2.0/grid4/rootupgrade.sh[8]: /u01/app/grid/product/11.2.0/grid4/network/install/sqlnet/setowner.sh: 0403-006 Execute permission denied.
/u01/app/grid/product/11.2.0/grid4/rootupgrade.sh[13]: /u01/app/grid/product/11.2.0/grid4/rdbms/install/rootadd_rdbms.sh: 0403-006 Execute permission denied.
/u01/app/grid/product/11.2.0/grid4/rootupgrade.sh[15]: /u01/app/grid/product/11.2.0/grid4/rdbms/install/rootadd_filemap.sh: 0403-006 Execute permission denied.
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid4/crs/install/crsconfig_params
Creating trace directory

I checked it on OS and well it was true
-bash-3.2$ ls -l /u01/app/grid/product/11.2.0/grid4/rdbms/install/rootadd_rdbms.sh
-rw-r--r--    1 grid     oinstall       2999 Apr 01 09:24 /u01/app/grid/product/11.2.0/grid4/rdbms/install/rootadd_rdbms.sh
-bash-3.2$ ls -l /u01/app/grid/product/11.2.0/grid4/rdbms/install/rootadd_filemap.sh
-rw-r--r--    1 grid     oinstall       1105 Apr 01 09:31 /u01/app/grid/product/11.2.0/grid4/rdbms/install/rootadd_filemap.sh
-bash-3.2$ ls -l /u01/app/grid/product/11.2.0/grid4/network/install/sqlnet/setowner.sh
-rw-r--r--    1 grid     oinstall        341 Apr 01 09:31 /u01/app/grid/product/11.2.0/grid4/network/install/sqlnet/setowner.sh

OK - maybe I could fix it but at this time I checked error log and it was much more errors
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp100. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp101. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp102. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp103. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp104. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp105. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp106. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp107. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp108. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp109. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp110. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp111. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp112. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp113. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp114. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp115. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp116. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp117. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp118. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp119. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp120. Please create more space at this location and try again.
oracle.sysman.oii.oiif.oiifb.OiifbCancelException: OUI-10096:You may not have enough space to write to /tmp/OraInstall2014-04-01_08-46-59AM/dtemp121. Please create more space at this location and try again.
java.io.FileNotFoundException: /tmp/OraInstall2014-04-01_08-46-59AM/setpermslog.err (There is not enough space in the file system.)
 at java.io.FileOutputStream.open(Native Method)
 at java.io.FileOutputStream.(FileOutputStream.java:205)
 at java.io.FileOutputStream.(FileOutputStream.java:96)
 at oracle.sysman.oii.oiij.OiijFastCopyPhase.execPermissions(OiijFastCopyPhase.java:1245)
 at oracle.sysman.oii.oiij.OiijFastCopyPhase.doFastModeOperations(OiijFastCopyPhase.java:542)
 at oracle.sysman.oii.oiif.oiifw.OiifwInstallPhaseWCCE$OiifwComponentIP1WCCE.doOperation(OiifwInstallPhaseWCCE.java:1980)
 at oracle.sysman.oii.oiif.oiifb.OiifbCondIterator.iterate(OiifbCondIterator.java:171)
 at oracle.sysman.oii.oiif.oiifw.OiifwInstallPhaseWCCE.doOperation(OiifwInstallPhaseWCCE.java:897)
 at oracle.sysman.oii.oiif.oiifb.OiifbCondIterator.iterate(OiifbCondIterator.java:171)
 at oracle.sysman.oii.oiif.oiifw.OiifwActionsPhaseWCDE.doOperation(OiifwActionsPhaseWCDE.java:641)
 at oracle.sysman.oii.oiif.oiifb.OiifbLinearIterator.iterate(OiifbLinearIterator.java:147)
 at oracle.sysman.oii.oiic.OiicInstallAPISession$OiicAPISelCompsInstall.doOperation(OiicInstallAPISession.java:1095)
 at oracle.sysman.oii.oiif.oiifb.OiifbCondIterator.iterate(OiifbCondIterator.java:171)
 at oracle.sysman.oii.oiic.OiicInstallAPISession.doInstallAction(OiicInstallAPISession.java:679)
 at oracle.sysman.oii.oiic.OiicInstallAPISession.access$000(OiicInstallAPISession.java:94)
 at oracle.sysman.oii.oiic.OiicInstallAPISession$OiicActionsThread.run(OiicInstallAPISession.java:971)
Strange enough there were errors related to /tmp space not a GRID HOME location - I have verified all logs files again:
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 190 MB.   Actual 1265 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 16512 MB    Passed
So far looks OK but
Free Space: itd11:/u01/app/grid/product/11.2.0/grid4: This is a prerequisite condition to test whether sufficient free space is available in the file system.
INFO: Severity:IGNORABLE
INFO: OverallStatus:SUCCESSFUL
INFO: -----------------------------------------------
INFO: Verification Result for Node:itd11
INFO: Expected Value:12GB 
INFO: Actual Value:30.5491GB 
INFO: -----------------------------------------------
INFO: *********************************************
INFO: Free Space: itd11:/tmp: This is a prerequisite condition to test whether sufficient free space is available in the file system.
INFO: Severity:IGNORABLE
INFO: OverallStatus:VERIFICATION_FAILED
INFO: -----------------------------------------------
INFO: Verification Result for Node:itd11
INFO: Expected Value:1GB 
INFO: Actual Value:915.3164MB 

OK there is 85 MB MB shortage in /tmp filesystem, cause some of space has been allocated already by OUI itself. I can't complain about disk error problem as I forced OUI to ignore it but I would really appreciate if OUI doesn't display successful feedback when something went wrong ;)



I do a proper cleanup of /tmp and re-run process


Note to myself : Remember do not trust output of command and always read logs


PS.
That remind me one guy I used to know who work as developer. By default all his programs end up with exit(0) C function so if you check error code there was not errors at all.

regards,
Marcin

Wednesday, March 19, 2014

Automated Linux VM build on ESX


How to automatically install RedHat like (Red Hat, Cent OS, Oracle Enterprise Linux) Linux system on ESX or VirtualBox servers ? There are at least two methods. I have seen VM cloning in lot of places using ESX (VSphere Center) or VirtualBox functionality. Cloning is fine but it required some intervention after clone will be finished (like host rename or IP address change). What if we want to install different releases of systems - well it is required to have a clone of every single release like RedHat 6.2, OEL 6.3 or OEL 6.4. It require a rework every time a new release is available on the market.

But there is a another method - developed years ago well before virtualization era. It is based on installation answer file and KickStart installation method. If I add DHCP and TFTP and NFS server to this equation I will get nice and quite configurable way to build my VMs very fast.

First of all DHCP server has to be configured. In my case I just created a internal network inside ESX and setup an additional DHCP sever for build purpose only. You can use any DHCP server but it has to be able to support TFTP redirection and also booting functionality. In my base I'm using DHCP, TFTP and NFS server provided by Ubuntu 13.04

DHCP configuration
root@myown:~# cat /etc/dhcp/dhcpd.conf

ddns-update-style none;

# option definitions common to all supported networks...
option domain-name "priv.localdomain";

default-lease-time 600;
max-lease-time 7200;

option subnet-mask 255.255.255.0;
option broadcast-address 10.0.0.255;
option domain-name-servers 192.168.1.20;

subnet 10.0.0.0 netmask 255.255.255.0 {
range dynamic-bootp 10.0.0.1 10.0.0.100;
filename  "pxelinux.0";
next-server  10.0.0.250;
}

PXELINUX.0 has to be copied from default location into TFTP directory and it has to be accessible by it. You can find "pxelinux.0" file in Ubuntu syslinux-common package.
Install it using "apt-get install syslinux-common" and then copy from it's default location into /tftpboot
root@myown:~# cp /usr/lib/syslinux/pxelinux.0 /tftpboot/

TFTP configuration - default port and /tftpboot used as files location
pioro@myown:/etc$ cat /etc/xinetd.d/tftp
service tftp
{
protocol        = udp
port            = 69
bind            = 10.0.0.250
socket_type     = dgram
wait            = yes
user            = nobody
server          = /usr/sbin/in.tftpd
server_args     = /tftpboot
disable         = no
}

TFTP directory structure
 
pioro@myown:/tftpboot$ ls -l
total 37816
-rw-r--r-- 1 root root 34551609 Dec 22 17:04 initrd.img
-rw-r--r-- 1 root root    26461 Dec 22 17:26 pxelinux.0
drwxr-xr-x 2 root root     4096 Jan 13 22:02 pxelinux.cfg
-r--r--r-- 1 root root      441 Dec 22 17:04 TRANS.TBL
-rwxr-xr-x 1 root root  4128944 Dec 22 17:04 vmlinuz
PXELINUX.CFG directory inside TFTP directory
pioro@myown:/tftpboot/pxelinux.cfg$ ls -l
total 8
-rw-r--r-- 1 root root 137 Dec 22 18:29 01-00-0c-29-41-69-15
-rw-r--r-- 1 root root 138 Jan 13 21:55 01-00-0c-29-99-7d-3d
Files names are related to NIC MAC addresses. For example: 01-00-0c-29-41-69-15 is first file for MAC address 00:0c:29:41-69:15
Now take a look what is inside a host configuration file

pioro@myown:/tftpboot/pxelinux.cfg$ cat 01-00-0c-29-41-69-15
default Oracle Linuxas_64
label Oracle Linuxas_64
kernel vmlinuz
append initrd=initrd.img ks=nfs:10.0.0.250:/images/ks.cfg ksdevice=eth1
This are boot properties created using a GRUB style and describing a kernel and initrd images. KS parameter is a configuration parameter of KickStart process. In the above example KickStart configuration file is placed on NFS server in /images directory and it's called ks.cfg. In addition to that KickStart will configure interface eth1 which is private ESX network in my case.
My DHCP and TFTP server has NFS server configured as well. It's exporting only one directory /images which keep a KickStart configuration files and also is a mount point for ISO image.
pioro@myown:/tftpboot/pxelinux.cfg$ cat /etc/exports
/images/        *(ro,subtree_check,crossmnt)
ISO with Linux distribution should be mounted below /images directory using loop option.
root@myown:~# mount -o loop /nfs/disk2/images/OEL65.iso /images/OEL65/
mount: block device /nfs/disk2/images/OEL65.iso is write-protected, mounting read-only
Now I have a access to installation files and also to PXE boot files. In my case all have been located in the following directory /images/OEL65/images/pxeboot/ and I just copied it into TFTP /tftpboot directory
root@myown:~# ls -l /images/OEL65/images/pxeboot/
total 37775
-rw-r--r-- 2 root root 34551609 Nov 26 05:02 initrd.img
-r--r--r-- 1 root root      441 Nov 26 05:04 TRANS.TBL
-rwxr-xr-x 2 root root  4128944 Nov 26 05:02 vmlinuz

root@myown:~# cp /images/OEL65/images/pxeboot/* /tftpboot/
Inside NFS exported directory I have also KickStart configuration files
pioro@myown:/images$ ls -l
total 12
-rw-r--r-- 1 root root 1935 Jan 13 21:59 ks2.cfg
-rw-r--r-- 1 root root 1936 Jan 13 22:00 ks.cfg
drwxr-xr-x 2 root root 4096 Dec 22 18:27 OEL65

Example Kickstart configuration file
pioro@myown:/images$ cat dg1.cfg
#platform=x86, AMD64, or Intel EM64T
#version=DEVEL
# Firewall configuration
firewall --disabled
# Install OS instead of upgrade
install
# Use CDROM installation media
nfs --server 10.0.0.250 --dir /images/OEL65/
#cdrom
# Root password
rootpw --plaintext 
# System authorization information
auth  --useshadow  --passalgo=sha512
# Use graphical install
graphical
firstboot --disable
# System keyboard
keyboard us
# System language
lang en_US
# SELinux configuration
selinux --disabled
# Installation logging level
logging --level=info
# Reboot after installation
reboot
# System timezone
timezone  Europe/Dublin
# Network information
network  --bootproto=static --device=eth0 --ip=192.168.1.51 --nameserver=192.168.1.20 --netmask=255.255.255.0 --onboot=on --hostname=dg1.localhost
network  --bootproto=static --device=eth1 --ip=10.0.0.1 --netmask=255.255.255.0 --onboot=on
# network  --bootproto=dhcp --device=eth2 --onboot=on
# System bootloader configuration
bootloader --location=mbr
# Clear the Master Boot Record
zerombr
# Partition clearing information
clearpart --all
# Disk partitioning information
part /boot --fstype ext4 --size=200
part pv.01 --size=1 --grow
volgroup VolGroup pv.01
logvol swap --fstype swap --vgname=VolGroup --size=1024 --name=lv_swap
logvol  /  --fstype ext4 --vgname=VolGroup  --size=1  --grow --name=lv_root


%packages
@base
@console-internet
@core
@debugging
@directory-client
@hardware-monitoring
@large-systems
@network-file-system-client
@performance
@perl-runtime
@security-tools
@server-platform
@server-policy
@system-admin-tools
gcc

%end 

Above configuration file will partition a disk into root and swap partition and configure two networks. In addition to that packages groups specified after %packages line will be installed.

Below are screen shots from my ESX environment:

Finding a MAC address of VM - Open VM configuration and go to Network adapters


Booting process - this VM is booting from NIC 2 using private network and all services configured above.


If you looking to step by step instructions you can find it on Tim Hall website:
PXE Network installations 
KickStart

I based my environment build on Tim's website and some Google research. 
Happy installing !!!

regards,
Marcin

Friday, October 25, 2013

OraSASH visualization


Hello,

I started working on that project 2 years ago and at that stage I created a JavaScript code to display real time graphs and parse JSON code. The backend providing JSON data from Oracle Database was based on PHP and I didn't like that solution as it required to much configuration so project has been postponed.

Couple of weeks ago I discovered NodeJS and I decided to come back to my old project and this time use this lightweight server to work as backend server. I couldn't find NodeJS database driver for Oracle but I decide to present performance data using DBMS_EPG and PL/SQL procedures. Data are taken from OraSASH (but it can be used for ASH/AWR as well) and encoded as JSON. Browser is using AJAX to call NodeJS and NodeJS is connecting to Oracle DB to take required data.

This project is on very early stage so stay tuned - you can find initial code here

Please find screen shots from VISASH project:


One instance view 



Two instances view

regards,
Marcin

 

 

New release candidate of OraSASH

Hi,

There is a new release candidate of OraSASH 2.4 available on github - https://github.com/pioro/orasash/tree/v2.4-rc2

This version is providing much more views and it's easier to use your existing AWR queries against OraSASH. 

regards,
Marcin 



Tuesday, July 30, 2013

How to improve VMware performance on your PC

I found very interesting post here - VMware Performance Enhancing Tweaks (Over-the-Counter Solutions). I used it on my VMware Player and Workstation and when I disabled vmem file my laptop it stopped blinking HDD led for 5 - 10 min every time it wake up from sleep mode.

regards,
Marcin

Sunday, July 28, 2013

DataGuard – Far Sync – part 2 - Data Guard Broker

Oracle introduced Far Sync Data Guard configuration which I described briefly in this post. Now is time for part two and using Data Guard Broker to add Far Sync instance.
Assuming that you have basic Data Guard Broker configuration ready (as described in - How to quickly build standby database and setup DataGuard configuration using Oracle 12c) adding new Far Sync instance is quite easy task.

First we have to setup a new instance which will be used as Far Sync for our primary database.

Instance parameter file - as a copy of primary database configuration (not necessary)
*.audit_file_dest='/home/oracle/admin/test12c/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/test12c/control01.ctl','/u01/app/oracle/oradata/test12c/control02.ctl','/u01/app/oracle/oradata/test12c/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test12c'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test12cXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/test12c/arch'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=450m
*.undo_tablespace='UNDOTBS1'
# for far sync
*.db_unique_name='test12c_far_sync'
*.LOG_FILE_NAME_CONVERT='test','test'
*.local_listener='(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.60)(PORT = 1522)))'
There are three parameters which have to be changed:
  • db_unique_name 
  • local_listener - new instance has to be registered in proper listener
  • log_file_name_convert - it is useful to have even with dummy values so Oracle can recreate standby logs automatically
Listener configuration
SID_LIST_LISTENER_DG =
 (SID_LIST =
   (SID_DESC=
       (ORACLE_HOME= /u01/app/oracle/product/12.1.0/db1)
       (SID_NAME=test12c)
   )
   (SID_DESC =
      (SID_NAME = test12c)
      (GLOBAL_DBNAME = test12c_far_sync_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db1)
   )
 )


LISTENER_DG =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDG1))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.60)(PORT = 1522))
   )
 )
tnsnames file - entry test12c_far_sync has to be added on primary and standby server as well
test12c_prim =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522))
   )
 (CONNECT_DATA =
   (SID = test12c)
   (SERVER=dedicated)
 )
)

test12c_std =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.79)(PORT = 1522))
   )
 (CONNECT_DATA =
   (SID = test12c)
   (SERVER=dedicated)
 )
)


test12c_far_sync =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.60)(PORT = 1522))
   )
 (CONNECT_DATA =
   (SID = test12c)
   (SERVER=dedicated)
 )
)
When instance parameter file and network configuration is ready we need to create Far Sync control file on primary server.
[oracle@ora12c dbs]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 26 23:21:59 2013

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


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

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/tmp/farsync.ctl';

Database altered.

Copy it to Far Sync server
[oracle@ora12c dbs]$ scp /tmp/farsync.ctl 192.168.1.60:/u01/app/oracle/oradata/test12c/control01.ctl
oracle@192.168.1.60's password:
farsync.ctl                                                                                                                  100%   10MB  10.3MB/s   00:00
[oracle@ora12c dbs]$ scp /tmp/farsync.ctl 192.168.1.60:/u01/app/oracle/oradata/test12c/control02.ctl
oracle@192.168.1.60's password:
farsync.ctl                                                                                                                  100%   10MB  10.3MB/s   00:01
[oracle@ora12c dbs]$ scp /tmp/farsync.ctl 192.168.1.60:/u01/app/oracle/oradata/test12c/control03.ctl
oracle@192.168.1.60's password:
farsync.ctl                                                                                                                  100%   10MB  10.3MB/s   00:00
[oracle@ora12c dbs]$
Now Far Sync instance can be started and new standby logs could be created. Make sure if Data Guard Broker has been started as well.
[oracle@oraclese dbs]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 26 23:24:27 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile;
File created.

SQL> startup mount
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2289688 bytes
Variable Size             293605352 bytes
Database Buffers          167772160 bytes
Redo Buffers                8163328 bytes
Database mounted.

SQL> alter system set dg_broker_start = true;
System altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby01.log') size 50M reuse;
Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby02.log') size 50M reuse;
Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby03.log') size 50M reuse;
Database altered.
Like if previous post about Data Guard configuration - Broker will be used to do all configuration work. You should to connect to Broker from primary database. This is how current configuration looks like:
[oracle@ora12c ~]$ rlwrap dgmgrl /
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

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

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

Configuration - fsc

  Protection Mode: MaxPerformance
  Databases:
  test12c_prim - Primary database
    test12c_std - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
Adding Far Sync
DGMGRL> add far_sync test12c_far_sync as connect identifier is 'test12c_far_sync';
far sync instance "test12c_far_sync" added
DGMGRL>  show configuration verbose;

Configuration - fsc

  Protection Mode: MaxPerformance
  Databases:
  test12c_prim - Primary database
    test12c_std - Physical standby database
    test12c_far_sync  - Far Sync (disabled)

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
Now new rules for redo log transport have to be configured
DGMGRL> edit database 'test12c_prim' set property redoroutes='(local : test12c_far_sync sync)';
Property "redoroutes" updated
DGMGRL> edit far_sync 'test12c_far_sync' set property redoroutes='(test12c_prim : test12c_std)';
Property "redoroutes" updated
Above configuration is sending redo data from primary database to far sync instance using SYNC mode and redo data from far sync instance to standby database using ASYNC mode. As primary and far sync instance are close one to each other you can have no data loss mode without additional network synchronization overhead.
Redo logs routing rules are described in Oracle documentation.

Enabling and checking configuration
DGMGRL> enable far_sync test12c_far_sync;
Enabled.
DGMGRL> show configuration verbose;

Configuration - fsc

  Protection Mode: MaxPerformance
  Databases:
  test12c_prim - Primary database
    test12c_far_sync  - Far Sync
      test12c_std - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
If everything is OK we can switch protection mode from Maximum Performance to Maximum Availability to get all advantages of our new configured Far Sync instance.
DGMGRL> edit configuration set protection mode as MaxAvailability;
Succeeded.
DGMGRL> show configuration verbose;

Configuration - fsc

  Protection Mode: MaxAvailability
  Databases:
  test12c_prim - Primary database
    test12c_far_sync  - Far Sync
      test12c_std - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Now all is done. Happy testing. 
Marcin

Saturday, July 27, 2013

How to quickly build standby database and setup DataGuard configuration using Oracle 12c

With every release of Oracle database there are new possibilities to build a required solution (ex. standby database) faster and with fewer steps. This is not official guide but rather a mix of Oracle 12c functionality used to build DataGuard configuration using RMAN and DataGuard Broker.

Assumptions:

  • There is a database which will be used as primary.
  • New listener will be added to support standby traffic
Steps to run on primary database

Listener configuration
LISTENER_DG =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDG1))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522))
   )
 )

SID_LIST_LISTENER_DG =
 (SID_LIST =
   (SID_DESC=
       (ORACLE_HOME= /u01/app/oracle/product/12.1.0/db1)
       (SID_NAME=test12c)
   )
   (SID_DESC =
      (SID_NAME = test12c)
      (GLOBAL_DBNAME = test12c_prim_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db1)
   )
 )
tnsnames entries
test12c_std =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.79)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = test12c)
      (SERVER = dedicated)
    )
  )


test12c_prim =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = test12c)
      (SERVER = dedicated)
    )
  )


New parameters for database
[oracle@ora12c ~]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 26 21:02:14 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

SQL> alter system set local_listener='(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522)))';
System altered.

SQL> alter system set db_unique_name='test12c_prim' scope=spfile;
System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2289688 bytes
Variable Size             255856616 bytes
Database Buffers          209715200 bytes
Redo Buffers                3969024 bytes
Database mounted.
Database opened.
New control file for standby
SQL> alter database create standby controlfile as '/tmp/standby.ctl' reuse;
Database altered.
Copy standby control file to standby server
[oracle@ora12c ~]$ scp -i /home/oracle/oracle_standby.key  /tmp/standby.ctl     oracle@10.0.1.79:/u01/app/oracle/oradata/test12c/control01.ctl
standby.ctl                                                                                                                       100%   10MB 438.0KB/s   00:24
[oracle@ora12c ~]$ scp -i /home/oracle/oracle_standby.key  /tmp/standby.ctl     oracle@10.0.1.79:/u01/app/oracle/oradata/test12c/control02.ctl
standby.ctl                                                                                                                       100%   10MB 438.0KB/s   00:24
[oracle@ora12c ~]$ scp -i /home/oracle/oracle_standby.key  /tmp/standby.ctl     oracle@10.0.1.79:/u01/app/oracle/oradata/test12c/control03.ctl
standby.ctl                                                                                                                       100%   10MB 438.0KB/s   00:24

Steps to run on new standby database

Database parameter file - it could be a copy of primary database file with two changes - db_unique_name and local_listener
*.audit_file_dest='/home/oracle/admin/test12c/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/test12c/control01.ctl','/u01/app/oracle/oradata/test12c/control02.ctl','/u01/app/oracle/oradata/test12c/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test12c'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test12cXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/test12c/arch'
*.open_cursors=300
*.pga_aggregate_target=150m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=450m
*.undo_tablespace='UNDOTBS1'
# changes for standby
*.db_unique_name='test12c_std'
*.local_listener='(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.79)(PORT = 1522)))'
Listener configuration
LISTENER_DG =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDG1))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.79)(PORT = 1522))
   )
 )

SID_LIST_LISTENER_DG =
 (SID_LIST =
   (SID_DESC=
       (ORACLE_HOME= /u01/app/oracle/product/12.1.0/db1)
       (SID_NAME=test12c)
   )
   (SID_DESC =
      (SID_NAME = test12c)
      (GLOBAL_DBNAME = test12c_std_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db1)
   )
 )
tnsnames entries
test12c_std =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.79)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = test12c)
      (SERVER = dedicated)
    )
  )


test12c_prim =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = test12c)
      (SERVER = dedicated)
    )
  )
 
In next step we have to check if all datafile and configuration directory are in place on standby machine and startup instance in mount mode.

Starting up instance
 
[oracle@ip-10-0-1-79 dbs]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 26 17:39:48 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile;
File created.

SQL> startup mount
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2289688 bytes
Variable Size             255856616 bytes
Database Buffers          209715200 bytes
Redo Buffers                3969024 bytes
Database mounted.
SQL>

RMAN restore over network is an Oracle 12c feature. Yes there was duplicate from active database in 11g but this one is much easier to run and required fewer configuration steps. To restore Oracle data files on standby server we need to configure only instance in mount mode and add tns alias to primary database.

Starting RMAN and restoring data files 
[oracle@ip-10-0-1-79 dbs]$ rlwrap rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Jul 26 16:18:57 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST12C (DBID=1424547711, not open)

RMAN> restore database from service test12c_prim using compressed backupset;
Starting restore at 26-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_prim
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/test12c/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:08:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_prim
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/test12c/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:08:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_prim
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test12c/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_prim
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test12c/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_prim
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/test12c/soe.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:46:45
Finished restore at 26-JUL-13

RMAN>

At that stage we have primary and standby database almost ready. 

Step to run on primary and standby database

Starting DataGuard Broker and create standby log files
 
SQL> alter system set dg_broker_start = true;
System altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby01.log') size 50M reuse;
Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby02.log') size 50M reuse;
Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby03.log') size 50M reuse;
Database altered.

Steps to run on primary database

Now is time for DataGuard Broker to work for us. 

Adding primary database
[oracle@ora12c ~]$ rlwrap dgmgrl /
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> connect /
Connected as SYSDG.
DGMGRL> create configuration FSC as primary database is test12c_prim connect identifier is 'test12c_prim';
Configuration "fsc" created with primary database "test12c_prim"
 
Adding standby database and displaying configuration
DGMGRL> add database test12c_std as connect identifier is 'test12c_std' maintained as physical;
Database "test12c_std" added
DGMGRL> show configuration verbose;

Configuration - fsc

  Protection Mode: MaxPerformance
  Databases:
  test12c_prim - Primary database
    test12c_std - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED
 
Enabling configuration and checking DataGuard status
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration verbose;

Configuration - fsc

  Protection Mode: MaxPerformance
  Databases:
  test12c_prim - Primary database
    test12c_std - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

And our DataGuard configuration is up and running. Now we can change default configuration to one which meet our requirements. I next post I will add Far Sync instance and switch configuration to Maximum Availability.

regards,
Marcin