Wednesday, April 29, 2015

Insufficient archive log space caused ORA-03113

Oracle 11g database was shut down for hardware maintenance. Unfortunately, the database can not be started after maintenance because of error ORA-03113.

$ srvctl sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 27 14:10:05 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 33947688
Session ID: 749 Serial number: 3


Apparently, something happended while instance status is changed to OPEN from MOUNT. Check alert log file:

ARC3 started with pid=30, OS id=51183782
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '+FRA2'
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-16038: log 3 sequence# 32464 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+REDO1/db01/onlinelog/group_3.310.857090421'
ORA-00312: online log 3 thread 1: '+REDO2/db01/onlinelog/group_3.310.857090421'
USER (ospid: 45220056): terminating the instance due to error 16038


Instance is terminated due to error 16038: log 3 sequence# 32464 cannot be archived. Redo log cannot be archived because the recovery area is 100% used.

Backup archive log and release space by running:

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 27 14:17:52 2015

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 835104768 bytes

Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes

RMAN> backup archivelog all delete all input;

... ...


Try to open database:

RMAN> alter database open;

database opened


Database is opened successfully.

Friday, February 13, 2015

Integrating BI Publisher with Enterprise Manager using the configureBIP Script

As we know, Oracle has deprecated Information Publisher (IP) Reports for Enterprise Manager 12c and introduce new report tool BI Publisher (BIP) Enterprise Reports. Here, I am going to configure BIP after OEM 12c (12.1.0.4) has been installed and used for a while.

Configure environment and set ORACLE_HOME to OMS installation path:
$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/oms12104/oms
$ export PATH=$ORACLE_HOME/bin:$PATH
$ which emctl
/u01/app/oracle/product/12.1.0/oms12104/oms/bin/emctl

Backup OEM using command: emctl exportconfig oms
$ emctl exportconfig oms -dir /u01/app/oracle/OMS_BACKUP -oms_only -keep_host
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
ExportConfig started...
Backup directory is /u01/app/oracle/OMS_BACKUP
Performing OMS backup...
Exporting emoms properties...
Exporting secure properties...

Export has determined that the OMS is not fronted
by an SLB. You have chosen to export the local
hostname. The exported data may ONLY be imported
on a host with the same hostname. Please see the
EM Advanced Configuration Guide for more details.

Warning: Could not export files for module bip: Unable to read file /u01/app/oracle/product/12.1.0/gc_inst/em/EMGC_OMS1/embip.properties for exporting
Exporting configuration for pluggable modules...
Preparing archive file...
Backup has been written to file: /u01/app/oracle/OMS_BACKUP/opf_OMS_20150210_111433.bka

The export file contains sensitive data.
You must keep it secure.

ExportConfig completed successfully!

Configure BIP using script configureBIP
$ cd $ORACLE_HOME/bin
$
$ ./configureBIP
Configuring BI Publisher Version "11.1.1.7.0" to work with Enterprise Manager
Logging started at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log.
Before this command is run, a backup of Enterprise Manager should be performed using the :emctl exportconfig oms: command. Have you made a valid backup of Enterprise Manager (yes/no) [no] ? yes
Enter sysdba user name (sys):sys
Enter sysdba user password:
Enter Administration Server user password:
Configuring BI Publisher in Oracle Home located in /u01/app/oracle/product/12.1.0/oms12104/Oracle_BI1 ...
Processing command line ....
Repository Creation Utility - Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility - Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility - Creating Tablespaces
Validating and Creating Tablespaces
Repository Creation Utility - Create
Repository Create in progress.
Percent Complete: 0
Percent Complete: 10
Percent Complete: 30
Percent Complete: 50
Percent Complete: 50
Percent Complete: 100
Repository Creation Utility: Create - Completion Summary
Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.dbaplus.ca)(PORT=1521)))(LOAD_BALANCE=ON)(CONNECT_DATA=(SERVICE_NAME=OEMREP)))
Connected As : sys
Prefix for (prefixable) Schema Owners : SYSMAN
RCU Logfile : /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/emBIPLATFORM.log
Component schemas created:
Component Status Logfile
Business Intelligence Platform Success /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/biplatform.log

Repository Creation Utility - Create : Operation Completed
Successfully created SYSMAN_BIPLATFORM schema...
Enter an integer between 9701 and 49152 for the BI Publisher HTTP server port. (9701):
Enter an integer between 9702 and 49152 for the BI Publisher HTTPS server port. (9702):
Extending domain with BI Publisher. This operations can take some time. Do not interrupt this command while it is running...
Locking Enterprise Manager ...
OMS Console is locked. Access the console over HTTPS ports.
Restart OMS.
Restarting Enterprise Manager ...
Stopping Enterprise Manager, this can take some time ...
Failed to stop Enterprise Manager. Diagnostic code 1.
See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.
Error extending domain
See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.

OMG, a bad luck! Check log file for what happened,
$ tail -20 /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log

[2015-02-10T11:30:51.192-05:00] [sysman] [NOTIFICATION] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 17] [userId: oracle] [ecid: 0000KhoL3KH0rmjrh0r2E41KqYwd000004,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.ProcessOutputReader] [SRC_METHOD: run] Stopping WebTier...[[

]]
[2015-02-10T11:30:55.221-05:00] [sysman] [NOTIFICATION] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 17] [userId: oracle] [ecid: 0000KhoL3KH0rmjrh0r2E41KqYwd000004,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.ProcessOutputReader] [SRC_METHOD: run] Error Occurred: Cannot run program "/u01/app/oracle/product/12.1.0/gc_inst/WebTierIH1/bin/opmnctl": error=12, Not enough space[[

]]
[2015-02-10T11:30:55.222-05:00] [sysman] [NOTIFICATION] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 17] [userId: oracle] [ecid: 0000KhoL3KH0rmjrh0r2E41KqYwd000004,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.ProcessOutputReader] [SRC_METHOD: run] Please check /u01/app/oracle/product/12.1.0/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for error details[[

]]
[2015-02-10T11:30:55.265-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: lockAndBounceEM] Failed to stop Enterprise Manager. Diagnostic code 1.
[2015-02-10T11:30:55.273-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: extendDomainWithBIP] See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.
[2015-02-10T11:30:55.274-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: handleFatalError] Error extending domain
[2015-02-10T11:30:55.275-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: handleFatalError] Fatal error:[[
java.lang.Exception: See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.
at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.extendDomainWithBIP(BIPCA.java:2370)
at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.main(BIPCA.java:1531)

At the begining of the error, I found messages: 'opmnctl', 'Not enough space', 'Failed to stop Enterprise Manager'. What a familiar look! Yes, it is familiar. Let's try to fix it following "Got ‘Not enought space’ while ‘emctl stop oms’". Then, restart the process. Of course, I have to clean/rollback the failed configuration first before re-running configureBIP.

Clean/rollback failed configureBIP run

Stop BIP server
$ emctl stop oms -bip_only
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Stopping BI Publisher Server...
BI Publisher Server Successfully Stopped
BI Publisher Server is Down

Unregister BIP from Enterprise Manager
$ emcli login -username=sysman
Enter password :

Login successful
$ emcli sync
Synchronized successfully

$ emcli unregister_bipublisher -force
Warning: BI Publisher was not registered for use with Enterprise Manager, no action taken.

BIP was not registered with Enterprise Manager yet, but it does not really matter, just go ahead.
Drop schema created by script configureBIP from Enterprise Manager repository database
$ sqlplus sys@OEMREP as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Feb 13 08:39:01 2015

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options

SQL> drop user SYSMAN_BIPLATFORM cascade;

User dropped.

SQL> select comp_id,COMP_NAME,owner,version,status from system.schema_version_registry where comp_id = 'BIPLATFORM';

COMP_ID COMP_NAME OWNER VERSION STATUS
--------------- ------------------------------ ------------------------------ ------------------------------ -----------
BIPLATFORM OracleBI and EPM SYSMAN_BIPLATFORM 11.1.1.7.0 VALID

SQL> delete from system.schema_version_registry where comp_id = 'BIPLATFORM';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select comp_id,COMP_NAME,owner,version,status from system.schema_version_registry where comp_id = 'BIPLATFORM';

no rows selected

Re-run script configureBIP to configure BIP with Enterprise Manager
$ ./configureBIP
Configuring BI Publisher Version "11.1.1.7.0" to work with Enterprise Manager
Logging started at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150213084523.log.
Before this command is run, a backup of Enterprise Manager should be performed using the :emctl exportconfig oms: command. Have you made a valid backup of Enterprise Manager (yes/no) [no] ? yes
Enter sysdba user name (sys):
Enter sysdba user password:
Enter Administration Server user password:
Configuring BI Publisher in Oracle Home located in /u01/app/oracle/product/12.1.0/oms12104/Oracle_BI1 ...
Processing command line ....
Repository Creation Utility - Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility - Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility - Creating Tablespaces
Validating and Creating Tablespaces
Repository Creation Utility - Create
Repository Create in progress.
Percent Complete: 0
Percent Complete: 10
Percent Complete: 30
Percent Complete: 50
Percent Complete: 50
Percent Complete: 100
Repository Creation Utility: Create - Completion Summary
Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.dbaplus.ca)(PORT=1521)))(LOAD_BALANCE=ON)(CONNECT_DATA=(SERVICE_NAME=OEMREP)))
Connected As : sys
Prefix for (prefixable) Schema Owners : SYSMAN
RCU Logfile : /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/emBIPLATFORM.log
Component schemas created:
Component Status Logfile
Business Intelligence Platform Success /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/biplatform.log

Repository Creation Utility - Create : Operation Completed
Successfully created SYSMAN_BIPLATFORM schema...
Enter an integer between 9701 and 49152 for the BI Publisher HTTP server port. (9701):
Enter an integer between 9702 and 49152 for the BI Publisher HTTPS server port. (9702):
Extending domain with BI Publisher. This operations can take some time. Do not interrupt this command while it is running...
Locking Enterprise Manager ...
OMS Console is locked. Access the console over HTTPS ports.
Restart OMS.
Restarting Enterprise Manager ...
Stopping Enterprise Manager, this can take some time ...
Starting Enterprise Manager. This operation can take some time. Do not interrupt this command while it is running.
OMS Started Successfully
BI Publisher server named :BIP: running at https://host01.dbaplus.ca:9702/xmlpserver.
Registering BI Publisher with Enterprise Manager and deploying reports...
Performing automatic backup of Enterprise Manager using the command :emctl exportconfig oms:.
Successfully backed up Enterprise Manager. The backup file is located in the INSTANCE_HOME sysman backup directory.
Successfully setup BI Publisher with Enterprise Manager

Now, we can use BI Publisher to do report instead of Information Publisher.

Monday, February 9, 2015

Apply GI PSU 12.1.0.2.2 while using Software-Only installation of GI12.1.0.2

A software-only installation only copies the Oracle Grid Infrastructure for a standalone server binaries to the specified location. After the installation, Oracle Grid Infrastructure (GI) is not configured and HAS (GI standalone)/ CRS(GI cluster) stack is not up yet. Therefore, "optach auto" introduced the the PSU document can not be used to apply the patch.

1. Start Oracle Universal Installer as the Oracle Restart software owner user (grid)
[grid@host01 grid]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 415 MB.   Actual 82041 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4095 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-02-05_02-16-33PM. Please wait ...
[grid@host01 grid]$ You can find the log of this install session at: /u01/app/oraInventory/logs/installActions2015-02-05_02-16-33PM.log
... ...

2. In the Select Installation Option screen, select the Install Oracle Grid Infrastructure Software Only option to install Oracle Restart and Oracle ASM software only. Click Next.

3. In the Select Product Languages screen, select one or more languages. Move the languages from the Available Languages list to the Selected Languages list. Click Next.

4. In the Privileged Operating System Groups screen, select the name of the operating system group you created for the OSDBA group, the OSASM group, and the Oracle ASM operator group OSOPER. If you create only the dba group, then you can use that group for all three privileged groups. If you created a separate asmadmin group, then use that value for the OSASM group. Click Next.

5. In the Specify Installation Location screen, enter the following information, and click Next:
Oracle Base: Enter the location for the Oracle base directory. Do not include spaces in the path. Here, I am using: /u01/app/grid
Software Location: Accept the default value or enter the directory path in which you want to install the software.The directory path must not contain
spaces. Here, I am using /u01/app/grid/product/12.1.0/grid12102

6. In the pop-up window, the warning message is for cluster installation, click Yes to continue if the GI is installed as Standalone. Otherwise, Oracle Home location has to be out of ORACLE_BASE.

7. The Create Inventory screen is displayed only if this is the first time you are installing Oracle software on your system. Change the path for the Inventory Directory (e.g. /u01/app/oraInventory), if required. Select oinstall for the oraInventory Group Name, if required. Click Next.

8. The Perform Prerequisite Checks screen checks if the minimum system requirements are met to perform the Oracle Grid Infrastructure installation. If all the system requirements are met, then you are directed to the Summary screen. If an installation fails, you can review the error.

9. Review the contents of the Summary screen, and click Install.

10. The Install Product screen displays the progress of the Oracle Grid Infrastructure installation. Oracle Universal Installer prompts you to run the root.sh script and, if required, the orainstRoot.sh script as the root user to complete the installation. During this process, the Execute Configuration Scripts window appears. Do not click OK until you successfully apply the patch (PSU 12.1.0.2.2).

11. If required, run orainstRoot.sh script as the root user
[root@host01 app]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.

12. Install new version of OPatch
[grid@host01 tmp]$ cd /u01/app/grid/product/12.1.0/grid12102
[grid@host01 grid12102]$ OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.
[grid@host01 grid12102]$ mv OPatch OPatch.12.1.0.1.3
[grid@host01 grid12102]$ unzip /u01/app/media/p6880880_121010_Linux-x86-64.zip
Archive:  /u01/app/media/p6880880_121010_Linux-x86-64.zip
   creating: OPatch/
  inflating: OPatch/opatchdiag.bat
    ... ...
[grid@host01 grid12102]$ OPatch/opatch version
OPatch Version: 12.1.0.1.6

OPatch succeeded.

13. Extract the GI PSU 12.1.0.2.2 (Patch 19954978) into the empty stage directory as the GI software owner (grid), we will find four patched included in the PSU bundle: 19769473, 19769479, 19769480, 19872484
[grid@host01 grid12102]$ unzip -d /u01/app/media/ /u01/app/media/p19954978_121020_Linux-x86-64.zip
[grid@host01 grid12102]$ ls -l /u01/app/media/19954978
total 104
drwxr-xr-x. 5 grid oinstall  4096 Jan 16 04:30 19769473
drwxr-xr-x. 5 grid oinstall  4096 Jan 16 04:30 19769479
drwxrwxr-x. 4 grid oinstall  4096 Jan 16 04:30 19769480
drwxr-xr-x. 4 grid oinstall  4096 Jan 16 04:30 19872484
drwxr-xr-x. 2 grid oinstall  4096 Jan 16 04:30 automation
-rw-rw-r--. 1 grid oinstall  4987 Jan 16 05:53 bundle.xml
-rw-rw-r--. 1 grid oinstall 77081 Jan 19 12:32 README.html
-rw-r--r--. 1 grid oinstall     0 Jan 16 04:30 README.txt

14.  Apply patch to the newly installed 12.1.0.2 GI home as the GI software owner, since "opatch auto" cannot be used, we have to apply patches in the PSU bundle one by one using opatch apply,
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769479
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769473
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19872484
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769480
[grid@host01 ~]$ export ORACLE_HOME=/u01/app/grid/product/12.1.0/grid12102
[grid@host01 ~]$ $ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769479
Oracle Interim Patch Installer version 12.1.0.1.6
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
   ... ...
Applying interim patch '19769479' to OH '/u01/app/grid/product/12.1.0/grid12102'
Verifying environment and performing prerequisite checks...
   ... ...
Verifying the update...
Patch 19769479 successfully applied
Log file location: /u01/app/grid/product/12.1.0/grid12102/cfgtoollogs/opatch/opatch2015-02-09_14-33-35PM_1.log

OPatch succeeded.

[grid@host01 ~]$ $ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769473
Oracle Interim Patch Installer version 12.1.0.1.6
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
   ... ...
Applying interim patch '19769473' to OH '/u01/app/grid/product/12.1.0/grid12102'
Verifying environment and performing prerequisite checks...
   ... ...
Patching component oracle.usm, 12.1.0.2.0...

Verifying the update...
Patch 19769473 successfully applied
Log file location: /u01/app/grid/product/12.1.0/grid12102/cfgtoollogs/opatch/opatch2015-02-09_14-41-04PM_1.log

OPatch succeeded.
[grid@host01 ~]$
[grid@host01 ~]$ $ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19872484
Oracle Interim Patch Installer version 12.1.0.1.6
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
   ... ...
Applying interim patch '19872484' to OH '/u01/app/grid/product/12.1.0/grid12102'
   ... ...
Patching component oracle.wlm.dbwlm, 12.1.0.2.0...

Verifying the update...
Patch 19872484 successfully applied
Log file location: /u01/app/grid/product/12.1.0/grid12102/cfgtoollogs/opatch/opatch2015-02-09_14-45-29PM_1.log

OPatch succeeded.
[grid@host01 ~]$ $ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769480
Oracle Interim Patch Installer version 12.1.0.1.6
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
   ... ...
Applying interim patch '19769480' to OH '/u01/app/grid/product/12.1.0/grid12102'
Verifying environment and performing prerequisite checks...
Patch 19769480: Optional component(s) missing : [ oracle.xdk, 12.1.0.2.0 ] , [ oracle.oraolap, 12.1.0.2.0 ]
   ... ...
OPatch succeeded.

15. Run root.sh as user root


[root@host01 bin]# /u01/app/grid/product/12.1.0/grid12102/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/product/12.1.0/grid12102

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

To configure Grid Infrastructure for a Stand-Alone Server run the following command as the root user:
/u01/app/grid/product/12.1.0/grid12102/perl/bin/perl -I/u01/app/grid/product/12.1.0/grid12102/perl/lib -I/u01/app/grid/product/12.1.0/grid12102    /crs/install /u01/app/grid/product/12.1.0/grid12102/crs/install/roothas.pl


To configure Grid Infrastructure for a Cluster execute the following command as grid user:
/u01/app/grid/product/12.1.0/grid12102/crs/config/config.sh
This command launches the Grid Infrastructure Configuration Wizard. The wizard also supports silent operation, and the parameters can be passed     through the response file that is available in the installation media.



16. Back to Oracle Universal Installer. In Execute Configuration Scripts screen, click Ok.
17. The Finish screen displays the installation status. Click Close to end the installation and exit Oracle Universal Installer.

References:
Supplemental Readme - Patch Installation and Deinstallation For 12.1.0.x.x GI PSU (Doc ID 1591616.1)
How to Apply a Grid Infrastructure Patch Before root script (root.sh or rootupgrade.sh) is Executed? (Doc ID 1410202.1)

Thursday, January 29, 2015

Database link cannot be dropped because of ORA-02024

When dropping database link got following error:
ORA-02024: database link not found.
SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
----------------- ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13

SQL> drop database link REFRESH_DBLINK;
drop database link REFRESH_DBLINK
*
ERROR at line 1:
ORA-02024: database link not found

Can not find an existing database link? What if create a new link with same name?
SQL> create database link REFRESH_DBLINK
2 connect to system identified by Ax356 using 'DBPROD';

Database link created.

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13
REFRESH_DBLINK.WORLD SYSTEM DBPROD 29-JAN-15

The name of new database link is appended with '.WORLD'. Did more test and found that all new created database link will be added suffix '.WORLD' in the name. Try DROP DATABASE LINK to see what is going to happen:
SQL> drop database link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13

See, I was dropping REFRESH_DBLINK, but REFRESH_DBLINK.WORLD was dropped. I always get REFRESH_DBLINK.WORLD dropped no matter whether I include '.WORLD' in the DROP DATABASE LINK command. Why did we get the weird term '.WORLD'? It was popular long time ago as default database domain and often found in global_name & db_domain of the database:
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DBPROD

SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
DBPROD.WORLD

It does be a part of database global_name. Does domain part in global_name affect database link name space? Unfortunately, it does. Delete the domain part from global_name:

 
SQL> alter database rename global_name to DBPROD;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------
DBPROD.WORLD

Interesting, the ALTER DATABASE command does not help. Let us manually change global_name bypass ALTER DATABASE command,
Note: Take a complete consistent backup of the database before you continue:
SQL> conn / as sysdba
Connected.

SQL> select name,value$ from props$ where GLOBAL_name='GLOBAL_DB_NAME';

NAME VALUE$
------------------------------ --------------------------------------------------
GLOBAL_DB_NAME DBPROD.WORLD

SQL> update props$ set value$='DBPROD' where name='GLOBAL_DB_NAME';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from global_name;

GLOBAL_NAME
---------------------
DBPROD

Oracle never suggests direct update on props$. We are not supposed to do that. Hmmm, today is special :)

Drop the database link:
SQL> connect system

SQL> drop datbaetabase link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

no rows selected

Finally, database link is really dropped.

Monday, January 26, 2015

TNSPING of 11g RAC database got very slow response

Business users complain it took several minutes to log into database. It used to take several seconds.

Confirmed informations as following:

Server: Solaris 10 SPARC + Oracle Database Server 11.2.0.3 RAC
Client: windows 7 + Oracle Client 11.2.0.3

Testing connectivity from workstation:
C:\>tnsping RACPD

TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 26-JAN-2015 10:49:15

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
c:\app\oracle\product\11.2.0\client11203\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host-scan.dbaplus.ca)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=RACPD)))
OK (120000 msec)

Feel like the SCAN listener(s) is/are alive and there is network issue. If it is network problem, the issue should be reproduced on all three IP addresses bound with SCAN name (host-scan.dbaplus.ca). Let's find IPs first:
C:\>nslookup host-scan.dbaplus.ca
Server: ns01.dbaplus.ca
Address: 10.10.2.253

Name: host-scan.dbaplus.ca
Addresses: 10.10.2.30
10.10.2.31
10.10.2.29

Checking the IP availability:
C:\>ping 10.10.2.30

Pinging 10.10.2.30 with 32 bytes of data:
Reply from 10.10.2.30: bytes=32 time=5ms TTL=252
Reply from 10.10.2.30: bytes=32 time<1ms TTL=252
Reply from 10.10.2.30: bytes=32 time<1ms TTL=252
Reply from 10.10.2.30: bytes=32 time<1ms TTL=252

Ping statistics for 10.10.2.30:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 5ms, Average = 1ms

C:\>ping 10.10.2.31

Pinging 10.10.2.31 with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.

Ping statistics for 10.10.2.31:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

C:\>ping 10.10.2.29

Pinging 10.10.2.29 with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.

Ping statistics for 10.10.2.29:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

Two of three SCAN virtual IPs are timed out, the other one (10.10.2.30) is working. It is not network problem. Log onto database servers:
$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/oracle
$
$ crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.FRA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.LISTENER.lsnr
ONLINE ONLINE host01
ONLINE OFFLINE host02
ora.asm
ONLINE ONLINE host01 Started
ONLINE ONLINE host02 Started
ora.gsd
OFFLINE OFFLINE host01
OFFLINE OFFLINE host02
ora.net1.network
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.ons
ONLINE ONLINE host01
ONLINE ONLINE host02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE host02
ora.LISTENER_SCAN2.lsnr
1 ONLINE OFFLINE
ora.LISTENER_SCAN3.lsnr
1 ONLINE OFFLINE
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.host01.vip
1 ONLINE ONLINE host01
ora.host02.vip
1 ONLINE UNKNOWN host01
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.scan1.vip
1 ONLINE ONLINE host02
ora.scan2.vip
1 ONLINE UNKNOWN host01
ora.scan3.vip
1 ONLINE UNKNOWN host01

SCAN VIP scan2 & scan3 are in UNKNOWN status, corresponding SCAN listeners LISTENER_SCAN2 & LISTENER_SCAN3 are OFFLINE.

When I connect to database from workstation, Oracle client will work as following:
1. Ask DNS server for IPs for the SCAN name host-scan.dbaplus.ca, and get three IPs
2. Choose one of them to connect. If I am lucky, the one should be SCAN1 and I will not find any issues. Actually, I was not. scan2 (or scan3) was chosen and the request waited for response from server until time out because the SCAN VIP is not ONLINE, then failed over to another SCAN VIP scan3 (or scan2) or scan1. The worst thing is failover to scan3, it will get timeout again and have to fail over finally to scan1.
3. No matter where it starts, SCAN VIP scan1 will be the final and only choice for the connection. How fast the connection can be established depends on how fast the ONLINE SCAN VIP scan1 is chosen.

To fix the issue, try to start them:
$ srvctl start scan_listener -i 2
$ srvctl start scan_listener -i 3
$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node host02
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node host01
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node host01

$ crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.FRA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.LISTENER.lsnr
ONLINE ONLINE host01
ONLINE OFFLINE host02
ora.asm
ONLINE ONLINE host01 Started
ONLINE ONLINE host02 Started
ora.gsd
OFFLINE OFFLINE host01
OFFLINE OFFLINE host02
ora.net1.network
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.ons
ONLINE ONLINE host01
ONLINE ONLINE host02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE host02
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE host01
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE host01
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.host01.vip
1 ONLINE ONLINE host01
ora.host02.vip
1 ONLINE UNKNOWN host01
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.scan1.vip
1 ONLINE ONLINE host02
ora.scan2.vip
1 ONLINE ONLINE host01
ora.scan3.vip
1 ONLINE ONLINE host01

All SCAN VIP and SCAN listeners are ONLINE. Back to workstation and test the connection:
C:\>tnsping RACPD

TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 26-JAN-2015 10:49:15

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
c:\app\oracle\product\11.2.0\client11203\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host-scan.dbaplus.ca)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=RACPD)))
OK (10 msec)

'10 msec' response time is not bad. The issue got fixed.

Tuesday, January 20, 2015

impdp got 'ORA-01843: not a valid month'

When running impdp to load data, got errors:
ORA-39083: Object type TABLE:"DEVUSER"."TAB01" failed to create with error:
ORA-01843: not a valid month
Failing sql is:
CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000')

Try to run the script manually in SQLPLUS:
devuser@ORADB> CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000');
CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000')
*
ERROR at line 1:
ORA-01843: not a valid month

Got same error. Obviously, the CREATE TABLE is using string constant as default value for data type TIMESTAMP. We know it will work only if the session TIMESTAMP format is same as the given string. Let's see what is the session TIMESTAMP format:
devuser@ORADB> variable partype number;
devuser@ORADB> variable intval number;
devuser@ORADB> variable strval varchar2(255);
devuser@ORADB> exec :partype:=dbms_utility.get_parameter_value('NLS_TIMESTAMP_FORMAT',:intval,:strval);

PL/SQL procedure successfully completed.

devuser@ORADB> col STRVAL for a50
devuser@ORADB> print strval

STRVAL
--------------------------------------------------
DD-MON-RR HH.MI.SSXFF AM

The value of session parameter NLS_TIMESTAMP_FORMAT is  'DD-MON-RR HH.MI.SSXFF AM', which is different from the string constant. Change the session parameter as following:
devuser@ORADB> alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6';

Session altered.

devuser@ORADB> exec :partype:=dbms_utility.get_parameter_value('NLS_TIMESTAMP_FORMAT',:intval,:strval);

PL/SQL procedure successfully completed.

devuser@ORADB> print strval

STRVAL
--------------------------------------------------
YYYY-MM-DD HH24:MI:SS.FF6

Retry the CREATE TABLE statement:
devuser@ORADB> CREATE TABLE "DEVUSER"."TAB01" ("ID" NUMBER NOT NULL ENABLE, "CREATED_TIME" TIMESTAMP (6) DEFAULT '1970-01-01 00:00:00.000000');

Table created.

The issue is fixed. Therefore, the CREATE TABLE can be run properly by setting session parameter NLS_TIMESTAMP_FORMAT. The question is how the impdp utility sets this parameter in its own session? LOGON trigger would be a tentative answer:
devuser@ORADB> create or replace trigger temp after logon on database
2 begin
3 execute immediate 'alter session set nls_timestamp_format=''YYYY-MM-DD HH24:MI:SS.FF6''';
4 end;
5 /

Trigger created.

Try impdp again, it works. However, it may not be a best solution. If possible I would like modify the CREATE TABLE (in source database) as following before runnig expdp to create dumpfile:
CREATE TABLE "DEVUSER"."TAB01"
("ID" NUMBER NOT NULL ENABLE,
"CREATED_TIME" TIMESTAMP (6) DEFAULT TO_TIMESTAMP('1970-01-01 00:00:00.000000','YYYY-MM-DD HH24:MI:SS.FF6'));

It is independent on the session timestamp format.

Monday, January 19, 2015

Setting CURRENT_SCHEMA caused error 'ORA-01031: insufficient privileges'

Question: Why did I get 'ORA-01031: insufficient privileges' when running 'create table' command to create table ? I do have 'CREATE TABLE' privilege granted.

Answer: the CURRENT_SCHEMA of your session is not expected account of yours.

The database user devuser is granted 'CREATE TABLE' privilege:
evuser@ORADB> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
-------------------- ---------------------------------------- ---
DEVUSER CREATE TABLE NO
DEVUSER CREATE SESSION NO

But he got ORA-01031 when running 'create table'
devuser@ORADB> create table tb01(sno number);
create table tb01(sno number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

And did not get error when creating table with schema specified in the statement:
devuser@ORADB> create table devuser.tb01(sno number);

Table created.

Looks like the 'create table' without schema specified is trying to create table in another user's schema. Find out who the another user is:
devuser@ORADB> select sys_context('userenv','current_schema') as current_schema from dual;

CURRENT_SCHEMA
------------------------------
DEVOWNER

It is not expected user name 'devuser'. Try to change back:
devuser@ORADB> alter session set current_schema=devuser;

Session altered.

devuser@ORADB> select sys_context('userenv','current_schema') as current_schema from dual;

CURRENT_SCHEMA
------------------------------
DEVUSER

Test if 'create table' works:
devuser@ORADB> create table tb02(sno number);

Table created.

See, current_schema accounts for it!

 

 

 

 

 

Wednesday, January 14, 2015

Automate Startup/Shutdown of OSWatcher with Oracle HAS (GridInfrastructure Standalone)

Grid Infrastructure Standalone 11.2.0.3
Operation system AIX 6.1

Download Oracle OSWatcher (oswbb732.tar, current version 7.3.2) from My Oracle Support. Install OSWatcher:
$ mkdir /u01/app/grid/product/7.3.2
$ cd /u01/app/grid/product/7.3.2
$ tar xvf /tmp/oswbb732.tar
  ... ...
$ ls -l oswbb/*OSWbb*
-rwxr-xr-x    1 grid  oinstall         2385 Oct 07 2013  oswbb/startOSWbb.sh
-rwxr-xr-x    1 grid  oinstall          558 Apr 17 2014  oswbb/stopOSWbb.sh

Friday, January 9, 2015

Relink Oracle Grid Infrastructure Standalone (Oracle Restart) home and Database home after OS is upgraded from AIX 6.1 to 7.0

Oracle Database 11.2.0.3
Operating System AIX 7.0
The server system is upgraded from AIX 6.1 to 7.0, generally IBM guarantees operating system binary compatibility, therefore, no relink of the Oracle
software is required after the upgrade. However, Oracle recommends performing manual relinking of Oracle Home binaries after OS upgrade.

Tuesday, January 6, 2015

OEM Cloud Control 12c: OMS 12.1.0.4.1 PSU installation

OEM: Cloud Control 12.1.0.4
OS: Solaris 10 Sparc 64bit

Download PSU p19218456_121040_Generic.zip and OPatch p6880880_111000_SOLARIS64.zip from my oracle support.

1. Upgrade OPatch
$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 11.1.0.11.0

OPatch succeeded.
$ cd $ORACLE_HOME
$ pwd
/u01/app/oracle/product/12.1.0/em12104/oms

$ mv OPatch Opatch.11.1.0.11.0

$ unzip /u01/app/media/p6880880_111000_SOLARIS64.zip
... ...

$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 11.1.0.12.3

OPatch succeeded.

Monday, January 5, 2015

Got 'Not enought space' while 'emctl stop oms'

Got 'Not enought space' message while stopping oms (OEM Cloud Control 12.1.0.4 running on Solaris 10 Sparc 64 bit):
$ emctl stop oms
Oracle Enterprise Manager Cloud Control 12c Release 4  
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
Error Occurred: Cannot run program "/u01/app/oracle/product/12.1.0/gc_inst/WebTierIH1/bin/opmnctl": error=12, Not enough space
Please check /u01/app/oracle/product/12.1.0/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for error details

Check log for details:
$ tail /u01/app/oracle/product/12.1.0/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for error details
2015-01-02 14:00:35,466 [main] ERROR wls.OMSController main.253 - OMSController failed for stop oms
2015-01-02 14:00:35,469 [main] ERROR wls.OMSController main.254 - OMSController Error: Cannot run program "/u01/app/oracle/product/12.1.0/gc_inst/WebTierIH1/bin/opmn
ctl": error=12, Not enough space
java.io.IOException: Cannot run program "/u01/app/oracle/product/12.1.0/gc_inst/WebTierIH1/bin/opmnctl": error=12, Not enough space
at java.lang.ProcessBuilder.start(ProcessBuilder.java:460)
at java.lang.Runtime.exec(Runtime.java:593)
at java.lang.Runtime.exec(Runtime.java:431)
at java.lang.Runtime.exec(Runtime.java:369)
at oracle.sysman.emctl.util.EmctlUtil.execCommand(EmctlUtil.java:1228)
at oracle.sysman.emctl.util.EmctlUtil.execCommand(EmctlUtil.java:1217)
at oracle.sysman.emctl.util.WebTierUtil.stopWebTier(WebTierUtil.java:430)
at oracle.sysman.emctl.commands.StopCommand.stopOMS(StopCommand.java:204)
at oracle.sysman.emctl.commands.StopCommand.execute(StopCommand.java:114)
at oracle.sysman.emctl.wls.OMSController.main(OMSController.java:234)
Caused by: java.io.IOException: error=12, Not enough space
at java.lang.UNIXProcess.forkAndExec(Native Method)
at java.lang.UNIXProcess.(UNIXProcess.java:53)
at java.lang.ProcessImpl.start(ProcessImpl.java:65)
at java.lang.ProcessBuilder.start(ProcessBuilder.java:453)
... 9 more

No extra helpful message was found. What kind of space is not enough, OMS Home, /tmp or SWAP?
$ df -k
Filesystem kbytes used avail capacity Mounted on
/devices 0 0 0 0% /devices
... ...
swap 8609640 4896048 3713592 57% /tmp
swap 3713656 64 3713592 1% /var/run
rootpool/oracle 209715200 102092072 107623127 49% /u01
... ...
$ swap -s
total: 33445792k bytes allocated + 2772072k reserved = 36217864k used, 3711289k available

There is >90GB (102092072k) space free for mount point /u01 which can be used by OMS instance, and >3GB(3713592k) space left for /tmp.
It is not reasonable to say the space is not enough. However, is 3.6GB (3711289k) available (free) SWAP space enough for stopping OMS?
I'd like to say yes unless I found MOS Doc 1452151.1. The document is talking about same issues happened while OMS 12c is being started and
mentions 4-5GB of minimum free SWAP space for starting OMS. Could it be same requirement for stopping OMS? Make sense?

Before we enlarge SWAP, we found that /tmp and /var/run are sharing SWAP space. Here, we can ignore /var/run because it is tiny (64k).

Tried to delete garbage files from /tmp and check available SWAP:
$ swap -s
total: 31272184k bytes allocated + 2552208k reserved = 33824392k used, 6271264k available

Now, we have almost 6G SWAP available space and re-run 'emctl stop oms', it works!

Refrerences:

EM 12c Emctl Start OMS Returns Error Occurred: Cannot Run Program "<Middleware_Home>/gc_inst/WebTierIH1/bin/opmnctl": error=12, Not Enough Space (Doc ID 1452151.1)

Friday, December 26, 2014

ASMB process crashed the instance after 'PMON failed to acquire latch'

Environment:
Oracle Database: Enterprise Edition 11.2.0.2.12
Operating System:  AIX 6.1

Symptom:

Database alert log:
Fri Dec 26 07:23:04 2014
PMON failed to acquire latch, see PMON dump
Fri Dec 26 07:24:04 2014
PMON failed to acquire latch, see PMON dump
Fri Dec 26 07:25:36 2014
PMON failed to acquire latch, see PMON dump
Fri Dec 26 07:26:36 2014
PMON failed to acquire latch, see PMON dump
Fri Dec 26 07:28:07 2014
PMON failed to acquire latch, see PMON dump
Fri Dec 26 07:28:52 2014
NOTE: ASMB terminating
Errors in file /u01/app/oracle/diag/rdbms/oradb/ORADB/trace/ORADB_asmb_5439902.trc:
ORA-15064: communication failure with ASM instance
ORA-03135: connection lost contact
Process ID:
Session ID: 152 Serial number: 9
Errors in file /U01/app/oracle/diag/rdbms/oradb/ORADB/trace/ORADB_asmb_5439902.trc:
ORA-15064: communication failure with ASM instance
ORA-03135: connection lost contact
Process ID:
Session ID: 152 Serial number: 9
ASMB (ospid: 5439902): terminating the instance due to error 15064
Fri Dec 26 07:28:52 2014

PMON trace:
*** 2014-12-26 07:23:04.963
Location from where latch is held: kgh.h LINE:6387 ID:kghalo:
Context saved from call: 0
state=busy [holder orapid=16] wlstate=free [value=0]
waiters [orapid (seconds since: put on list, posted, alive check)]:
64 (103, 1419596584, 0)
... ...
61 (4, 1419596584, 0)
waiter count=17
gotten 38993536 times wait, failed first 285766 sleeps 71131
gotten 0 times nowait, failed: 0
Short stack dump:
... ...
possible holder pid = 16 ospid=5439902
----------------------------------------
SO: 0x7000002fec6df78, type: 2, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x7000002fec6df78, name=process, file=ksu.h LINE:12451 ID:, pg=0
(process) Oracle pid:16, ser:1, calls cur/top: 0x7000002ff83fd00/0x7000002ff83fd00
flags : (0x6) SYSTEM
flags2: (0x0), flags3: (0x0)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 0 0 35
last post received-location: ksr2.h LINE:603 ID:ksrpublish
last process to post me: 7000002fec731a8 2 0
last post sent: 1051 0 2
last post sent-location: ksl2.h LINE:2293 ID:kslpsr
last process posted by me: 7000002fec69db8 1 6
(latch info) wait_event=0 bits=84
holding (efd=4) 700000000107940 Child shared pool level=7 child#=1
Location from where latch is held: kgh.h LINE:6387 ID:kghalo:
Context saved from call: 0
state=busy [holder orapid=16] wlstate=free [value=0]
waiters [orapid (seconds since: put on list, posted, alive check)]:
64 (103, 1419596584, 0)
... ...
61 (4, 1419596584, 0)
waiter count=17
holding (efd=4) 700000000047900 ASM map headers level=2
Location from where latch is held: kffm2.h LINE:306 ID:kffmAllocate:
Context saved from call: 0
state=busy [holder orapid=16] wlstate=free [value=0]
Process Group: DEFAULT, pseudo proc: 0x7000002fee6c858
O/S info: user: oragrid, term: UNKNOWN, ospid: 5439902
OSD pid info: Unix process pid: 5439902, image: oracle@host01.dbaplus.ca (ASMB)

ASMB trace:
*** 2014-12-26 07:28:52.409
NOTE: ASMB terminating
ORA-15064: communication failure with ASM instance
ORA-03135: connection lost contact
Process ID:
Session ID: 152 Serial number: 9
error 15064 detected in background process
ORA-15064: communication failure with ASM instance
ORA-03135: connection lost contact
Process ID:
Session ID: 152 Serial number: 9
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+644<-kjzdssdmp()+444<-kjzduptcctx()+272<-kjzdicrshnfy()+96<-ksuitm()+1284<-ksbrdp()+4508<-opirip()+1216<-opidrv()+1156<-sou2o()+192<-opimai_real()+308<-ssthrdmain()+340<-main()+216<-__start()+112
----- End of Abridged Call Stack Trace -----

*** 2014-12-26 07:28:52.412
ASMB (ospid: 5439902): terminating the instance due to error 15064
ksuitm: waiting up to [5] seconds before killing DIAG(8519832)

AWR report:
Cache Sizes                       Begin        End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 6,464M 6,464M Std Block Size: 16K
Shared Pool Size: 5,152M 5,152M Log Buffer: 7,172K

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 97.27 In-memory Sort %: 100.00
Library Hit %: 73.34 Soft Parse %: 63.01
Execute to Parse %: 45.26 Latch Hit %: 99.74
Parse CPU to Parse Elapsd %: 4.75 % Non-Parse CPU: 92.21


Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
latch: shared pool 836 866 1035 38.4 Concurrenc
db file sequential read 227,015 281 1 12.5 User I/O
DB CPU 280 12.4
latch free 6 161 26794 7.1 Other
library cache lock 3 89 29759 4.0 Concurrenc

Check duplicate SQL:
select PLAN_HASH_VALUE,count(*) from v$sql
where EXECUTIONS=1 group by PLAN_HASH_VALUE order by 2 desc;

251958376 34281
3946703927 33694
1997326701 2603
3410233426 1221
3460305098 304
... ...
3150591124 1

select substr(sql_text,1,50),count(1) from v$sql
where PLAN_HASH_VALUE=251958376 group by substr(sql_text,1,50);

Obviously,  the application is not designed with consideration of bind variables, the duplicate SQLs (using literals instead of bind variables) caused massive shared pool fragmentation.

Therefore, ASMB process can not find a suitable sized chunk of memory before it was considered unresponsive and thereafter killed.

Workaround:

Since application can not be changed (system too old to find developer), tried CURSOR_SHARING=FORCE and no crash happened after that.

 

Friday, December 19, 2014

Got 'database incarnation not set' while using RMAN with catalog

RMAN> run {
2> allocate channel oem_backup_sbt1 type 'SBT_TAPE' format '%d_%U' parms "ENV=(NB_ORA_POLICY=oracle_rman, NB_ORA_SCHED=Default)";
3> backup as BACKUPSET tag 'BACKUP_REVQAS1_121814161014' recovery files;
4> release channel oem_backup_sbt1;
5> }
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
allocated channel: oem_backup_sbt1
channel oem_backup_sbt1: SID=162 device type=SBT_TAPE
channel oem_backup_sbt1: Veritas NetBackup for Oracle - Release 6.5 (2009050106)

Starting backup at 2014-12-18 16:11:33
released channel: oem_backup_sbt1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/18/2014 16:11:33
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20020: database incarnation not set


RMAN> exit;

Recovery Manager complete.


select * from dual;

Tuesday, January 14, 2014

Simple Test on RAC Cache Fusion

RAC: Oracle Database 11.2.0.3
OS: Solaris 10 Sparc 64bit

Create a test table 'TEST_CACKE' and find out its OBJECT_ID
system@RACDB.RACDB_1.rac01> create table TEST_CACHE as select * from dba_tables;
Table created.
system@RACDB.RACDB_1.rac01> create index TEST_CACHE_IDX on TEST_CACHE(owner,table_name);
Index created.
system@RACDB.RACDB_1.rac01> select object_id from dba_objects where object_name='TEST_CACHE';
OBJECT_ID
----------
1707649 <--- Object id of table TEST_CACHE

Here, I also created a two-column composite index on columns (owner, table_name) to avoid full table scan and get the block cached normally. I will use the row which columns owner='SYS' ans table_name='CON$' in table TEST_CACHE as specimen:
system@RACDB.RACDB_1.rac01> select dbms_rowid.rowid_to_absolute_fno(rowid,'SYSTEM','TEST_CACHE') file#,
2 dbms_rowid.rowid_block_number(rowid) block#,
3 owner,table_name,tablespace_name
4 from test_cache
5* where owner='SYS' and table_name='CON$';

FILE# BLOCK# OWNER TABLE_NAME TABLESPACE_NAME
---------- ---------- ------- --------------- ------------------------------
1 122897 SYS CON$ SYSTEM <--- file# and block# which contains specified row

We can find that the row specimen is stored in BLOCK 122897 of FILE 1.
Find out if the block is cached:
system@RACDB.RACDB_1.rac01> select inst_id,status from gv$bh where file#=1 and block#=122897 and objd=1707649;
INST_ID STATUS
---------- ----------
1 scur <--- block#122897 is cached on instance 1

STATUS='scur' means block#122897 containing specified row is cached on instance 1. Yes, it was loaded from disk to memory by 'create index TEST_CACHE_IDX' statement.
To empty buffer cache:
system@RACDB.RACDB_1.rac01> alter system flush buffer_cache;
System altered.
system@RACDB.RACDB_1.rac01> select inst_id,status from gv$bh where file#=1 and block#=122897 and objd=1707649;
INST_ID STATUS
---------- ----------
1 free <--- cache memory of block#122897 is freed

The cache memory of block#122897 of table TEST_CACHE(object#1707649) is freed (not cached any more)
Re-load  block#122897 into cache on instance 1:
system@RACDB.RACDB_1.rac01> set autotrace on statistics
system@RACDB.RACDB_1.rac01> select owner,table_name,tablespace_name from test_cache where owner='SYS' and table_name='CON$';
OWNER TABLE_NAME TABLESPACE_NAME
------- --------------- ------------------------------
SYS CON$ SYSTEM

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
3 physical reads <--- Incur physical reads
0 redo size
297 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

The query caused '3  physical reads' to load table & index blocks into memory from disk.
Check cached data:
system@RACDB.RACDB_1.rac01> select inst_id,status from gv$bh where file#=1 and block#=122897 and objd=1707649;
INST_ID STATUS
---------- ----------
1 scur
1 free

block#122897 is cached on instance 1 again.
Now re-run the query and check the physical read:
system@RACDB.RACDB_1.rac01> select owner,table_name,tablespace_name from test_cache where owner='SYS' and table_name='CON$';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------- ------------------------------ ------------------------------
SYS CON$ SYSTEM

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
312 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

It is '0 physical reads' because of cache on instance 1. Ok, that is what is expected. What if I run the query from other instance of the RAC?
Check if the block is cached on instance 2:
system@RACDB.RACDB_2.rac02> select inst_id,status from gv$bh where file#=1 and block#=122897 and objd=1707649;
INST_ID STATUS
---------- ----------
1 scur
1 free

No, it is not cached on instance 2. Run the query on instance 2 and see what will happen:
system@RACDB.RACDB_2.rac02> select owner,table_name,tablespace_name from test_cache where owner='SYS' and table_name='CON$';

OWNER TABLE_NAME TABLESPACE_NAME
------- --------------- ------------------------------
SYS CON$ SYSTEM

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
295 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

There is no cache of the block on instance 2, but Oracle did not read the data from disk (0 physical reads). Instead, it read/copy the block from cache on instance 1.
Let's see the current cache status:
system@RACDB.RACDB_2.rac02> select inst_id,status from gv$bh where file#=1 and block#=122897 and objd=1707649;

INST_ID STATUS
---------- ----------
2 scur <--- block#122897 is cached on instance 2
1 scur <--- block#122897 is cached on instance 1
1 free

Both instances caches the block block#122897.

Thursday, July 4, 2013

Oracle Database alert showing ORA-02062

It showed up because failed distributed transaction cannot be committed or rolled back.

To identify failed transaction,

sys@DB01> alter session set nls_date_format='Mon-dd-yyyy HH24:MI:SS';

Session altered.

sys@DB01> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, FAIL_TIME, STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID            FAIL_TIME            STATE            MIXED
---------------------- ------------------------- -------------------- ---------------- -----
3.32.45182             DB01.d947e55d.3.32.45182  Jan-26-2010 09:42:09 collecting       no
                       

Wow, the issues has been there since Jan 26, 2010. Let's see who is the neighbor database (remote database),



sys@DB01> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID          IN_OUT INTERFACE DATABASE
---------------------- ------ --------- ----------------------
3.32.45182             in     N
3.32.45182             out    N         DB02


Check with DBA, the remote database DB02 was gone long time ago (Yes, Jan-26-2010 is pretty 'long time ago').Therefor, it is safe to purge this pending transaction,


sys@DB01> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.32.45182');

PL/SQL procedure successfully completed.

sys@DB01> commit;

Commit complete.

Doubel check if the pending transcation is gone,


sys@DB01> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,FAIL_TIME,STATE, MIXED FROM DBA_2PC_PENDING;

no rows selected


Check alert log, there is no new ORA-02062 found.

Wednesday, October 12, 2011

Using UTL_HTTP to access SSL (https) web sites

Demonstration in this article is given on Oracle Database - Enterprise Edition - version 11.2.0.3

Not like non-SSL (http) web site, accessing SSL (https) web sites with UTL_HTTP  needs additional configuration steps outside of PL/SQL programming as following.

1. Create wallet on database server where UTL_HTTP is called from

$ $ORACLE_HOME/bin/orapki wallet create -wallet /u01/app/oracle/admin/DB01/wallet -auto_login -pwd Pasword11203
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

If -auto_login option is not used, UTL_HTTP.SET_WALLET has to be called with the wallet password (here given Password11203). Check wallet built-in certificates:

$ $ORACLE_HOME/bin/orapki wallet display -wallet /u01/app/oracle/admin/DB01/wallet
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US


2. Import certificate of the web sites

Certificate file GoogleAccount.cer is exported from FireFox while https://account.google.com is open. If you do not know how to export certificate of your SSL web sites, try to GOOGLE with  "export ssl certificate from Internet Explorer Firefox".

$ $ORACLE_HOME/bin/orapki wallet add -wallet /u01/app/oracle/admin/DB01/wallet -trusted_cert -cert  /tmp/GoogleAccount.cer -pwd Pasword11203
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

$ $ORACLE_HOME/bin/orapki wallet display -wallet /u01/app/oracle/admin/DB01/wallet
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

The root certificate (CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US) of https://account.google.com is imported successfully.

3. Test UTL_HTTP

system@DB01> DECLARE
  2    v_http_request    UTL_HTTP.REQ;
  3    v_http_response   UTL_HTTP.RESP;
  4    v_text            VARCHAR2(255);
  5  BEGIN
  6    --UTL_HTTP.SET_PROXY ('username:password@proxy-server:port');  -- if proxy is needed
  7    UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet');
  8    v_http_request := UTL_HTTP.BEGIN_REQUEST(url => 'https://accounts.google.com', method => 'GET', http_version => 'HTTP/1.1');
  9    v_http_response := UTL_HTTP.GET_RESPONSE(v_http_request);
 10    UTL_HTTP.READ_TEXT(v_http_response, v_text, 100);
 11    DBMS_OUTPUT.PUT_LINE (v_text);
 12    UTL_HTTP.END_RESPONSE(v_http_response);
 13  END;
 14  /

<!DOCTYPE html>
<html lang="en">
  <head>
  <meta charset="utf-8">
  <meta content="width=300, init

PL/SQL procedure successfully completed.

If the wallet is created without '-auto_login' option, UTL_HTTP.SET_WALLET has to be called as UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet','Pasword11203').Here, 'Pasword11203' is password of the wallet.If the wallet is not configured properly or required certificate is not imported into wallet, UTL_HTTP will return 'ORA-29024: Certificate validation failure',

system@DB01> DECLARE
  2    v_http_request    UTL_HTTP.REQ;
  3    v_http_response   UTL_HTTP.RESP;
  4    v_text            VARCHAR2(255);
  5  BEGIN
  6    --UTL_HTTP.SET_PROXY ('username:password@proxy-server:port');  -- if proxy is needed
  7    --UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet');
  8    v_http_request := UTL_HTTP.BEGIN_REQUEST(url => 'https://accounts.google.com', method => 'GET', http_version => 'HTTP/1.1');
  9    v_http_response := UTL_HTTP.GET_RESPONSE(v_http_request);
 10    UTL_HTTP.READ_TEXT(v_http_response, v_text, 100);
 11    DBMS_OUTPUT.PUT_LINE (v_text);
 12    UTL_HTTP.END_RESPONSE(v_http_response);
 13  END;
 14  /
declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at line 8

Monday, September 28, 2009

Install DNS server on Red Hat Enterprise Linux (RHEL) AS 4 Update 8

Normally, DBA is far away from DNS server configuration. However, Oracle Database 11g Release 2 introduced an exciting new RAC feature SCAN (Single Client Access Name) witch needs to work with DNS. It becomes the motivation for DBA to study DNS configuration.

Saturday, June 6, 2009

Change Resolution of Red Hat Enterprise Linux Text Console

If Red Hat Enterprise Linux is installed without GUI (Gnome, KDE), or the system is running at runlevel 3, all the text appears big (low resolution) on local text-mode console as following,



Actually, this can be changed by adding 'vga=<resolution code>' option into grub boot loader configuration file '/boot/grub/grub.conf'.

Original grub.conf contains following line,

kernel /vmlinuz-2.6.9-89.EL ro root=LABEL=/ rhgb quiet


And we are going to set the resolution to 1024x768 24bit color and new file is changed as following,

kernel /vmlinuz-2.6.9-89.EL ro root=LABEL=/ rhgb quiet vga=792


Note: Resolution code (here is 792) really depends on hardware. It could be following values (come from Red Hat support),

   Depth      800×600     1024×768     1152×864     1280×1024     1600×1200
   8 bit         vga=771     vga=773        vga=353        vga=775         vga=796
   16 bit       vga=788     vga=791        vga=355        vga=794         vga=798
   24 bit       vga=789     vga=792        -------           vga=795         vga=799


After reboot, much more messages come up onto text console, the console becomes 'bigger',