Feed aggregator

runcluvfy.sh -pre crsinst NTP failed PRVF-07590 PRVG-01017

Michael Dinh - Wed, 2017-02-08 06:56

12c (12.1.0.2.0) RAC Oracle Linux Server release 7.3
/u01/software/grid/runcluvfy.sh stage -pre crsinst -n node1,node2 -verbose

Starting Clock synchronization checks using Network Time Protocol(NTP)...

Checking existence of NTP configuration file "/etc/ntp.conf" across nodes
  Node Name                             File exists?            
  ------------------------------------  ------------------------
  node02                                yes                     
  node01                                yes                     
The NTP configuration file "/etc/ntp.conf" is available on all nodes
NTP configuration file "/etc/ntp.conf" existence check passed

Checking daemon liveness...

Check: Liveness for "ntpd"
  Node Name                             Running?                
  ------------------------------------  ------------------------
  node02                                no                      
  node01                                yes                     
PRVF-7590 : "ntpd" is not running on node "node02"
PRVG-1017 : NTP configuration file is present on nodes "node02" on which NTP daemon or service was not running
Result: Clock synchronization check using Network Time Protocol(NTP) failed

NTP was indeed running on both nodes.
The issue is /var/run/ntpd.pid does not exist on the failed node.
NTP was started with incorrect options.

GOOD:

# cat /etc/sysconfig/ntpd
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

# systemctl status ntpd.service
ntpd.service - Network Time Service
   Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-02-03 20:37:18 CST; 3 days ago
 Main PID: 22517 (ntpd)
   CGroup: /system.slice/ntpd.service
           /usr/sbin/ntpd -u ntp:ntp -x -u ntp:ntp -p /var/run/ntpd.pid

# ll /var/run/ntpd.*
-rw-r--r-- 1 root root 5 Feb  3 20:37 /var/run/ntpd.pid

BAD:

# cat /etc/sysconfig/ntpd
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

# systemctl status ntpd.service
ntpd.service - Network Time Service           
   Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-02-03 18:10:23 CST; 3 days ago
 Main PID: 22403 (ntpd)
   CGroup: /system.slice/ntpd.service
           /usr/sbin/ntpd -u ntp:ntp -g           

# ll /var/run/ntpd.*
ls: cannot access /var/run/ntpd.*: No such file or directory

SOLUTION:

Restart ntpd on failed node.

ORA-06512 At Line Solution

Complete IT Professional - Wed, 2017-02-08 05:00
Did you get an ORA-06512 error when running an SQL query? Learn what this error is and how to resolve it in this article. ORA-06512 Cause The error message you get will look similar to this: ORA-06512: at line n. Where n is a line number. This error message is a generic PL/SQL error message […]
Categories: Development

Oracle 12c – RMAN list failure does not show any failure even if there is one

Yann Neuhaus - Wed, 2017-02-08 04:11

Relying to much on the RMAN Data Recovery Advisor is not always the best idea. In a lot of situations,  it tells you the right things, however, sometimes it tells you not the optimal things, and sometimes, RMAN list failure does not show any failure at all, even if there is one.

So … let’s simulate quickly a loss of a datafile during the normal runtime of the database. The result is a clear error message which says that the datafile 5 is missing.

SQL> select count(*) from hr.employees;
select count(*) from hr.employees
                        *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/oradata/DBTEST1/hrDBTEST01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Of course, the error message is immediately reflected in the alert.log as well where it clearly says that Oracle in unable to open file number 5.

Errors in file /u00/app/oracle/diag/rdbms/dbtest1/DBTEST1/trace/DBTEST1_smon_17115.trc:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/oradata/DBTEST1/hrDBTEST01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory

Only the RMAN Data Recovery advisor does not know what it’s going on.

RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

no failures found that match specification

Of course, I could shutdown the DB, and then startup again which would trigger a Health Check, but shutting down an instance is not always so easy on production systems. Especially when only one datafile is missing, but all others are available and only a part of the application is affected.

The solution to that issue, is to run a manual health check. Quite a lot of health checks can be run manually, like show in the following documentation.

https://docs.oracle.com/database/121/ADMIN/diag.htm#ADMIN11269

I start with the DB Structure Integrity Check. This check verifies the integrity of database files and reports failures if these files are inaccessible, corrupt or inconsistent.

SQL> begin
  2  dbms_hm.run_check ('DB Structure Integrity Check','Williams Check 00000001');
  3  end;
  4  /

PL/SQL procedure successfully completed.

After running the Health Check, Oracle finds the failure and in the alter.log you will see an entry like the following:

Checker run found 1 new persistent data failures

If you want to take a look what exactly the Health check found, you can invoke the ADRCI and execute the “show hm_run” command.

oracle@vmoratest1:/oracle/workshop/bombs/ [DBTEST1] adrci

ADRCI: Release 12.1.0.2.0 - Production on Tue Feb 7 16:02:21 2017

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

ADR base = "/u00/app/oracle"
adrci> show homes
ADR Homes:
diag/clients/user_oracle/host_1833655127_82
diag/tnslsnr/vmoratest1/listener
diag/rdbms/cdb1p/CDB1P
diag/rdbms/dbtest1/DBTEST1
diag/rdbms/rcat/RCAT

adrci> set home diag/rdbms/dbtest1/DBTEST1

adrci> show hm_run

ADR Home = /u00/app/oracle/diag/rdbms/dbtest1/DBTEST1:
*************************************************************************

...
...

**********************************************************
HM RUN RECORD 9
**********************************************************
   RUN_ID                        206
   RUN_NAME                      Williams Check 00000001
   CHECK_NAME                    DB Structure Integrity Check
   NAME_ID                       2
   MODE                          0
   START_TIME                    2017-02-07 16:03:44.431601 +01:00
   RESUME_TIME                   <NULL>
   END_TIME                      2017-02-07 16:03:44.478127 +01:00
   MODIFIED_TIME                 2017-02-07 16:03:44.478127 +01:00
   TIMEOUT                       0
   FLAGS                         0
   STATUS                        5
   SRC_INCIDENT_ID               0
   NUM_INCIDENTS                 0
   ERR_NUMBER                    0
   REPORT_FILE                   <NULL>
9 rows fetched

adrci>

However, if you take a look at the HM RUN report, is gives you an error.

adrci> show report hm_run 'Williams Check 00000001'
DIA-48415: Syntax error found in string [show report hm_run 'Williams Check 00000001'] at column [44]

This is not a bug. The HM run name must be only alphanumeric and underscore.  So … better don’t use spaces in between your name. The following would have been better.

SQL> begin
  2  dbms_hm.run_check ('DB Structure Integrity Check','WilliamsCheck');
  3  end;
  4  /

PL/SQL procedure successfully completed.

In case, the “adrci show report hm_run” does not work for you, it is not the end of the story. We still can look up the v$hm_finding view.

select RUN_ID, TIME_DETECTED, STATUS, DESCRIPTION, DAMAGE_DESCRIPTION from v$hm_finding where run_id = '206';

SQL> select RUN_ID, TIME_DETECTED, STATUS, DESCRIPTION, DAMAGE_DESCRIPTION from v$hm_finding where run_id = '206';

RUN_ID TIME_DETECTED                STATUS       DESCRIPTION                                  DAMAGE_DESCRIPTION
------ ---------------------------- ------------ -------------------------------------------- --------------------------------------------
   206 07-FEB-17 04.03.44.475000 PM OPEN         Datafile 5: '/u01/oradata/DBTEST1/hrDBTEST01 Some objects in tablespace HR might be unava
                                                 .dbf' is missing                             ilable

Now let’s check the RMAN “list failure” again.

RMAN> list failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected        Summary
---------- -------- --------- -------------------- -------
2          HIGH     OPEN      07-FEB-2017 15:39:38 One or more non-system datafiles are missing


RMAN> advise failure;
...
Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 5
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u00/app/oracle/diag/rdbms/dbtest1/DBTEST1/hm/reco_668410907.hm

  
RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u00/app/oracle/diag/rdbms/dbtest1/DBTEST1/hm/reco_668410907.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 5 offline';
   restore ( datafile 5 );
   recover datafile 5;
   sql 'alter database datafile 5 online';
Conclusion

The Oracle Data Recovery Advisor is quite good, but sometimes you need to push it into the right direction. Besides that, take care of the naming convention that you use for your health check runs. ;-)

 

Cet article Oracle 12c – RMAN list failure does not show any failure even if there is one est apparu en premier sur Blog dbi services.

Database 12.1 Extended Support Fee Waived through July 2019

Steven Chan - Wed, 2017-02-08 02:06

Oracle's Lifetime Support policy has three phases:  Premier Support, Extended Support, and Sustaining Support.  For details about coverage during each phase, see:

You can purchase a support plan for your licensed products to obtain Premier Support.  There is an additional fee for Extended Support. 

Premier Support for Database 12.1 runs to July 31, 2018. Extended Support for Database 12.1 runs to July 31, 2021. The Extended Support fee for Oracle Database 12c 12.1 has been waived to July 31, 2019.  See:

Related Articles


Categories: APPS Blogs

Changing the label of an item in Oracle APEX dynamically

Dimitri Gielis - Tue, 2017-02-07 16:37

Today I got the question how to change the label of an item in Oracle Application Express (APEX) based on some condition. I actually had this requirement myself a couple of times, so maybe other people too.

Here’s an example; whenever we change the Source item, we want the Affected Item to change it’s label:

 after change of the source item, the label of the affected item changes

The first thing that comes to mind (if you already know a little bit of APEX); lets use a Dynamic Action: on change of the Source item we will fire (in this example we will only fire when the value is A):

Dynamic Action in APEX

Now which action should we use when the dynamic action fires?

Default possibility of actions

Set Value will typically set the value of an Item, but what about the Label?
If I don’t find the option, I typically look for a plugin or write some code myself. In this case I wrote a bit of JavaScript, for example:

var newLabel = 'My new label for ' + $v('P2_SOURCE_ITEM');
$('#'+$(this.affectedElements).attr('id')+'_LABEL').html(newLabel);

This will set the label to "My new label for " and then the value of the item, at least if you select in the Affected Elements the item that needs the label change.

Whenever I think about writing custom code, my mind says “you should create a plugin for that”.
So I actually started to write an Oracle APEX Plug-in called “Set Label” (https://github.com/dgielis/orclapex-plugin-set-label)

While I was trying the plugin and writing up the things I needed to do, I guess something happend in my mind. I missed the obvious, it suddenly came to my mind there’s a much simpler solution to this…

You can actually use the Set Value action… just add after your item _LABEL, that’s it.

Use the Set Value dynamic action but add _LABEL to change the label of the item

Here’s the result:

enter image description here

Sometimes developing is much more simple than initially thought, you just have to see it :)

Categories: Development

DBMS_PARALLEL_EXECUTE getting chunks to work in special order

Tom Kyte - Tue, 2017-02-07 13:46
Hi Tom, I'm using DBMS_PARALLEL_EXECUTE package to run in parallel my PL/SQL procedure's work. The chunks are generated by my own SQL on table which contains numeric field "priority" like this <code> v_sql := ' select rowid, rowid ...
Categories: DBA Blogs

Real time scenarios

Tom Kyte - Tue, 2017-02-07 13:46
friends, I am searching complex real time scenarios and solutions on PL/SQL and SQL, Please provide reference documentation links if any. this will b great helpful Thanks, vin
Categories: DBA Blogs

Flush buffer cache and shared pool

Tom Kyte - Tue, 2017-02-07 13:46
Hi Tom, We have an application performing many inserts and updates from many machines. At peak time, we may have the application running on 300 machines performing inserts and updates. Once in a while, we saw some active sessions blocking other sess...
Categories: DBA Blogs

Question on ORA-12899: value too large

Tom Kyte - Tue, 2017-02-07 13:46
Hi Tom, We have a migration project which from Sybase SQL Anywhere to Oracle. And there is an issue we still have no perfect solution. In Sybase, When insert/update to a target column, the source string will be auto truncate if the length more t...
Categories: DBA Blogs

right way to grant permissions to developers

Tom Kyte - Tue, 2017-02-07 13:46
hello, i am junior dba with quite a little knoweledge about oracle. lately i wanted to make users for plsql developers who work on production database with the same user-bill(which has DBA role). i wanted to make them their own user for security p...
Categories: DBA Blogs

Error(24,1): PLS-00103: Encountered the symbol "BEGIN"

Tom Kyte - Tue, 2017-02-07 13:46
I wonder where it shows PLS-00103: Encountered the symbol "BEGIN" when i compile the procedure. please help <code> create or replace PROCEDURE GETCTRLEDPTDETAILSBYIPADDRR ( v_ipaddress IN NVARCHAR2 DEFAULT NULL , v_organizationId IN NUM...
Categories: DBA Blogs

Passing multiple value in IN clause in SQL query

Tom Kyte - Tue, 2017-02-07 13:46
Hi I have SQL query which takes around 50000 as input value in IN clause. What is best approach to passing value into IN clause which are more than 1000 in count.
Categories: DBA Blogs

Dynamic SQL

Tom Kyte - Tue, 2017-02-07 13:46
Hi Tom, I have the following dynamic sql statement, I want to return the rowid of the inserted record into a variable but I get a command not properly ended error. What is the problem? stmt:= 'INSERT INTO ' || destination_table_name || ' ( ' |...
Categories: DBA Blogs

Parting With ocm.rsp Isn’t Really Sweet Sorrow…

Pythian Group - Tue, 2017-02-07 13:00

Now that we’re well into 2017, let’s sit back and ask the question: “What was the best news of 2016 in the Oracle ecosystem?”

I’ll give you a clue.  The best news was something that sneakily appeared between April and October 2016 in your patch maintenance logs :

[root@myclusterdb02 OPatch]# ./opatchauto apply /patches/OCT2016_bundle_patch/24436624/Database/12.1.0.2.0/12.1.0.2.161018DBBP/24448103 -oh /u01/app/oracle/product/12.1.0.2/dbhome_1 -ocmrf /u01/app/oracle/product/12.1.0.2/dbhome_1/OPatch/ocm/bin/ocm.rsp
System initialization log file is /u01/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatchautodb/systemconfig2016-12-11_05-23-36PM.log.
Session log file is /u01/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatchauto/opatchauto2016-12-11_05-24-09PM.log
WARNING: the option -ocmrf is deprecated and no longer needed. OPatch no longer checks for OCM configuration. It will be removed in a future release.

The interesting part is in that 4th line :

WARNING: the option -ocmrf is deprecated and no longer needed. OPatch no longer checks for OCM configuration. It will be removed in a future release.

This is awesome! We no longer need this annoying ocm.rsp file !

When I discovered this back in November 2016, I searched for an official feature announcement but found nothing (it looks like an opatch 13c new feature backported in 11g and 12c). This ocm.rsp file was still needed when I applied the April 2016 PSU, but from which opatch version was it implemented?

First, I looked at the 2016 dev comments in opatch 11.2.0.3.15 and 12.2.0.1.7 (which were the last version when I was applying this Exadata October 2016 PSU) and I sadly discovered that no specific comments were talking about this new feature (even if it would obviously deserve some):

#  opatch  04/08/16  Update CAS_VERSION to 12.2.0.1.3
#  opatch  04/11/16  Consolidate all 4 opatch scripts
#  opatch  06/07/16  Calling auto_patch.pl using the perl from Oracle Home Location
#  opatch  06/30/16  bug 23721730 - default memory was too high for 32 bit so removed and disabled opatch_env.sh script call

 

After a quick look at the code, I found the amazing piece of code that would ease our lives in the future (the function is named ocmOptionDetect()):

# Option 'ocmrf' is deprecated in OPatch 13.3 - Detect and give warning message
if [ "$OCMRF_OPTION" = "1" ] && [ "$VERSION_13_X" = "1" ]; then
echo "WARNING: the option \"-ocmrf\" is deprecated and no longer needed.  OPatch no longer checks for OCM configuration. It will be removed in a future release."

 

I then performed a few greps in different opatch versions that I had and I found that this feature has been implemented in:

  • 11.2.0.3.13 or 11.2.0.3.14 for 11g (I was unable to find any of these versions so I couldn’t check)
  • Between versions 12.1.0.1.10 and 12.2.0.1.7 for 12c (again, I was unable to find any of these versions so I couldn’t check)

If you come across any of the opatch versions specified above, please “grep -i ocmrf opatch” it and let me know so I can find when this feature has been implemented.

You can now upgrade all your opatches to become ocm.rsp-free and this (for me) was definitely the best new of 2016!

Categories: DBA Blogs

Setting Invalid Fields for the UI in ADF BC Groovy

Andrejus Baranovski - Tue, 2017-02-07 10:23
What if you have entity level validation rule and want to attach validation error message to specific field. By default this is not possible - all entity level validation error messages are displayed in the popup and are not attached to the fields (differently than attribute level validation rule messages).

Apparently there is a way to achieve such requirement with Groovy expression, this can be executed from entity level validation - adf.error.addAttribute('Salary'). In addAttribute you need to provide attribute name which will be assigned with the error. Complete expression for entity validator:


Result displayed on UI - validation error message is assigned to the field, which was changed:


Download sample application - GroovyADFApp_v2.zip.

Profiles of the Future

Michael Armstrong-Smith - Tue, 2017-02-07 09:37
I've been thinking about the laws of the universe recently and came across a book written by the science fiction master Arthur C. Clarke called Profiles of the Future. Over the course of many years and several revisions he put forward the hypothesis that there are 3 laws for predicting the future. These are:
Clarke's first law
When a distinguished but elderly scientist states that something is possible, he is almost certainly right. When he states that something is impossible, he is very probably wrong.
Clarke's second law
The only way of discovering the limits of the possible is to venture a little way past them into the impossible.
Clarke's third law
Any sufficiently advanced technology is indistinguishable from magic.

The third one has various alternative explanations such as:

Any technology, no matter how primitive, is magic to those who don't understand it ,and
Any sufficiently ancient recovered wisdom or artifact is also indistinguishable from magic
There's a couple of variations I like that are particularly related to the field of computing in which I work. 
These are:
Any sufficiently advanced technology is indistinguishable from a rigged demo, and
Software tests that inexplicably pass or fail are indistinguishable from voodoo
Over the course of my career I've seen the truth of these last two. Many years ago I had the privilege of being invited to the demo of a new piece of software that was slated to be years ahead of its time. It was being shown to us by an executive of that company and what we saw really was a leap beyond anything any of us had seen before that is, until one of us spotted that what appeared to be buttons being clicked on a screen actually looked suspiciously like a PowerPoint presentation moving from one picture to another. The buttons were not depressing and the positioning of some of the buttons moved ever so slightly from one screen to another.
At one point a question was asked from the audience if we could go back to the previous screen to look and see what one of the other buttons could do. Of course this would have been impossible but the presenter was slick and had anticipated such a request when his reply was that we had so much to cover in a short period of time and would love to show that feature in a separate demo after the main presentation.
When the presentation was over, some of us went to ask about that feature to be told that the link back to the database at head office had gone down and therefore we would not be able to see a demo of that feature after all.

CIO Review: The Future of Content is NOW

WebCenter Team - Tue, 2017-02-07 08:59

By this time, we are all bought into the value that a Cloud infrastructure brings. So, if you are in Information Technology (IT), you are looking to best leverage a single, centralized Cloud platform to meet your needs. As you think through your Cloud investments, consider an iterative approach where you start off looking for the biggest bang for your money and an initiative that earns you instant results (and credibility) with the various lines of businesses.

I recently wrote a feature for the CIO Review publication in which I  explored how a Cloud content hub would create value for both IT and the different lines of businesses. In it, I also shared an example of one of our customers, Omni Financiera to discuss how they are choosing to get immediate value from their Cloud investments. Take a read and see if this could be your next step in the Cloud. As always, I would welcome an open dialog and look forward to hearing from you on your thoughts.

Hope you enjoy the feature.


Database 11.2 Extended Support Fee Waived through December 2018

Steven Chan - Tue, 2017-02-07 02:06

Oracle's Lifetime Support policy has three phases:  Premier Support, Extended Support, and Sustaining Support.  For details about coverage during each phase, see:

You can purchase a support plan for your licensed products to obtain Premier Support.  There is an additional fee for Extended Support. 

Extended Support for Database 11.2 runs to December 31, 2020. The Extended Support fee for Oracle Database 11gR2 11.2 has been waived to December 31, 2018.  See:

Related Articles


Categories: APPS Blogs

Links for 2017-02-06 [del.icio.us]

Categories: DBA Blogs

Query for generating resultset with continuos dates for existing data

Tom Kyte - Mon, 2017-02-06 19:26
Dear Tom, I have a table which stores all the account balances with dates. A row is inserted into this table only for those days where the account has transactions. The table looks as below. create table account_balance (account varchar2(16), b...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator