World of Oracle DBA

This example is based on, You have lost all your current and backup of controlfiles. You have avaiable your current data files. You have lost all your online active , current redo log files. You have lost your spfile and pfile. In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and all copies of redo log groups and members are lost. 1. Let's start by deleting online redo log files and controlfile of my running database. SQL> select MEMBER from v$logfile; MEMBER -------------------------------------------------------------------------------- /oradata2/shaikdba/shaikdba/redo02.log /oradata2/shaikdba/shaikdba/redo01.log /oradata2/shaikdba/shaikdba/redo03.log SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /oradata2/shaikdba/shaikdba/control01.ctl /oradata2/shaikdba/shaikdba/control02.ctl /oradata2/shaikdba/shaikdba/control03.ctl 2.Delete all c...

In this I will show what can happen if Oracle is unable to write to the trace/alert.log to bdump or cdump & udump directories. It can happen if you loss your background_dump_dest or user_dump_dest or core_dump_dest ormay the the partition containing bdump or udump folder is full. The answer is if oracle is unable to write to the trace/alert.log to bdump & udump directories then the oracle behavior depends on which process is attempting to write to alert.log/trace/coredump file. Based on the process the instance may or may not crash immediately. If a foreground process corresponding to user process wants to write, but is unable to do so, the process may hang/terminate but there will not be any impact on database. But if the background process wants to write it may hang and eventually crash the instance if that background process terminates. In the following section I demonstrate this behavior on 10.2g 1.Start database with spfile Create one bdump directory SQL> !mkdir /oradat...

Problem Description When I start my database my instace fails with ORA-00444 and ORA-07446 as follows. SQL> startup ORA-00444: background process "MMAN" failed while starting ORA-07446: sdnfy: bad value '' for parameter . Cause of The Problem Database startup event is logged into alert log file. And alert log file is defined by the background_dump_dest parameter inside the initialization parameter. While starting up the instance if instance fail to write into the alert log file then instance crushes and fail to startup. Solution of The Problem There may be different scenario whenever oracle fails to write alert log contents. Suppose the partition reside alert log file is full. In that case delete/move unnecessary file from the alert log partition directory. There may be the case the the directory defined by the background_dump_dest does not exist in the OS. In both case you may also like to change the background_dump_dest location inside the pfile. If you have spfi...

One thing you need to remember before read this post is there is no easy or shortcut way to make a read only user of another schema. Like grant select on username to another_username- there is no such single command like this. However you may have several alternatives to make read only user for a schema. I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema. Let's start by creating PROD user. SQL> CREATE USER PROD IDENTIFIED BY P; User created. SQL> GRANT DBA TO PROD; Grant succeeded. SQL> CONN PROD/P; Connected. SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER); Table created. SQL> INSERT INTO PROD_TAB1 VALUES(1,2); 1 row created. SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE); Table created. SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1 BEGIN INSERT INTO PROD_TAB2 VALUES(SYSDATE); END; / Trigger created. SQL...

DBA wants that in the database certain user will not be able structural change. That is no DDL operation can be performed by the user. In that case DBA can achieve his goal simply by making a trigger on the schema. Suppose we want user faruk will not be able to perform any DDL. Then create trigger as below. SQL> conn shaik/abdul Connected. SQL> create table before_trigger(a number); Table created. SQL>conn system/manager Connected. SQL> CREATE OR REPLACE 2 TRIGGER BEFORE_DDL_SHAIK 3 BEFORE DDL 4 ON SHAIK.SCHEMA 5 BEGIN 6 RAISE_APPLICATION_ERROR(-30900,'DDL Operation is not Permitted.' ); 7 END; 8 / Trigger created. SQL> conn shaik/abdul Connected. SQL> create table after_trigger(a number); create table after_trigger(a number) * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-21000: error number argument to raise_application_error of -30900 is out of range ORA-06512: at line 2

If someone ask for top sql they from enterprise manager he can find some expensive queries. But the result may vary based on the metrics or criteria of expensive. Measurement may vary on, a) Logical IO's per execution? b) Physical IO's per execution? c) Cpu usage? d) Based on number of parse calls? e) Elapsed time used? f) Number of executions? g) Size consumed in shared pool? h) Number of child versions found? i) Based on Wait time? However from DBA_HIST_SQLSTAT we can get a complete picture of historical SQL statistics. AWR does a pretty job. Between two snapshot it displays the top sql statements in the enterprise manager. We can also see it from sql queries. In fact Enterprise Manager displays information of SQL text from DBA_HIST_SQLTEXT view which captures information from V$SQL and is used with the DBA_HIST_SQLSTAT view. Along with the DBA_HIST_SQLSTAT and DBA_HIST_SQLSTAT we can use the view DBA_HIST_SNAPSHOT in order to specify the range of snapshots in between analysi...

Problem Description While doing remote mining in the mining database through logminer START_LOGMNR procedure fails with ORA-01295. May be you want to analysis redo log files of the production database in the testing environment. SQL> BEGIN DBMS_LOGMNR.START_LOGMNR (options => dbms_logmnr.dict_from_online_catalog); END; / 2 3 4 5 6 BEGIN * ERROR at line 1: ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles ORA-06512: at "SYS.DBMS_LOGMNR", line 58 ORA-06512: at line 2 Cause of The Problem The error occurs because the dictionary file produced by a database (testing) is different from the one that produced the logfiles (production). In order to use logminer you need to extract dictionary information from the source database(production) to the mining database(testing). Solution of The Problem Step 1. Extract the dictionary to the redo logs in the production database by executing following command, SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPT...

Problem Symptoms From the backuppiece restore spfile commnad fails with ORA-19870,ORA-19587,ORA-27091,ORA-27067 RMAN> restore spfile from '/backup1/snap/june/8rji9vrq_1_1'; Starting restore at 27-AUG-08 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=36 devtype=DISK channel ORA_DISK_1: autobackup found: /backup1/snap/june/8rji9vrq_1_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/27/2008 01:05:07 ORA-19870: error reading backup piece /backup1/snap/june/8rji9vrq_1_1 ORA-19587: error occurred reading 0 bytes at block number 1 ORA-27091: unable to queue I/O ORA-27067: size of I/O buffer is invalid Additional information: 2 Cause of the Problem RMAN could not read the backuppiece. There may be severa...

Problem Description SQL> select dbms_metadata.get_ddl('TABLE','TEST_EXT') from dual; ERROR: ORA-39212: installation error: XSL stylesheets not loaded correctly ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_METADATA_INT", line 7398 ORA-06512: at "SYS.DBMS_METADATA_INT", line 7447 ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453 ORA-06512: at "SYS.DBMS_METADATA", line 1919 ORA-06512: at "SYS.DBMS_METADATA", line 2792 ORA-06512: at "SYS.DBMS_METADATA", line 4333 ORA-06512: at line 1 no rows selected Cause of The Problem The XSL stylesheets used by the Data Pump Metadata API were not loaded correctly into the Oracle dictionary table "sys.metastylesheet." It may be the cause that the stylesheets were not loaded at all in the database, or they were not converted to the database character set. Suppose if you convert your database character set using ALTER DATABASE statement...

Error Description: While performing disaster recovery I get the error RMAN-06172 as below. RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initSHAIK.ora' from '/backup1/snap/june/ctl_sp_bak_c-448149146-20080607-00'; Starting restore at 27-AUG-08 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=36 devtype=DISK RMAN-00571: ==================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: ==================================================== RMAN-03002: failure of restore command at 08/27/2008 00:42:41 RMAN-06172: no autobackup found or specified handle is not a valid copy or piece Cause of The Error To perform restore operation to a new host you must have at least one autobackup of the controlfile. If you don't have autobackup of the controlfile then you would not be able to perform disaster recovery. For example if you have snapshot of controlfile backup then you w...

Whenever we issue in RMAN, RMAN> restore spfile from autobackup; then restoring from autobackup stops at seven days and then it arises message channel ORA_DISK_1: no autobackup in 7 days found. For example, RMAN> restore spfile from autobackup; Starting restore at 31-AUG-08 using channel ORA_DISK_1 channel ORA_DISK_1: looking for autobackup on day: 20080831 channel ORA_DISK_1: looking for autobackup on day: 20080830 channel ORA_DISK_1: looking for autobackup on day: 20080829 channel ORA_DISK_1: looking for autobackup on day: 20080828 channel ORA_DISK_1: looking for autobackup on day: 20080827 channel ORA_DISK_1: looking for autobackup on day: 20080826 channel ORA_DISK_1: looking for autobackup on day: 20080825 channel ORA_DISK_1: no autobackup in 7 days found RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03...

Error Description I give the full path of rman executable file location and I am getting error RMAN .msb not found as below. -bash-3.1$ /oradata2/bin/rman target / Message file RMAN .msb not found Verify that ORACLE_HOME is set properly Solution of The problem Believe me, as I still got you have not set ORACLE_HOME properly. So set it. To know your current settings of ORACLE_HOME, issue, -bash-3.1$ echo $ORACLE_HOME /oradata2/bin/ Here we see it is set, it may either unset. Though it is set wrong. The ORACLE_HOME path is before the bin directory. So here ORACLE_HOME will be /oradata2 instead of /oradata2/bin/ On unix set the value for the current session by, -bash-3.1$ export ORACLE_HOME=/oradata2/ On Windows environment you have to set by set ORACLE_HOME=C:\oracle or like that. In order to set it permanently edit your profile. On linux like, ~/.bash_profile or on unix edit the file .profile on home directory and make an entry of ORACLE_HOME. After setting correct ORACLE_HOME now try ...

Problem Description Whenever you try to startup the database with pfile then it fails with error RA-09925: Unable to create audit trail file along with Linux Error: 2: No such file or directory. RMAN> startup force pfile='/oradata2/shaikdbapfile.ora'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 08/28/2008 15:02:29 RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file Linux Error: 2: No such file or directory Additional information: 9925 Cause of the Problem The error ORA-09925 indicates that oracle was unable to write the audit information into the audit directory. The audit directory is specified by audit_trail parameter in the initialization parameter. Just after the ORA-09925 if there is some OS error then that is notified. The linux error cle...

Problem Description While performing disaster recovery to a new host, you need to start the database in nomount sate(in fact to restore control file) but database could not start in nomount state because it fails with RMAN-04014, ORA-07446. RMAN> startup force pfile='/oradata2/shaikdbapfile.ora'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 08/28/2008 15:03:44 RMAN-04014: startup failed: ORA-07446: sdnfy: bad value '/oracle/app/oracle/product/10.2.0/db_1/admin/shaikdba/udump' for parameter user_dump_dest. Cause of The Problem The path /oracle/app/oracle/product/10.2.0/db_1/admin/shaikdba/udump as shown in output does not exist. Oracle itself does not create any path if a path does not exist. So, you have to change the value of user_dump_dest in the initial...

Problem Description While I start my database with my initialization parameter it fails with oracle error ORA-01261 and ORA-01262 as below: RMAN> startup force pfile='/oradata2/shaikdbapfile.ora'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 08/28/2008 15:04:27 RMAN-04014: startup failed: ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux Error: 2: No such file or directory Cause of The Problem The linux error No such file or directory indicates all what you need to do. It may be the case that, -You might not start your database with proper or recently updated pfile/spfile. May be you changed your location of your db_recovery_file_dest and that initialization parameter that ...

Problem Description Whenever I try to create tablespace it fails with error message, ORA-00059. SQL> create tablespace test_tbs; create tablespace test_tbs * ERROR at line 1: ORA-00059: maximum number of DB_FILES exceeded Cause of The Problem You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter. The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile. And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but les...

Problem Description An attempt to manually start the instance fails with, ORA-29702: error occurred in Cluster Group Service operation crs_stat produces the error, /oracle/crs/bin/crs_stat -t CRS-0184: Cannot communicate with the CRS daemon. CSS stack did not come up as the following command issued out of init.cssd crsctl check boot failed with error message Failed 3 to bind listening endpoint: (ADDRESS=(PROTOCOL=tcp)(HOST=node2priv)) and return code 11. The error message was also written to /var/log/messages on this node. Note that in 10gR2 the message no longer gets written to /var/log/messages, instead it will be written to /tmp/crsctl. Cause of the Problem Error message 'Failed 3 to bind listening endpoint: (ADDRESS=(PROTOCOL=tcp)(HOST=node2priv))' indicated that CSS had a problem creating the listening endpoint for hostname node2 In node1 within the /etc/hosts file the name of the private interconnect exist. But on node2 somehow it is missing the private interconne...

There may be situations when you need to increase or decrease your datafile size. Prior to oracle 7.2 there was no easy way to resize the datafile. Before 7.2 the solutions was to drop and recreate the tablespace with different sized datafiles, or to add more datafiles to a tablespace whenever you need more space in the tablespace. Before 7.2 the RESIZE command will raise error, ORA-00923: FROM keyword not found where expected Before entering into resize datafile let's be familiar with several views related to datafile. From V$DATAFILE.CREATION_TIME we can see the timestamp of the datafile creation time. From V$DATAFILE.BYTES we can see the current datafile size in bytes. 0 in this fields indicate the datafile is inaccessible. From V$DATAFILE.CREATE_BYTES we can the datafile size when it was created. From V$DBA_DATA_FILES.MAXBYTES we can see the maximum size of the datafile. Before going into resize I just create one tablespace containing one datafile of size 10M which can be ext...

Problem Description Whenever I try to create tablespace it fails with error message, ORA-00059. SQL> create tablespace test_tbs; create tablespace test_tbs * ERROR at line 1: ORA-00059: maximum number of DB_FILES exceeded Cause of The Problem You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter. The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile. And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but les...

Problem Description An attempt to manually start the instance fails with, ORA-29702: error occurred in Cluster Group Service operation crs_stat produces the error, /oracle/crs/bin/crs_stat -t CRS-0184: Cannot communicate with the CRS daemon. CSS stack did not come up as the following command issued out of init.cssd crsctl check boot failed with error message Failed 3 to bind listening endpoint: (ADDRESS=(PROTOCOL=tcp)(HOST=node2priv)) and return code 11. The error message was also written to /var/log/messages on this node. Note that in 10gR2 the message no longer gets written to /var/log/messages, instead it will be written to /tmp/crsctl. Cause of the Problem Error message 'Failed 3 to bind listening endpoint: (ADDRESS=(PROTOCOL=tcp)(HOST=node2priv))' indicated that CSS had a problem creating the listening endpoint for hostname node2 In node1 within the /etc/hosts file the name of the private interconnect exist. But on node2 somehow it is missing the private interconne...

Before discussing limitations of adding datafiles to a tablespace let's think about DB_FILES parameter. DB_FILES is an initialization parameter in oracle. When an oracle instance starts, this parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. This parameter exists throughout the life cycle of the instance. So, changes to DB_FILES affect only after restarting the instance. As this parameter indicates the amount of SGA space to reserve for datafile information the bigger value settings indicates memory is unnecessarily consumed. And smaller value setting of this parameter impose limit to create another datafile. So setting of DB_FILES parameter value is important. Now let's look about possible limitations when adding datafiles to a tablespace. Possible Limitations When Adding Datafiles to a Tablespace •OS imposes a limit of the number of files a process can be opened at a ...

The Oracle Clusterware comprises several background processes that facilitate cluster operations. These processes or components are the main communication links between the Oracle Clusterware high availability components and the Oracle Database as well as they monitor and manage database operations. Here is the list of major oracle clusterware components or processes. 1)Cluster Synchronization Services (CSS): It manages and controls which nodes are members of the cluster and notify members when a node joins or leaves the cluster. 2)Cluster Ready Services (CRS): It manages high availability operations within a cluster. The CRS process start, stop, monitor and failover operations. It generates events when a resource status changes. When you have installed Oracle RAC, crs monitors the Oracle instance, Listener, and so on, and automatically restarts these components when a failure occurs. 3)Event Management (EVM): It is a background process that publishes events that crs creates. 4)Orac...

Configuring RAW decides in RAC is just similiar as you do configure raw devices for voting disk and OCR. In this example, I have faced a different scenario where in In racnode-1 disk sdb is mapped to sdf in ranode-2 In racnode-1 disk sdc is mapped to sdd in ranode-2 In racnode-1 disk sdd is mapped to sde in ranode-2 First I have created three raw partitions from my raw devices sdb, sdc and sdd of racnode-1 each of 80GB for ASM. [root@racnode-1 ~]# /sbin/fdisk /dev/sdb The number of cylinders for this disk is set to 19581. There is nothing wrong with that, but this is larger than 1024, and could in certain setups cause problems with: 1) software that runs at boot time (e.g., old versions of LILO) 2) booting and partitioning software from other OSs (e.g., DOS FDISK, OS/2 FDISK) Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 2 First cylinder (33-19581, default 33): +80000M Last cylinder or +size or +sizeM or +sizeK (9726-19...

Many one got confused with the setting of umask in unix or linux system. Whenever I was a newbie in this field I also got a bit confused with this settings. In a nutshell umask defines what will be the default permission of a file whenever a user create the file. You already are familiar with the unix permission of 754. The first digit 7(4 read +2 write+1 execute) is for permission for owner, the second digit 5 is for permission for the group and third digit 4 is for permission for others. In brief, User class: Owner Group Others character representation: rwx r-x r-- binary representation: 111 101 100 octal representation: 7 5 4 Now lets a look at ls -l after creating a file without setting any umask. -bash-3.00$ touch without_umask.txt -bash-3.00$ ls -l total 0 -rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt After omitting first digit as it indicates whether file or folder we get first 3 digit after - is 110 that is 6 for user oracle permission. second...

Problem Description While using OUI it fails with following message, OUI-10020:The target area /oracle/10gR2/oraInventory is being used by another session. A write lock cannot be obtained. Cause of The Problem There is some process or software that holds lock on the /oracle/10gR2/oraInventory file. Until that software release lock OUI continues to display message OUI-10020. Solution of The Problem 1.Go to the oraInventory directory. $cd /oracle/10gR2/oraInventory 2. See the contents of it. $ls /oracle/10gR2/oraInventory You will see the locks directory here. 3.Go to the lock directory and delete the read lock under the directory. $cd locks $ rm /oracle/10gR2/oraInventory/locks/reader0.lock 4.Now run your Oracle Universal Installer.

Reasons of ORA-04030 When a client program connects to oracle database an oracle process does work on behalf of the client. The Oracle process allocated memory from PGA(Program Global Area). For dedicated server process PGA contains stack, user session data, cursor information and the sort area. For shared server configuration UGA(user session data, cursor information and the sort area) is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors. The ORA-4030 can come based on various reasons which is noted below. 1)The Oracle process need more memory in order to request client program and it requests additional memory from Operating System but the Operating System can't serve the request. This is likely to happen if OS does not have enough memory or swap space is not available. 2)There is a memory limit restriction from OS for oracle process to use. Oracle process already use the limit and now it requested additional memory and hence error appears....

Error Description While connect to database as a normal user it fails with ORA-28031: maximum of 148 enabled roles exceeded Cause of The Problem In a database at one time 148 user-defined roles can be enabled. Enabling more than 148 user-defined roles causes ORA-28031. Solution of The Problem At first time you can think about max_enabled_roles parameter but max_enabled_roles parameter has nothing to do if you enable 148 user-defined roles. In fact this parameter is deprecated. It is retained for backward compatibility only. You can see this parameter settings by, SQL> show parameter max_enable NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ max_enabled_roles integer 150 Though this parameter is set to 150 and you have enabled 148 user defined roles but additional user-defined roles can't be enabled because...

We know in a UNIX system with tail -f we can monitor alert log file to see how alert log file is populated. For example the latest happenings in alert log file can be seen by, $tail -f background_dump_dest location/alert_$ORACLE_SID.ora But in windows there is not tail command. We can monitor the alert log within oracle itself regardless of platform. This can be archived by following steps. 1)Create an External Table to read the alert log. column a_log new_value ALOG noprint column value new_value bkgd_dmp noprint select 'alert_'||instance_name||'.log' a_log from v$instance; select value from v$parameter where name = 'background_dump_dest'; create or replace directory data_dir as '&&bkgd_dmp'; CREATE TABLE alert_t ( text_line varchar2(255) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( records delimited by newline fields REJECT ROWS WITH ALL NULL FIELDS ) LOCATION...

Schedule or Automate Backup is a needed thing almost in all environment. We can do automate or scheduling tasks through two ways, one is DBMS_SCHEDULER packager which will be discusses in another topic and another is OS scheduler. If you think about OS scheduler then on unix box use crontab and on windows box use scheduling jobs. In this topic I have shown how we can take automate backup through crontab tool. To avoid error Verify that ORACLE_HOME is set properly error like as below Message file RMAN.msb not found Verify that ORACLE_HOME is set properly set proper ORACLE_HOME and ORACLE_SID in your backup file. So I created my backup_job.sh as below. bash-3.00$ vi backup_job.sh #!/bin/bash export ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1 export ORACLE_SID=dbase /oracle/app/oracle/product/10.2.0/db_1/bin/rman target sys/a@moon:1522/dbase cmdfile=/oradata1/ backup/backup_job2.sh If you don't set $ORACLE_HOME and don't give full path of rman then possibly in the output fi...

With the command netstat -i we can monitor the traffic in the network nodes. The sample output of netstat -i is below. # netstat -i Kernel Interface table Iface MTU Met RX-OK RX-ERR RX-DRP RX-OVR TX-OK TX-ERR TX-DRP TX-OVR Flg eth0 1500 0 226707 0 0 0 68280 0 0 0 BMRU lo 16436 0 13 0 0 0 13 0 0 0 LRU vmnet 1500 0 0 0 0 0 6 0 0 0 BMRU We can get the the elaboration of the preceding terms below. * Name - The type of the Ethernet interface * Mtu - The maximum size of the packets that are transmitted on the network. * Net/Dest - The subnet address * Address - The name of the system * Ipkts - The number of packets received * Ierrs - The number of received packets that have errors * Opkts - The number of packets sent * Oerrs - The number of sent packets that have errors * Collis - The number of output packets that result in coll...

In a database whenever you query from V$database to know the name of the database and want to know the name of the instance from v$thread they return similar result. Suppose, SQL> select name from v$database; NAME --------- DBASE SQL> select instance from v$thread; INSTANCE -------------------------------------------------------------------------------- dbase which indicates in the database there is only one instance that is database have a one-to-one relationship between datafiles and the instance. Oracle RAC environments, however, have a one-to-many relationship between datafiles and instances. In RAC environments multiple instances form a single database. The instances can reside on different servers which is referred as nodes in RAC environment. • In RAC environment the set of interconnected instances that allows the servers to work together is called cluster. The physical structures that make up the database must reside on shared storage that is accessible from all servers t...

1)Oracle Universal Installer (OUI)- After configuring the pre-installation tasks of the nodes OUI installs the Oracle Clusterware and the Oracle Database software with Oracle RAC. It also can install oracle software on the other nodes using a network connection. 2)Cluster Verification Utility (CVU)- The CVU is a command-line tool. It is very useful to check the nodes for preinstallation as well as postinstallation requirements of the cluster environment. In fact OUI runs the CVU after the Oracle Clusterware installation to verify the environment. 3)Oracle Enterprise Manager- With EM it is easy to configure RAC environments. It has both the Database Control and Grid Control graphical user interfaces (GUIs). 4)Server Control (SRVCTL)- SRVCTL is a command-line interface that you can use to manage an Oracle RAC database from a single point. Using SRVCTL you can start or stop or manage any instance on the cluster. 5)Cluster Ready Services Control (CRSCTL)- CRSCTL is a command-line tool...

1)Checking the Hardware Requirements •Physical memory: At least 1GB RAM. •Swap space: If RAM is between 1 GB and 2 GB then make swap space to 1.5 times of the size of RAM. If RMAN is more than 2GB then make swap space to the equal of the size of the RAM. •Temporary space: At least 400 MB. Typically in /tmp directory. •Processor type (CPU): Need to be certified with the version of the Oracle software being installed. •Hard Disk Space: 1.5 GB for oracle database home directory+1.5GB for the ASM home directory+120 oracle clusterware software installation+Two Oracle Clusterware components OCR 256 MB each, or 512 MB total disk space+Three Oracle Clusterware components Voting Disk 256 MB each, or 768 MB total disk space. •All the nodes in the cluster must have same hardware architecture. However can have machines of different speeds and size in the same cluster. On *nix system you can check hardware components as follows. •To determine physical RAM size, # grep MemTotal /proc/meminfo •To...

Kernel Parameters, Recommended Values to install Oracle and Associated Files 1)semmsl semmns semopm semmni: Corresponding recommended values to install oracle are 250 32000 100 128 or over and associated the default settings of these kernel parameter are in file /proc/sys/kernel/sem 2)shmmax: Recommended value to install oracle is the half the size of physical memory (in bytes) or over. The default setting of this kernel parameter is in /proc/sys/kernel/shmmax 3)shmmni: Recommended value to install oracle is 4096 or over. The default setting of this kernel parameter is in /proc/sys/kernel/shmmni 4)shmall: Recommended value to install oracle is 2097152 or over. The default setting of this kernel parameter is in /proc/sys/kernel/shmall 5)ip_local_port_range: Recommended value to install oracle is Minimum: 1024 and Maximum: 65000 or over. The default setting of this kernel parameter is in /proc/sys/net/ipv4/ip_local_port_range 6)rmem_default: Recommended value to install oracle is 1...

Configuring Operating System Users and Groups in All Nodes On windows you don't need to create separate user to install oracle or don't need to create groups. OUI automatically do it. However on unix you must create the following operating system groups are required if you are installing Oracle RAC for the first time. •The OSDBA group (typically, dba) -This is the OS user who has SYSDBA privilege. •The Oracle Inventory group (typically, oinstall) -This group owns all Oracle software installed on the system. The following operating system users are required •A user that owns the Oracle software (typically, oracle). •An unprivileged user (for example, the nobody user on Linux systems). Create the required groups and users Do this steps on all nodes of your cluster. As a root user, /usr/sbin/groupadd oinstall /usr/sbin/groupadd dba useradd -u 200 -g oinstall -G dba -d /home/oracle -r oracle Change the password by, passwd oracle Verify the user by, id oracle Note that in this examp...