Posts

Showing posts from 2016

oracle 12c sqlldr error in windows oranfsodm12.dll missing

Image
We Installed oracle 12c client in windows 12 R2. sqlplus is wokring fine but sqlldr is failing with error  "oranfsodm12.dll is missing from your computer". Error screenshot: Solution: Make a copy of the oraodm12.dll in the bin directory and rename it to oranfsodm12.dll .

Password verify function with one Capital Letter check

Image
Password verify function with following checks. We can use password function provided by ORACLE but it contains around 9 checks like: 1.Check if the password is same as the username  2.Check if the password is same as the username reversed  3.Check if the password differs from the previous password by at least 3 letters  4.Check if the password is the same as server name  5.Check if the password is too simple. A dictionary of words may be maintained and a check may be made so as not to allow the words that are too simple for the password.  6.Check if the password is the same as oracle  But it doesn't contain "One Capital Letter Check"  Below Function with 4 checks only, customized as per customer requirement : *minimum length of 8 characters  * one capital letter   * one number  * one special character  ========================== CREATE OR REPLACE FUNCTION verify_function_anil (username varchar2,   password varchar2,   old_password

Oracle Software Editions available from 12.2​ in cloud

Image

Speed up Data Pump Import by suppressing redo generation

Customer Request : Import 20GB  Dump file without downtime(Without changing into noarchivelog mode). Problem: Database running in Archive log mode  and my archive destination size is very limited. Solution: Using  transform=disable_archive_logging:Y we can avoid redo generation and it will speedup import. Test:  Without Feature:  ============  Before Import:  SQL> select * from v$recovery_area_usage where FILE_TYPE='ARCHIVED LOG'; FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- ARCHIVED LOG                          1.74                         0               7          0 After Import:  SQL> set linesize 400 SQL> select * from v$recovery_area_usage where FILE_TYPE='ARCHIVED LOG'; FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID ------------------

IMPDP is Taking Long Time at defining state

IMPDP is Taking Long Time at defining state with SYS User. Defining State itself taking more than 3 hours and actual data import finishing in 1 hour. We tried setting all performance parameters but no luck. Note: We are doing import using sys user in 12c Database (Exported from 11g Database). After struggling one week still no luck :( Solution :  we tried import with  system user by mistake and defining state completed in 2 Minutes :) use SYSTEM, not SYS on datapump. SYS is only for the legacy exp/imp.  From 12c Use SYSTEM user  to avoid Import performance issues. impdp system/manager dumpfile=test:filename.dmp logfile=test:filename.log

Oracle Database 12.2 books

All Database 12.2 Books link: http://docs.oracle.com/database/122/nav/portal_booklist.htm

Oracle Database 12.2.0.1 New Init Parameters

Oracle Database 12.2.0.1 is available now in the Oracle Cloud. And this is the list of 46 new init.ora/spfile parameters compared to Oracle Database 12.1.0.2 - including the links (where possible) to the Oracle Database 12.2 Reference documentation. Parameter Description allow_global_dblinks LDAP lookup for DBLINKS allow_group_access_to_sga Allow read access for SGA to users of Oracle owner group approx_for_aggregation Replace exact aggregation with approximate aggregation approx_for_count_distinct Replace count distinct with approx_count_distinct approx_for_percentile Replace percentile_* with approx_percentile asm_io_processes number of I/O processes per domain in the ASM IOSERVER instance autotask_max_active_pdbs Setting for Autotask Maximum Maintenance PDBs awr_pdb_autoflush_enabled Enable/Disable AWR automatic PDB flushing cdb_cluster [undocumented if TRUE startup in CDB Cluster mode cdb_cluster_name [undocumente

How to Recover Agent Home on OMS got deleted accidentally

How to Recover Agent Home on OMS got deleted accidentally Ensure that no agent java process is alive from the agent home 1. Rename agent_inst directory under agent base: /usr/oraoms/product/Agent12c/* --> You donot have agent_inst dir 2. Run the following command to reconfigure agent: NOTE: Replace all the required fields in the below command as per your environment $ cd /usr/oraoms/product/Agent12c/core/12.1.0.5.0/sysman/install $ agentDeploy.bat AGENT_BASE_DIR=/usr/oraoms/product/Agent12c AGENT_INSTANCE_HOME=/usr/oraoms/product/Agent12c/agent_inst AGENT_PORT=<Agent-Port> -configOnly OMS_HOST=<OMS HOSTNAME WITH DOMAIN> EM_UPLOAD_PORT=<OMS HTTPS UPLOAD PORT> AGENT_REGISTRATION_PASSWORD=<PROVIDE AGENT REGISTRATION PASSWORD> 3. If the above command completes successfully then follow the below steps: <AGENT_HOME>/BIN> ./emctl stop agent <AGENT_HOME>/BIN> ./emctl secure agent <AGENT_HOME>/BIN> ./emctl start agent <

OEM 13C Installation fails pre-requisites are getting failed in REDHAT 6.8

Image
We are installing OEM13C in REDHAT 6.8 and pre-requisites are getting failed due to the non-certified version. The certification information on My Oracle Support lists Red Hat Enterprise Linux 6 as a certified platform for "Enterprise Manager Base Platform - OMS 13.1.0.0.0". The certification information does not list any specific version of Red Hat Enterprise Linux 6, such as 6.1, 6,2, 6,3, 6,4, 6.5, 6.6, 6.7, etc., it only says "Red Hat Enterprise Linux 6". However the EM 13.1 installation process doesn't recognize 6.8 as a certified version of RHEL 6. The error from the installer is shown below. The installer will not allow this to be skipped so the install can continue.   13.1 EM install does not support on RHEL6.8. This is taken care in next release of 13c OEM. For 13.1 EM the following documentation bug is logged to get the OTN document updated with this information: Bug 23328216 - 13c EM install on RHEL6.8 Fails Workaround: Once t

RMAN Catalog RC_BACKUP_FILES View access Issue

Image
When I am trying to access RC_BACKUP_FILES view from our RMAN Catalog database,  getting following error .. SQL> select count(*) from RC_BACKUP_FILES; select count(*) from RC_BACKUP_FILES * ERROR at line 1: ORA-20021: database not set ORA-06512: at "RCAT_EMB.DBMS_RCVMAN", line 26137 ORA-06512: at "RCAT_EMB.RC_LBRECSETIMPL_T", line 69 Workaround: It is usually more convenient to access this information using the LIST BACKUP and LIST COPY  commands from within RMAN. We must use DBMS_RCVMAN.SetDatabase to select a database from the recovery catalog schema before you can use this view. SQL> SELECT NAME,DBID FROM V$DATABASE; NAME            DBID --------- ---------- XXXXXX 3430087157                                                                                                                                                                         DBID  => 3430087157 SQL> CALL DBMS_RCVMAN.SETDATABASE(nul

/U01 Space Consumption By CVUCHECKREPORT.XML In Cluster

Image
/U01 Space Consumption By CVUCHECKREPORT.XML In Cluster We are observing /u01 is full in RAC Environments due to CVUCHECKREPORT.XML Getting created and not purged in  /u01/app/oracle/crsdata/@global/cvu/baseline/cvures/   Oracle Provided a Patch to fix  this Bug 20177779 in April PSU . We have already applied April PSU but issue was not fixed. CVU HEALTH CHECK REPORTS NOT PURGED EVEN AFTER APPLYING PATCH FOR 20177779 (Patch). Bug 20177779 has been replaced by new bug 22594310. So Now we need to Apply 22594310 to fix this bug in all Cluster environments. Workaround: Purge CVUCHECKREPORT.XML files Manually till we apply this Patch. -anil12cdba

Useful OEM Queries to get Target details from OEM Repository

List Targets with TNS Listener ports configured : SELECT mgmt$target.host_name , mgmt$target.target_name , mgmt$target.target_type , mgmt$target_properties.property_name , mgmt$target_properties.property_value FROM mgmt$target , mgmt$target_properties WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name ) AND ( mgmt$target.target_type = mgmt$target_properties.target_type ) and ( mgmt$target.target_type = 'oracle_listener' ) and ( mgmt$target_properties.property_name = 'Port' ); Devora02       LISTENER_ora02                       oracle_listener Port 1529 Devora01       LISTENER_ora01                       oracle_listener Port 1529 Devora04       LISTENER_ora04                       oracle_listener Port 1529 List Machine_Names, CPU Count & Database Verion for Licensing SELECT mgmt$target.host_name , mgmt$target_properties.property_name , mgmt$target_properties.property_value FROM mgmt$target , mgmt$target_properties WHERE ( mgmt$

Patching Prereq Failing

Image
opatch prereq CheckConflict fails with Exception occured : PatchObject constructor:Input file does not exist Patch Prereq step is failing with following error.. The OPatch option of  -phBaseDir  option is used to specify the path to base dir where all the patches to be applied are kept.  The command enterred was $ opatch prereq CheckConflictAgainstOHWithDetail -ph ./  OPatch will look in $ORACLE_HOME/patches for the following directory structure: $ORACLE_HOME/patches/ 23615381 /etc/config Since unzipping the patch creates multiple directories, that directory structure does not exist and  -phBaseDir  must be the directory that is created when the patch is unzipped.  -phBaseDir $/u01/software/patches/23615381/23177648

Oracle Database 12.2 New Features

********************************************** 12.2 New  Features from one of the presentation. ********************************************** AWR now available on PDB level Memory management on PDB level PDB cloning is now possible also online Flashback now possible on PDB level Backup / Restore on PDB level with RMAN

Applying Incremental Backups on Standby Database.

Standby Database is not Sync with Primary and gap is more than 40 days.. We don't have archivelog backups older than 30 days to apply on Standby Database. Instead of Rebuilding entire Standby Database we used Incremental Backup recover Standy database. Steps Followed briefly: 1 . Take current SCN From Standby Database 2. Take Primary DB Backup using FROM SCN. 3.Create standby controlfile 4. Copy Backups to Standby Server 5. shutdown standby ,startup nomount, Restore Standby control file & startup mount 6.   REcovery database noredo 7. alter database recover managed standby database disconnect from session; 8. Verify Standby Gap. Standby: ======== Current SCN From Standby Database: ---------------------------------- SQL> col current_scn for 999,999,999,999,999,9999 SQL>  select current_scn from v$database;               CURRENT_SCN -------------------------          917,102,315,7647 Cancel Recovery of Standby Database: ------------------------

Proxy User and Connect Through

SYS@S2411S > alter user S24RMAN grant connect through SREE;   User altered.   SYS@S2411S >   [oracle@s24-oratl15 ~]$ sqlplus SREE[S24RMAN]/long99991234@S2411S   SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 30 08:34:55 2016 Copyright (c) 1982, 2013, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production S24RMAN@S2411S > sho user USER is "S24RMAN" S24RMAN@S2411S >   Revoke : ------------- As sysdba > alter user S24RMAN revoke connect through SREE;   User altered.

Force SQL Plan

How to Force SQL PLAN: Find the old query plan for an sql_id ====================================== set long 90000 SET PAGESIZE 14 set lines 150 pages 50 column begin_interval_time format a25 alter session set nls_date_format='dd-mon-yy hh24:mi:ss'; select begin_interval_time,plan_hash_value,cpu_time_delta,elapsed_time_delta,iowait_delta,disk_reads_delta from dba_hist_sqlstat A,dba_hist_snapshot B where A.snap_id = B.snap_id and A.sql_id = '&sql_id' order by 1; Forcing a SQL Plan : ====================== Step 1: ---------- SPO coe_xfr_sql_profile.log; SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQ LP SQL>; REM REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.1.4 2010/07/12 csierra $ REM REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved. REM REM AUTHOR R