Tag: Osama mustafa blog

Remove Oracle / Uninstall

Posted on by Osama Mustafa in Database
I post this topics for people who want to learn how to remove Oracle For Window/Linux 


Windows :

Its easy common steps you have to follow :

  • Uninstall all Oracle components using the Oracle Universal Installer (OUI).
  • Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key. This contains registry entires for all Oracle products.
  • Delete any references to Oracle services left behind in the following part of the registry (HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*). It should be pretty obvious which ones relate to Oracle.
  • Reboot your machine.
  • Delete the “C:\Oracle” directory, or whatever directory is your ORACLE_BASE.
  • Delete the “C:\Program Files\Oracle” directory.
  • Empty the contents of your “C:\temp” directory.
  • Empty your recycle bin.

Unix :

  • Uninstall all Oracle components using the Oracle Universal Installer (OUI).
  • Stop All Oracle Process .

lsnrctl stop
emctl stop dbconsole
emctl stop agent.

  • Delete the files and directories below the $ORACLE_HOME.
# cd $ORACLE_HOME
# rm -Rf *
  •  delete directories below the $ORACLE_BASE.

 # cd $ORACLE_BASE
# rm -Rf admin

  •  Delete the /etc/oratab file

Done .

Osama Mustafa

PL/SQL developer: Dynamic Performance Tables not accessible

Posted on by Osama Mustafa in Database
ERROR HAPPENED WHEN :

Login into Pl/sql developer (software)
 



Solution:

1.grant select any dictionary to username
2.disable this function by

Tools –> Preference –> option –> uncheck option “Automatic statistics”

Oracle On Windows vs Linux

Posted on by Osama Mustafa in Operating system
Today i will talk about Compare Oracle Installation On Windows vs Linux , and just to let you know oracle recommended Linux .

1-Installation Oracle on Windows / Linux

For installation of Oracle on windows doesn’t require any other user creation, we can perform oracle installation using “administrator” superuser of windows. For installation of Oracle on Unix/linux required to creating separate operating system user account. Using super user “root” we doesn’t require to perform Oracle installation.

For installation of Oracle on windows, if we create separate operating system then it should be group of super user administrator. For installation of Oracle on Unix/Linux, when we create operating system user then it should be not part of super user group.

2-Default Location Of Windows , Linux :

Default location of password file and parameter file for Windows is ORACLE_HOME\database folder.Default location of password file and parameter file for Unix/Linux is ORACLE_HOME/dbs folder.

ORACLE_BASE,ORACLE_HOME,ORACLE_SID are defined in registry of Windows as HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0. ORACLE_BASE,ORACLE_HOME,ORACLE_SID are defined as user’s environment variables in Unix/Linux.

3-Symbolic Links windows , Linux

Symbolic links are NOT supported for user’s environment variables or registry parameter in Windows. Symbolic links are supported for user’s environment variables in Unix/Linux.

4-Enviorment variable in Windows, Linux 

In windows we should need to set environment variable using “set” command and it doesn’t save in user profile. In Unix and Linux we should need to set environment variable using “export” command and it can save using .profile (in Unix) and .bash_profile (in Linux).

5-Shared Library DLL windows , Linux 

Oracle’s shared libraries are called as shared DLL in windows. Oracle’s shared libraries are available in Unix/Linux.

6-Relinking On Windows , Linux

Relinking of Oracle executable is not available in Windows. Relinking of Oracle executable is available in Unix/Linux.

7-Shared memory Segment Windows, Linux

Shared memory , shared segments and semaphores are NOT adjustable in Windows. Shared memory segment(SHMMAX), shared segments (SHMMNI) and semaphores (SEMMNS) are adjustable using kernel parameters in Unix/Linux.

8-Memory and processes In Windows , Linux

Oracle’s SGA locking in real memory doesn’t possible in Windows. Oracle’s SGA locking in real memory is possible in Unix/Linux.

Each background process of Oracle is implementing as Thread inside single process in Windows. Each background process of Oracle is a process in Unix/Linux.

9-GUI , CLUI Windows , Linux 

Windows called as GUI because it provides Graphical User Interface. Unix and Linux called as CLUI called Command Line User Interface. Due to this reason Unix and Linux provides more performance than Windows due to resource utilization.

10-File System , Security Windows , Linux

Windows is flat file system. Unix and Linux is hierarchical model file system. Windows kernel stores in couple of files like Registry. Unix and Linux kernel stores in many files which are hierarchy. It is very easy to understand Unix and Linux file systems in any version.

Earlier FAT and FAT32 file system has no security in Windows. Using NTFS file system windows use file permission based security. In Unix and Linux has traditional file permission security with owner,group and other users.Unix has greater built-in security and permissions features than Windows. Linux contains also same type of security and permissions logic like Unix.

11-Mointer Oracle In Windows, Linux

There are very few utilities available in Windows for performance monitoring and administration. There are lot of command line utilities are available in Unix/Linux for performance monitoring and administration.

12-Source Code Windows , Linux 

Source code of Operating system doesn’t available in Windows. Source code of Operating system is available in some of Linux flavors, means we can modify source code of operating system.

13-Skills Windows , Linux 

Oracle on Windows magnetize because easy to understand, easy to maintain, easy to develop, resource availability and with good support. Oracle on Unix/Linux is not easy to understand,easy to maintain or easy to develop because it requires high skill set and depth knowledge.

Oracle deployment is very easy in Windows because not need to more knowledge or special skill sets. Oracle deployment is not easy in Unix/Linux because it requires special skill sets.

Windows is user friendly operating system. Unix and Linux doesn’t user friendly operating system.

14-Virus Windows , Linux 

There is high risk of virus attacks on Windows. Because majority of windows users run as Administrator and virus can be affecting on any of files of kernel due to super user account. There is minimum risk for virus attacks on Unix and Linux. Because most of Unix box or Linux box is being run by user interface not using “root” super user. Due to this reason virus attacker cannot able to modify kernel of operating system. 

Thank you
Osama Mustafa

RMAN-20001: target database not found in recovery catalog

Posted on by Osama Mustafa in Database
===================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
RMAN-00571: ===================================================
RMAN-03002: failure of list command at 11/13/2011 03:26:18

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

  1. Connect to RMAN Catalog
$ rman catalog rman/rman@rmancat target rmanbkup/rmanbkup@orcl
Recovery Manager: Release 11.2.0.2.0 – Production on Sun Nov 13 03:25:49 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: orcl (DBID=5858479612)
connected to recovery catalog database
  1. Unregister the database
RMAN>unregister database noprompt;
  1. Register Database to RMAN catalog
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog

full resync complete

Script to Extract Code to Recreate Materialized

Posted on by Osama Mustafa in Scripts
This function will extract the DDL for an existing materialized view

SET serveroutput on
SET feedback off
UNDEF v_sql

DECLARE
   v_task_name       VARCHAR2 (100);
   v_mview_owner     VARCHAR2 (30)   := UPPER ('&&ENTER_MVIEW_OWNER');
   v_mview_name      VARCHAR2 (30)   := UPPER ('&&ENTER_MVIEW_NAME');
   v_mview_sql       VARCHAR2 (4000);
   v_mview_log_sql   VARCHAR2 (4000);
BEGIN
   -- get mview text from data dictionary
   SELECT DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', mview_name, owner)
     INTO v_mview_sql
     FROM dba_mviews
    WHERE owner = v_mview_owner AND mview_name = v_mview_name;

   SELECT DBMS_METADATA.get_dependent_ddl ('MATERIALIZED_VIEW_LOG',
                                           referenced_name,
                                           referenced_owner
                                          )
     INTO v_mview_log_sql
     FROM dba_dependencies
    WHERE referenced_type = 'TABLE'
      AND referenced_name != v_mview_name
      AND owner = v_mview_owner
      AND NAME = v_mview_name;

   DBMS_OUTPUT.put_line ('MVIEW SQL Is: ' || v_mview_sql);
   DBMS_OUTPUT.put_line ('MVIEW LOG SQL Is: ' || v_mview_log_sql);
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

OER 7451 in Load Indicator

Posted on by Osama Mustafa in Operating system
The Error Appear in Alertlog like :

Mon Aug 06 03:57:54 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Mon Aug 06 03:58:04 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Mon Aug 06 03:58:14 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Mon Aug 06 03:58:24 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Mon Aug 06 03:58:34 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Mon Aug 06 03:58:44 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Mon Aug 06 03:59:05 2012
O/S-Error: (OS 1) Incorrect function. !

Oracle Support points to Doc. ID 1060806.1

Cause
Installed 32-bit Oracle database software on a 64-bit MS Windows OS which is not supported.Note: For the Database software, you can ONLY install the x64 version on MS Windows (x64).  You can NOT install the 32-bit version Database software on MS Windows (x64).
Solution
Install 32-bit Oracle database software only on 32-bit MS Windows OS.

Article / Data Guard

Posted on by Osama Mustafa in Articles, Database
Today while I am Browsing i read this amazing article about Data Guard and want to share it .


What Is Data Guard 

Oracle Data Guard delivers high availability, data protection, and disaster recovery for the enterprise data. 

Data Guard configuration consists of one production database, having primary role, and one or more standby databases, having standby roles.

Standby database can take over the production service either for planned or unplanned outages.
Apart from that it can be used for offloading the reporting and backup operations from production.

The databases in a Data Guard configuration are connected by Oracle Net and might be arranged geographically. 

There are no strict network requirements for the databases to be in the same subnet and etc. It could be LAN, WAN or internet connection.

Data Guard configuration and control could be managed via:

  • SQL command line interface;
  • Data Guard Broker interface: using DGMRL for command line interface and GUI delivered via Oracle Enterprise Manager. Data Guard broker tool is included in Enterprise edition license. This tool provides simplified and automatic data guard maintenance and configuration procedures. How to use it I will describe later in this article.

Oracle Data Guard could be deployed among different platforms(x86, Power, Itanium, SPARC), Operating systems(32/64 bit) and Oracle software versions (32/64bit) with limitations.

Check This notes : 413484.1 and 1085687.1 On MOS.

Data Guard deployment examples

 

Examples provided here are using fast start failover technology for automatic failover operations.
Without fast start failover in place all failover operations must be performed manually.

 The depiction of solution above demonstrates high level HA ready deployment leveraging fast start fail-over configuration and active data guard option on physical standby for reporting&queries. 

Here observer is data guard broker executable utility, used in fast start failover configurations, which  could run on either laptop, server or workstation.


Disaster ready  setup


The depiction of solution below demonstrates high level DR ready deployment leveraging fast start failover configuration and logical standby for reporting&queries. As depicted it involves 3 different sites to provide desired functionality. Although it is not intended to provide reporting services in case of Site A outage, it could be done by putting additional standby server in site B though.

 


Standby databases overview

Data Guard provides three different types of standby databases:

  • Physical standby. This is physically identical copy of primary database compared on block level. All database physical and logical structures like data files, schemas are the same. Standby database is synchronized through Redo Apply mechanism, which delivers redo data and applies it on the physical standby database. For such purpose so called standby redo logs are used in standby which are providing similar functionality as online redo logs.
  • Logical standby. This type of database contains only the same logical information as the production database, but the physical structure  of the data can be different, like different data file organization and etc.  The logical standby database is synchronized through SQL Apply mechanism, which first transforms the data, in the redo received, into SQL statements – DML’s and DDL’s – and then executes them on the standby. Here LogMiner component of SQL Apply is used. Standby databases are functioning in read write mode.  
  • Snapshot standby. This type of physical standby database appeared first with 11g release. It is more like a data guard mode than a different configuration. The only difference from latter ones, when enabled it is fully operational – read and write mode –  production database copy, where users can access and perform any data manipulations. Meantime redo data is being received from the primary database, archived, but not applied until snapshot standby database is reverted back to the physical standby database.

Physical standby

  • Starting with Oracle database release 11g there is new active data guard feature which allows reporting and queries while redo apply is active on physical standby. This data guard option must be purchased as add-on to Enterprise edition license. 
  • Can be used to offload backup operations from production server. 
  • Supports all database datatypes, types of tables, DDL and DML operations

* Logical standby  

  • Typically is used for reporting and queries while sql apply is active on logical standby.
  • Could be used for database upgrades with minimal downtime.
  • Can maintain other database objects not belonging to primary database. 
  • Logical standby database has limitation on support of datatypes, types of tables, DDL and DML operations.

Data Guard protection modes

Databases running with Data Guard implemented could be configured to act differently when primary server outage is taking place. 


Maximum performance
Default protection mode. This protection mode provides maximum protection without affecting primary database performance. Here transactions on primary are committed as soon as redo log is filled with all relevant redo data without waiting for write accomplishment from standby databases – it is being done asynchronously. Therefore such protection mode does not guarantee complete data restore until last transaction.
Maximum availability
This protection mode guarantees that no data loss will occur if primary database fails. Transactions do not commit until all relevant redo data is written in redo and standby redo logs of standby database. In such configuration standby database is kept completely synchronized with primary. In case of standby database outage or whatever reasons preventing to write/send redo data into standby locations, primary database is kept running to preserver availability.
Maximum protection
This protection mode guarantees that no data loss will occur if primary database fails. The only difference from the Maximum Availability mode is that if primary database cannot write/send redo data into standby database locations it will be shutdown. Since such configuration prioritizes data protection over primary database’s availability, the recommended deployment is to have at least several standby databases.

How to enable, change protection modes

from SQLPlus on primary : 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY/PERFORMANCE/PROTECTION;

from Data Guard broker command line interface :

 DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY/MAXPROTECTION/MAXPERFORMANCE;

 Thank you
Osama Mustafa

ORA-29701: Unable to connect to Cluster Manager

Posted on by Osama Mustafa in Database
ORA-29701: Unable to connect to Cluster Manager.

The reason is Oracle cssd daemon process was not running.

As Root User :

Step 1: Go to the /etc folder
Step 2: Execute the following command.

# init.cssd start

startup will be queued to init within 30 seconds

Step 3: Wait for 2 minutes and execute the following commands

# cd $ORACLE_HOME/bin
# ./localconfig delete

Stopping CSSD
Failure in CSS initialization opening OCR.
Shutdown has begun. The daemons should exit soon.

Step 4: Execute the following command

# ./localconfig add

Successfully accumulated necessary OCR keys.
Creating OCR keys for user root, privgrp ‘system’
Operation Successful
Configuration for local css has been initialized.
Adding to inittab
Startup will be queued to init within 30 seconds
Checking the status of new oracle init process..
Expecting the CRS daemons to be up within 600 seconds
CSS is active on these nodes
nodelist
CSS is active on all nodes
Oracle CSS service is installed and running under init(1M)

Step 5: Start the ASM instance now.